分页存储过程-引

CREATE PROCEDURE [GetCustomersDataPage]

         @PageIndex INT,

         @PageSize  INT,

         @RecordCount INT OUT,

         @PageCount INT OUT

AS

SELECT @RecordCount = COUNT(*)  FROM   Customers

SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

DECLARE @SQLSTR NVARCHAR(1000)

IF @PageIndex = 0 OR @PageCount <= 1

         SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+

'  CustomerID, CompanyName,Address,Phone  FROM   Customers ORDER BY CustomerID DESC'

ELSE IF     @PageIndex = @PageCount - 1            

         SET @SQLSTR =N' SELECT * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+

'  CustomerID, CompanyName,Address,Phone  FROM   Customers ORDER BY CustomerID ASC ) TempTable  ORDER BY CustomerID DESC'

ELSE         

        SET @SQLSTR =N' SELECT TOP  '+STR( @PageSize )+' * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+

'  CustomerID, CompanyName,Address,Phone  FROM   Customers ORDER BY CustomerID ASC ) TempTable ORDER BY CustomerID DESC'

EXEC (@SQLSTR)

 

GO

 

select count(*) from Employee
--求的记录数

SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
--求得页数

select top 5 * from (select top (49-5*1) * from Employee order by ID ASC) tmptb  order by ID DESC

---2是pageIndex,页索引，当前页码是 pageIndex+1.

 

获取记录数和页数都采用存储过程的输出参数。

获取数据源，这里返回一个DataSet。

先定义了连个数据成员，

private int pageCount;//页数

private int recordCount;//记录数

//获取单页数据

private static DataSet GetCustomersData(int pageIndex,int pageSize,ref int recordCount,ref int pageCount)

{

     string connString = ConfigurationSettings.AppSettings["ConnString"];

     SqlConnection conn = new SqlConnection(connString);

     SqlCommand comm = new SqlCommand("GetCustomersDataPage",conn);

 

     comm.Parameters.Add(new SqlParameter("@PageIndex",SqlDbType.Int));

     comm.Parameters[0].Value = pageIndex;

 

     comm.Parameters.Add(new SqlParameter("@PageSize",SqlDbType.Int));

     comm.Parameters[1].Value = pageSize;

 

     comm.Parameters.Add(new SqlParameter("@RecordCount",SqlDbType.Int));

     comm.Parameters[2].Direction = ParameterDirection.Output;

 

     comm.Parameters.Add(new SqlParameter("@PageCount",SqlDbType.Int));

     comm.Parameters[3].Direction = ParameterDirection.Output;

 

     comm.CommandType = CommandType.StoredProcedure;

     SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);

     DataSet ds = new DataSet();

     dataAdapter.Fill(ds);

 

     recordCount = (int)comm.Parameters[2].Value;

     pageCount = (int)comm.Parameters[3].Value;

 

     return ds;

}

//绑定数据到DataGrid，同时刷新数据总记录数

private void DataGridDataBind()

{

     DataSet ds = GetCustomersData(PageIndex,PageSize,ref recordCount,ref pageCount);

     this.DataGrid1.VirtualItemCount = RecordCount;

     this.DataGrid1.DataSource = ds;

     this.DataGrid1.DataBind();

}

下面是分页的几个变量属性

public int PageCount

{

     get{return this.DataGrid1.PageCount;}

}

 

public int PageSize

{

     get{return this.DataGrid1.PageSize;}

}

 

public int PageIndex

{

     get{return this.DataGrid1.CurrentPageIndex;}

     set{this.DataGrid1.CurrentPageIndex = value;}

}

 

public int RecordCount

{

     get{return recordCount;}

}

注册DataGrid分页事件

//分页事件处理

private void DataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)

{

     DataGrid dg = (DataGrid)source;

     dg.CurrentPageIndex = e.NewPageIndex;

     DataGridDataBind();

}

最好判断当前页面是否是第一次加载，防止重复加载两次数据，

private void Page_Load(object sender, System.EventArgs e)

{

     if(!Page.IsPostBack)

     {

         DataGridDataBind();

     }

}