.Net 分页实现系列之二基于sql语句分页的方式

  Sql语句。很多情况下都是基于Select Top  {0} *  From T_O_Log Where  Id Not in ( Select Top {1} Id From T_O_Log  Order By ID Desc ) Order By ID Desc

  其基本原理是获取总数M条记录，而通过m条记录获取所需分页的n条最前的记录。

例如 分页中每页为10条记录，现在要跳转到10页去。那么总要获取的纪录为100条，而我们所需要的其实是最后的10条记录而已。所以前面的90条记录抛弃。

为了实现的方便，该例子是用aspnetpager做为显示页码和分页转向，当然你也可以自己写上一页、下一页的转向工作

实现方法如下
第一步，从工具箱—〉Web窗体—〉拖拉出DataGrid控件（或者是DataList 等等）

<asp:DataGrid id="DataGrid1" runat="server" Width="100%" AutoGenerateColumns="False" singleValue="#F7F7F7" oldValue="#FFFFFF">

</ asp:DataGrid>

 第二步 双击该窗体，进入codebehide
 private void Page_Load(object sender, System.EventArgs e)

         {

              // 在此处放置用户代码以初始化页面

              if(!Page.IsPostBack)

              {

                   GetLog();

              }

         }

private void GetLog()

         {

              string strsql;

//统计总的记录数

              SqlConnection Conn = new SqlConnection();

                   Conn.ConnectionString =@"server=MIS\PMSERVER;uid=sa;pwd=sa;database=test";

                   Conn.Open();

                   strsql=" Select count(ID) From T_O_Log";

                   DataSet ds = new DataSet();

                   SqlDataAdapter Adp = new SqlDataAdapter(strSql,Conn);

                   Adp.Fill(ds,"TypeIdList");                     this.AspNetPager1.RecordCount=System.Convert.ToInt32(ds.Tables[0].Rows[0][0]) ;

              BindData();

         }

private void BindData()

         {

              string strsql;            

              int RecordPage=(AspNetPager1.CurrentPageIndex-1)*AspNetPager1.PageSize;                               

              SqlConnection Conn = new SqlConnection();

                   Conn.ConnectionString =@"server=MIS\PMSERVER;uid=sa;pwd=sa;database=test";

                   Conn.Open();

                        strsql=String.Format(" Select Top  {0} *  From T_O_Log Where  Id Not in ( Select Top {1} Id From T_O_Log  Order By ID Desc) Order By ID Desc" ,AspNetPager1.PageSize.ToString(),RecordPage.ToString());
                   DataSet ds = new DataSet();

                   SqlDataAdapter Adp = new SqlDataAdapter(strSql,Conn);

                   Adp.Fill(ds,"TypeIdList");

              this.DataGrid1.DataSource=ds.Tables["T_O_Log"];

              this.DataGrid1.DataBind();          

              //动态设置用户自定义文本内容

              adp.Dispose();            

              AspNetPager1.CustomInfoText="记录总数：<font color=\"blue\"><b>"+AspNetPager1.RecordCount.ToString()+"</b></font>";

              AspNetPager1.CustomInfoText+=" 总页数：<font color=\"blue\"><b>"+AspNetPager1.PageCount.ToString()+"</b></font>";

              AspNetPager1.CustomInfoText+=" 当前页：<font color=\"red\"><b>"+AspNetPager1.CurrentPageIndex.ToString()+"</b></font>";

         }

private void AspNetPager1_PageChanged(object src, Wuqi.Webdiyer.PageChangedEventArgs e)

         {           AspNetPager1.CurrentPageIndex=e.NewPageIndex;

              BindData();

              System.Text.StringBuilder sb=new StringBuilder("<script Language=\"Javascript\"><!--\n");

              sb.Append("var el=document.all;");

              sb.Append(this.DataGrid1.ClientID);

              sb.Append(".scrollIntoView(true);");

              sb.Append("<");

              sb.Append("/");

              sb.Append("script>");

              if(!Page.IsStartupScriptRegistered("scrollScript"))

                   Page.RegisterStartupScript("scrollScript",sb.ToString());

         }