AspNetPager与MSSQL海量分页存储过程结合

存储过程Pagination：

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[Pagination] @tblName varchar(255), -- 表名 @PKName varchar(255), -- 字段名 @strGotFields varchar(1000) = '*' , --查询字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strSort varchar(255) = '', --排序字段 @strWhere varchar(2000) = '', -- 查询条件 (注意: 不要加 where) @isCount bit = 1 --取得记录条数 AS DECLARE @strSQL varchar(6000) -- 主语句 DECLARE @strTmp varchar(1000) -- 临时变量 DECLARE @strOrder varchar(500) -- 排序类型 DECLARE @strCount varchar(1000) DECLARE @fldName varchar(255) DECLARE @sortName varchar(255) DECLARE @countSQL varchar(1000) SET @fldName = @PKName IF @strSort != '' BEGIN SET @sortName = @strSort END ELSE BEGIN SET @sortName = @PKName END IF @isCount = 1 BEGIN IF @strWhere != '' BEGIN SET @countSQL = 'select count(' + @fldName + ') from ' + @tblName + ' where ' + @strWhere END ELSE BEGIN SET @countSQL = 'select count(' + @fldName + ') from ' + @tblName END EXEC (@countSQL) RETURN END ELSE BEGIN IF @OrderType != 0 BEGIN SET @strTmp = '<(select min' SET @strOrder = ' order by ' + @sortName + ' desc' END ELSE BEGIN SET @strTmp = '>(select max' SET @strOrder = ' order by ' + @sortName +' asc' END SET @strSQL = 'select top ' + str(@PageSize) + @strGotFields + ' from ' + @tblName + ' where ' + @fldName + '' + @strTmp + '(' + @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)' + @strOrder IF @strWhere != '' SET @strSQL = 'select top ' + str(@PageSize) + @strGotFields + ' from ' + @tblName + ' where ' + @fldName + '' + @strTmp + '(' + @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder IF @PageIndex = 1 BEGIN SET @strTmp = '' IF @strWhere != '' SET @strTmp = ' where (' + @strWhere + ')' SET @strSQL = 'select top ' + str(@PageSize) + @strGotFields + ' from ' + @tblName + '' + @strTmp + ' ' + @strOrder END EXEC (@strSQL) END

.CS部分：

 #region 获取分页总数 /// <summary> /// Pg_Paging /// </summary> /// <param name="Tables"></param> /// <param name="PK"></param> /// <param name="Filter"></param> /// <returns></returns> public static int Pg_PageCount(string TableName, string PK, string Filter, string strSort) { using (SqlConnection sqlConn = new SqlConnection(DBUtility.DbHelperSQL.connectionString)) { //存储过程名 string storedProcName = "Pagination"; //设置参数 SqlParameter[] para = new SqlParameter[] { new SqlParameter("@tblName",TableName), new SqlParameter("@PKName",PK), new SqlParameter("@strGotFields","*"), new SqlParameter("@PageSize",0), new SqlParameter("@PageIndex",0), new SqlParameter("@OrderType",true), new SqlParameter("@strSort",strSort), new SqlParameter("@strWhere",Filter), new SqlParameter("@isCount",true) }; sqlConn.Open(); SqlCommand comm = DBUtility.DbHelperSQL.BuildQueryCommand(sqlConn, storedProcName, para); return int.Parse(comm.ExecuteScalar().ToString()); } } #endregion #region 获取分页列表 /// <summary> /// Pg_Paging /// </summary> /// <param name="Tables"></param> /// <param name="PK"></param> /// <param name="Sort"></param> /// <param name="PageNumber"></param> /// <param name="PageSize"></param> /// <param name="Fields"></param> /// <param name="Filter"></param> /// <param name="Group"></param> /// <returns></returns> public static DataSet Pg_Paging(string TableName, string PK, string Fields, int PageSize, int PageIndex, bool OrderType, string Sort, string Filter, bool isCount) { //存储过程名 string storedProcName = "Pagination"; //设置参数 SqlParameter[] para = new SqlParameter[] { new SqlParameter("@tblName",TableName), new SqlParameter("@PKName",PK), new SqlParameter("@strGotFields",Fields), new SqlParameter("@PageSize",PageSize), new SqlParameter("@PageIndex",PageIndex), new SqlParameter("@OrderType",OrderType), new SqlParameter("@strSort",Sort), new SqlParameter("@strWhere",Filter), new SqlParameter("@isCount",isCount) }; return DBUtility.DbHelperSQL.RunProcedure(storedProcName, para, "singleselectinfo"); } #endregion

protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { SubjectBind(); } } //绑定单选题列表 protected void SingleSelectBind() { string table = " SingleSelect "; string pk = " SingleSelectID "; string sort = "SingleSelectID"; bool ordertype = false; int pageindex = 0; int pagesize = 1; string fields = " SingleSelectID,Title "; string filter = "IsDeleted = 0"; if (AspNetPager1.CurrentPageIndex < 1) { pageindex = 1; } else { pageindex = AspNetPager1.CurrentPageIndex; } //动态设置用户自定义文本内容 AspNetPager1.RecordCount = Pg_PageCount(table, pk, filter, sort); AspNetPager1.PageSize = pagesize; this.GridView1.DataSource = Pg_Paging(table, pk, fields, pagesize, pageindex, ordertype, sort, filter, false).DefaultView; this.GridView1.DataBind(); } protected void AspNetPager1_PageChanged(object src, EventArgs e) { SingleSelectBind(); }