分页 sql 语句 及在 asp.net 中自定义 datagrid 分页

作者: qi_jianzhou 标题: 分页 sql 语句 关键字: 分类: Sql Server 密级: 私有 (评分: , 回复: 0, 阅读: 2)  »»

select top 页大小 * from table1 where id>       (select max (id) from       (select top ((页码-1)*页大小) id from table1 order by id) as T        )       order by id 2005-11-29 16:04:00    修改笔记 发表评语»»»     2005-11-30 16:22:58    用 asp.net 的 dagaGrid 控件，自定义分页，速度很快 using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Data.SqlClient; namespace person {  /// <summary>  /// dataGrid 的摘要说明。  /// </summary>  public class dataGrid : System.Web.UI.Page  {    protected System.Web.UI.WebControls.DataGrid DataGrid1;        protected int pageSize=0;    protected int pageCurrentIndex=0;    protected System.Web.UI.WebControls.Button Button1;    protected System.Web.UI.WebControls.Button Button2;    protected int pageCount=0;        private void Page_Load(object sender, System.EventArgs e)    {      // 在此处放置用户代码以初始化页面      if ( ! this.IsPostBack)      {                this.pageSize=10;        this.pageCurrentIndex=1;        this.pageCount=this.GetPageCount();        ViewState["pageSize"]=this.pageSize;        ViewState["pageCount"]=this.pageCount;        ViewState["pageCurrentIndex"]=this.pageCurrentIndex;        this.BindToDataGrid();                      }                              }    private int GetPageCount()    {      SqlConnection sqlcon=new SqlConnection("data source=(local);database=northwind;user id=sa;password=sa");      sqlcon.Open();      SqlCommand cmd=new SqlCommand("select count(*) from employees",sqlcon);      int i=(int)cmd.ExecuteScalar();      int j=i%this.pageSize;  //取余数      int k=i / this.pageSize;//取总页数      if ( j>0)               //如果余数不为 0　则表示有不满 1　页的数据　则总页数加 1      {        return k+1;      }      else      return k;  //余数为0　，则正好除尽    }        private void BindToDataGrid()    {      this.pageSize=(int)ViewState["pageSize"];      this.pageCount=(int)ViewState["pageCount"];      this.pageCurrentIndex=(int)ViewState["pageCurrentIndex"];      int recordCount=this.pageSize*(this.pageCurrentIndex-1);      string sql;      if ( recordCount==0)      {         sql="select top "+this.pageSize.ToString()          +" * from employees";      }      else      {         sql="select top "+this.pageSize.ToString()          +" * from employees where employeeid>"          +" ( select max(employeeid) from "          +" (select top "+Convert.ToString(recordCount)+" * "          +" from employees order by employeeid) as t) order by employeeid";      }      SqlConnection sqlcon=new SqlConnection("data source=(local);database=northwind;user id=sa;password=sa");      sqlcon.Open();      SqlDataAdapter sda=new SqlDataAdapter(sql,sqlcon);      DataSet ds=new DataSet();      sda.Fill(ds,"employees");      this.DataGrid1.DataSource=ds.Tables["employees"].DefaultView;      this.DataGrid1.DataBind();    }        #region Web 窗体设计器生成的代码    override protected void OnInit(EventArgs e)    {      //      // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。      //      InitializeComponent();      base.OnInit(e);    }        /// <summary>    /// 设计器支持所需的方法 - 不要使用代码编辑器修改    /// 此方法的内容。    /// </summary>    private void InitializeComponent()    {          this.Button1.Click += new System.EventHandler(this.Button1_Click);      this.Button2.Click += new System.EventHandler(this.Button2_Click);      this.Load += new System.EventHandler(this.Page_Load);    }    #endregion    private void Button1_Click(object sender, System.EventArgs e)    {      this.pageCurrentIndex=(int)ViewState["pageCurrentIndex"];      this.pageCount=(int)ViewState["pageCount"];      this.pageSize=(int)ViewState["pageSize"];      this.pageCurrentIndex-=1;      ViewState["pageCurrentIndex"]=this.pageCurrentIndex; //      ViewState["pageCount"]=this.pageCount; //      ViewState["pageSize"]=this.pageSize;      this.BindToDataGrid();    }    private void Button2_Click(object sender, System.EventArgs e)    {      this.pageCurrentIndex=(int)ViewState["pageCurrentIndex"];      this.pageCount=(int)ViewState["pageCount"];      this.pageSize=(int)ViewState["pageSize"];      this.pageCurrentIndex+=1;      ViewState["pageCurrentIndex"]=this.pageCurrentIndex; //      ViewState["pageCount"]=this.pageCount; //      ViewState["pageSize"]=this.pageSize;      this.BindToDataGrid();          }  } }