本分页实现没使用Gridview的自带分页,因为我很懒,自己根据公司现有的情况写了一个,很烂,是个回发分页,想看url分页的就当打酱油了,贴上来求大牛指点一下,我还停留在代码的阶段,泪奔...
数据库有一个获取数据的存储过程,我感觉写的很好,我的这个自定义分页完全依托与这个存储过程。
先上存储过程
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go create PROCEDURE [dbo].[sp_PageData] @TableName NVARCHAR(50), @SelectFields NVARCHAR(500), @PageIndex INT, @PageSize INT, @Condition NVARCHAR(4000), @Sort NVARCHAR(100), @RowCount INT output AS BEGIN IF LEN(@Condition) > 0 BEGIN SET @Condition = ' WHERE ' + @Condition; END IF LEN(@Sort) = 0 BEGIN SET @Sort = 'id DESC'; END --获取@RowCount信息 DECLARE @sql NVARCHAR(4000) SET @sql = 'SELECT @TEMP = COUNT(id) FROM ' + @TableName + @Condition EXEC sp_executesql @sql, N'@TEMP INT OUTPUT', @RowCount OUTPUT DECLARE @StartRowIndex INT; --根据页码和每页大小计算起始行 IF @PageIndex < 1 BEGIN SET @PageIndex = 1 END DECLARE @PageCount INT SET @PageCount = ceiling(cast(@RowCount as float) / @PageSize) IF @PageIndex > @PageCount BEGIN SET @PageIndex = @PageCount END SET @StartRowIndex = ( @PageIndex - 1 )* @PageSize + 1 EXEC (' --声明一个读取主键的游标 DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR --这里只读取主键,并插入表里 SELECT id FROM ' + @TableName + @Condition + ' ORDER BY ' + @Sort ) DECLARE @IDs NVARCHAR(2000) SET @IDs = '0' DECLARE @PK NVARCHAR(50) --打开游标 OPEN PagingCursor --直接跳到起始行 FETCH RELATIVE @StartRowIndex FROM PagingCursor INTO @PK --不返回统计的行数 SET NOCOUNT ON --开始循环读取记录 WHILE @PageSize > 0 AND @@FETCH_STATUS = 0 BEGIN --INSERT @tblPK (PK) VALUES ( @PK ) SET @IDs = @IDs + ',' + @PK FETCH NEXT FROM PagingCursor INTO @PK SET @PageSize = @PageSize - 1 END CLOSE PagingCursor DEALLOCATE PagingCursor EXEC('SELECT ' + @SelectFields + ' FROM ' + @TableName + ' WHERE ID IN (' + @IDs+ ') ORDER BY ' + @Sort ) END
aspx页面代码
共有< %# grRowCount%>条记录 第 页 共 页
aspx.cs代码
public int grRowCount = 0; public int currentIndex = 0; public int grPageCount = 0; protected void LinkClickPage(object sender, EventArgs e) { LinkButton clickBtn = sender as LinkButton; int pageIndex = Convert.ToInt32(clickBtn.CommandArgument); pageIndex = (pageIndex > -1) ? pageIndex : Convert.ToInt32((clickBtn.Parent.FindControl("txtNewPageIndex") as TextBox).Text) - 1; pageIndex = (pageIndex > -1) ? pageIndex : 0; BandData(pageIndex); } private void BandData(int pageIndex) { GridView gr = this.FindControl("GridView1") as GridView; DataTable source = new DataTable(); source = GetPagerData(ConnectString, gr.PageSize, string.Empty, pageIndex + 1, string.Empty, "*", TableName, out grRowCount); grPageCount = (grRowCount - (grRowCount % gr.PageSize)) / gr.PageSize + (((grRowCount % gr.PageSize) == 0) ? 0 : 1); pageIndex = (pageIndex < 0) ? 0 : pageIndex; pageIndex = (pageIndex + 1 > grPageCount) ? grPageCount - 1 : pageIndex; currentIndex = pageIndex; hidPageIndex.Value = currentIndex.ToString(); //绑定数据 gr.DataSource = source; gr.DataBind(); } public DataTable GetPagerData(string connstr,int pageSize, string condition, int pageIndex, string sortField, string selectField, string tableName, out int rowCount) { DataTable da = null; SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@TableName",tableName), new SqlParameter("@SelectFields",selectField), new SqlParameter("@PageIndex",pageIndex), new SqlParameter("@PageSize",pageSize), new SqlParameter("@Condition",condition), new SqlParameter("@Sort",sortField), new SqlParameter("@RowCount",SqlDbType.Int) }; parms[parms.Length - 1].Direction = ParameterDirection.Output; da = SqlHelper.ExecuteDatatable(connstr, CommandType.StoredProcedure, "sp_PageData", parms); rowCount = Convert.ToInt32(parms[parms.Length - 1].Value); return da; } //强制让gridview的PagerTemplate显示 protected void GridView1_DataBound(object sender, EventArgs e) { if (GridView1.Rows.Count != 0) { Control table = GridView1.Controls[0]; int count = table.Controls.Count; table.Controls[count - 1].Visible = true; } }
实现原理,这是一个回发分页,通过LinkButton的点击事件处理分页,以CommandArgument来获取pageIndex。还通过一个hidden控件保存了pageIndex,避免别的控件引起回发(例如LinkButton删除某条记录)后无法绑定当前页数据。