■使用DataGrid时自带的分页实现起来...... 使用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 * 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 获取记录数和页数都采用存储过程的输出参数。 获取数据源,这里返回一个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(); } } 显示界面如下: 这个例子中没有显示分页的一些参数,我们可以进一步对其进行改进。 |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-29 23:40 , Processed in 0.131577 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.