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

存储过程Pagination：

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 --取得记录条数ASDECLARE @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 = @PKNameIF @strSort != ''BEGINSET @sortName = @strSortENDELSEBEGINSET @sortName = @PKNameENDIF @isCount = 1BEGINIF @strWhere != ''BEGINSET @countSQL = 'select count(' + @fldName + ') from ' + @tblName + ' where ' + @strWhereENDELSEBEGINSET @countSQL = 'select count(' + @fldName + ') from ' + @tblNameENDEXEC (@countSQL)RETURNENDELSEBEGINIF @OrderType != 0BEGINSET @strTmp = '<(select min'SET @strOrder = ' order by ' + @sortName + ' desc'ENDELSEBEGINSET @strTmp = '>(select max'SET @strOrder = ' order by ' + @sortName +' asc'ENDSET @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)'+ @strOrderIF @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 + ' ' + @strOrderIF @PageIndex = 1BEGINSET @strTmp = ''IF @strWhere != ''SET @strTmp = ' where (' + @strWhere + ')'SET @strSQL = 'select top ' + str(@PageSize) + @strGotFields + ' from '+ @tblName + '' + @strTmp + ' ' + @strOrderENDEXEC (@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();}