ASP.NET 2.0 分頁技術之使用純sql語句的雙top分頁篇

悟靜 2013-02-02

展開全文

Repeater在前臺使用比較靈活自由，但有一個問題就是Repeater不支持直接分頁，這個很多人看起來就有點不想用了，但我想大家都知道GridView控件或DataGrid控件在啟用自帶分頁的時候其實效率是非常低的，大的不說，一但到了百萬級數(shù)據(jù)以后，就會感覺是多么的痛苦和無耐了，所以即使是用DataGrid(GridView)控件，高手們還是只會只使用一個界面而已，而分頁肯定都是自己寫的，這樣一比較利弊就出來了，既然這樣那就選擇Repeater吧,本篇示例演示使用純sql語句的雙top分頁法達到控件分頁的功能.
--------------------------------------------------------------------------------

前臺代碼：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www./TR/xhtml1/DTD/xhtml1-transitional.dtd">

后臺事件代碼：
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//------導包
using System.Data.SqlClient;

public partial class Default3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        int cpage;
        if (Request.QueryString["page"] != null)
            cpage = Convert.ToInt32(Request.QueryString["page"]);
        else
            cpage = 1;

        TimeSpan newtimespan = DateTime.Now.TimeOfDay;//運行時間開始
        int pagesize = 5;
        string sql = "";
        string sqlstr = "select count(*) from jobs";
        SqlConnection con = new SqlConnection("server=(local);database=pubs;uid=sa;pwd=sa;");
        SqlCommand cmd = new SqlCommand(sqlstr, con);
        con.Open();
        int totalput = Convert.ToInt32(cmd.ExecuteScalar());

        int maxpage = 1;
        if (totalput % pagesize == 0)
        {
            maxpage = totalput / pagesize;
        }
        else
        {
            maxpage = totalput / pagesize + 1;
        }
        if (maxpage == 0) { maxpage = 1; }
        if (cpage < 1) { cpage = 1; }
        else if (cpage > maxpage) { cpage = maxpage; }

        if (totalput != 0)
        {
            if (cpage == 1)
            {
                sql = "select top " + pagesize + " * from jobs order by job_id desc";
            }
            else
            {
                sql = "select top " + pagesize + " * from jobs where job_id not in(select top " + (cpage - 1) * pagesize + " job_id from jobs order by job_id desc) order by job_id desc";
            }
        }

        SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        adapter.Dispose();

        this.Label1.Text = "共有信息" + totalput.ToString() + "條當前是第" + cpage.ToString() + "/" + maxpage.ToString() + "頁 ";
        if (cpage != 1)
        {
            this.HyperLink1.NavigateUrl = "Default3.aspx?page=1";
            this.HyperLink2.NavigateUrl = "Default3.aspx?page=" + Convert.ToString(cpage - 1);
        }
        if (cpage != maxpage)
        {
            this.HyperLink3.NavigateUrl = "Default3.aspx?page=" + Convert.ToString(cpage + 1);
            this.HyperLink4.NavigateUrl = "Default3.aspx?page=" + maxpage.ToString();
        }

        this.Repeater1.DataSource = ds.Tables[0].DefaultView;
        this.Repeater1.DataBind();
        //算出運行時間并附值給Label控件顯示
        this.Label1.Text = DateTime.Now.TimeOfDay.Subtract(newtimespan).TotalMilliseconds.ToString();
    }
}

如上述代碼有什么問題請留言或聯(lián)系我!