使用DataGrid时自带的分页实现起来虽然比较方便但是效率不高每次都需要读取所有页（整个记录集）而加载的只是其中一页造成了资源的浪费记录多又会使效率变得很低下面通过DataGrid的自定义分页功能来减少资源使用和提高效率
实现的关键是设置AllowCustomPaging属性位True并把VirtualItemCount属性设置位总的记录数给分页提供依据前台的主要代码如下
这里使用的数据源还是假设为Northwind的Customers表
下面是访问单页的存储过程实现方式很多不过这个是最普通的
CREATE PROCEDURE [GetCustomersDataPage]
@PageIndex INT
@PageSize INT
@RecordCount INT OUT
@PageCount INT OUT
AS
SELECT @RecordCount = COUNT(*) FROM Customers
SET @PageCount = CEILING(@RecordCount * / @PageSize)
DECLARE @SQLSTR NVARCHAR()
IF @PageIndex = OR @PageCount <=
SET @SQLSTR =NSELECT TOP +STR( @PageSize )+
CustomerID CompanyNameAddressPhone FROM Customers ORDER BY CustomerID DESC
ELSE IF @PageIndex = @PageCount
SET @SQLSTR =N SELECT * FROM ( SELECT TOP +STR( @RecordCount @PageSize * @PageIndex )+
CustomerID CompanyNameAddressPhone 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 CompanyNameAddressPhone FROM Customers ORDER BY CustomerID ASC ) TempTable ORDER BY CustomerID DESC
EXEC (@SQLSTR)
GO
获取记录数和页数都采用存储过程的输出参数
获取数据源这里返回一个DataSet
先定义了连个数据成员
private int pageCount;//页数
private int recordCount;//记录数
//获取单页数据
private static DataSet GetCustomersData(int pageIndexint pageSizeref int recordCountref int pageCount)
{
string connString = ConfigurationSettingsAppSettings[ConnString];
SqlConnection conn = new SqlConnection(connString);
SqlCommand comm = new SqlCommand(GetCustomersDataPageconn);
commParametersAdd(new SqlParameter(@PageIndexSqlDbTypeInt));
commParameters[]Value = pageIndex;
commParametersAdd(new SqlParameter(@PageSizeSqlDbTypeInt));
commParameters[]Value = pageSize;
commParametersAdd(new SqlParameter(@RecordCountSqlDbTypeInt));
commParameters[]Direction = ParameterDirectionOutput;
commParametersAdd(new SqlParameter(@PageCountSqlDbTypeInt));
commParameters[]Direction = ParameterDirectionOutput;
commCommandType = CommandTypeStoredProcedure;
SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
dataAdapterFill(ds);
recordCount = (int)commParameters[]Value;
pageCount = (int)commParameters[]Value;
return ds;
}
//绑定数据到DataGrid同时刷新数据总记录数
private void DataGridDataBind()
{
DataSet ds = GetCustomersData(PageIndexPageSizeref recordCountref pageCount);
thisDataGridVirtualItemCount = RecordCount;
thisDataGridDataSource = ds;
thisDataGridDataBind();
}
下面是分页的几个变量属性
public int PageCount
{
get{return thisDataGridPageCount;}
}
public int PageSize
{
get{return thisDataGridPageSize;}
}
public int PageIndex
{
get{return thisDataGridCurrentPageIndex;}
set{thisDataGridCurrentPageIndex = value;}
}
public int RecordCount
{
get{return recordCount;}
}
注册DataGrid分页事件
//分页事件处理
private void DataGrid_PageIndexChanged(object source SystemWebUIWebControlsDataGridPageChangedEventArgs e)
{
DataGrid dg = (DataGrid)source;
dgCurrentPageIndex = eNewPageIndex;
DataGridDataBind();
}
最好判断当前页面是否是第一次加载防止重复加载两次数据
private void Page_Load(object sender SystemEventArgs e)
{
if(!PageIsPostBack)
{
DataGridDataBind();
}
}
显示界面如下
src=http://imgeducitycn/img_///gif width= border=>
这个例子中没有显示分页的一些参数我们可以进一步对其进行改进
