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="#F 7F 7F 7" 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());