asp.net sql 分页，，优化 排序 及分页，

调用代码：

<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>

         private void bind()
         {
             string sqlwhere = "CreatorId=\'" + user.Id.ToString() + "\'";
             Pager page = new Pager();
             page.Procedure = "SPager";
             page.PageIndex = pageindex;
             page.SelectStr = "*";
             page.PageSize = pagesize;
             page.TableName = "Papers";
             page.Pagekey = "Id";
             page.Subkey = "Id";
             page.PagekeyOrderType = ;
             page.Order = "";
             page.WhereCondition = sqlwhere;
             DataTable dt = page.GetDatas(pageindex);
             listpage.RecordCount = page.RecordCount;
             this.r_scoreCount.DataSource = dt.DefaultView;
             this.r_scoreCount.DataBind();

         }
         //分页控件绑定
         protected void listpage_PageChanged(object sender, EventArgs e)
         {
             pageindex = listpage.CurrentPageIndex;
             bind();
         }

C#代码

 using System;
 using System.Collections.Generic;
 using System.Data;
 using System.Data.SqlClient;
 using System.Configuration;
 //using DocumentTransfer.DAL;

 namespace ExamOnline.Utils
 {
     /// <summary>
     /// 分页类,通过存储过程进行分页
     /// </summary>
     public class Pager
     {
         #region 参数
         private int tableNo;
         private int pageIndex = ;
         private int pageSize = ;
         private int rowCount;
         private string procedure = "pager";
         private string tableName = "";
         private string whereCondition = "1=1";
         private string selectStr = "*";
         private string pagekey = "";
         private string subkey = "";
         private int pagekeyorderType = ;
         private string order = "";
         private string _FirstStr = "";
         private string _PrevStr = "";
         private string _NextStr = "";
         private string _LastStr = "";
         private string _TurnUrlStr = "";
         private string _Options = "";
         private string strCountww = "";  //共N条信息
         private string strPageww = "";    //第N页/共N页
         private string strTurnww;  //跳转控件
         private string pageindexName = "page";
         private string _ConnString = SqlHelper.ConnectionString;
         /// <summary>
         /// 信息池连接字符串
         /// </summary>
         //public static string PoolConnString = "Data Source=10.10.101.79;Initial Catalog=CER_Master;Persist Security Info=True;User ID=sa;Password=123";
         /////////////////////2011-05-27张立华修改.连接字符串都从web.config中获取.////////////////////
         public static string PoolConnString =ConfigurationManager.AppSettings["SqlConnMaster"];
         /// <summary>
         /// 所要操作的存储过程名称,已有默认的分页存储过程
         /// </summary>
         public string Procedure
         {
             get
             {
                 return procedure;
             }
             set
             {
                 if (value == null || value.Length <= )
                 {
                     procedure = "pager";
                 }
                 else
                 {
                     procedure = value;
                 }
             }
         }

         /// <summary>
         /// 当前所要显示的页面数
         /// </summary>
         public int PageIndex
         {
             get
             {
                 return pageIndex;
             }
             set
             {
                 pageIndex = value;
             }
         }

         /// <summary>
         /// 总的页面数
         /// </summary>
         public int PageCount { get; set; }

         /// <summary>
         /// 总行数
         /// </summary>
         public int RecordCount { get; set; }

         /// <summary>
         /// 每页条数
         /// </summary>
         public int PageSize
         {
             get
             {
                 return pageSize;
             }
             set
             {
                 pageSize = value;
             }
         }

         /// <summary>
         /// 表名称
         /// </summary>
         public string TableName
         {
             get
             {
                 return tableName;
             }
             set
             {
                 tableName = value;
             }
         }

         /// <summary>
         /// 条件查询
         /// </summary>
         public string WhereCondition
         {
             get
             {
                 return whereCondition;
             }
             set
             {
                 whereCondition = value;
             }
         }

         /// <summary>
         /// 查询目标(搜索目标),比如:AddTime AS 时间,ID AS 编号
         /// </summary>
         public string SelectStr
         {
             get
             {
                 return selectStr;
             }
             set
             {
                 selectStr = value;
             }
         }
         /// <summary>
         /// /
         /// </summary>
         public string Subkey
         {
             get
             {
                 return subkey;
             }
             set
             {
                 subkey = value;
             }
         }

         /// <summary>
         /// 排序表达式
         /// </summary>
         public string Pagekey
         {
             get
             {
                 return pagekey;
             }
             set
             {
                 pagekey = value;
             }
         }
         /// <summary>
         /// 排序类型 true:asc false:desc
         /// </summary>
         public int PagekeyOrderType
         {
             get
             {
                 return pagekeyorderType;
             }
             set
             {
                 pagekeyorderType = value;
             }
         }
         /// <summary>
         ///
         /// </summary>
         public string Order
         {
             get
             {
                 return order;
             }
             set
             {
                 order = value;
             }
         }
         /// <summary>
         /// 得到当前返回的数量
         /// </summary>
         public int RowCount
         {
             get
             {
                 return rowCount;
             }
         }
         /// <summary>
         /// 首页 显示样式
         /// </summary>
         public string FirstStr
         {
             get { return _FirstStr; }
             set { _FirstStr = value; }
         }

         /// <summary>
         /// 上一页 显示样式
         /// </summary>
         public string PrevStr
         {
             get { return _PrevStr; }
             set { _PrevStr = value; }
         }

         /// <summary>
         /// 下一页 显示样式
         /// </summary>
         public string NextStr
         {
             get { return _NextStr; }
             set { _NextStr = value; }
         }
         /// <summary>
         /// 尾页 显示样式
         /// </summary>
         public string LastStr
         {
             get { return _LastStr; }
             set { _LastStr = value; }
         }
         /// <summary>
         /// 跳转 的url链接
         /// </summary>
         public string TurnUrlStr
         {
             get { return _TurnUrlStr; }
             set { _TurnUrlStr = value; }
         }
         /// <summary>
         /// 跳转的url链接的参数前面不要加问号和与号
         /// </summary>
         public string Options
         {
             get { return _Options; }
             set { _Options = value; }
         }
         /// <summary>
         /// 分页参数名称
         /// </summary>
         public string PageIndexName
         {
             get { return pageindexName; }
             set { pageindexName = value; }
         }
         /// <summary>
         /// 连接字符串
         /// </summary>
         public string ConnString
         {
             get { return _ConnString; }
             set { _ConnString = value; }
         }
         #endregion 参数
         /// <summary>
         /// 分页查寻结果
         /// </summary>
         public DataTable GetDatas(int pageIndex)
         {
             this.pageIndex = pageIndex;
             Pager pager = this;
             DataTable returnTb = Pagination(ref pager).Tables[];
             rowCount = returnTb.Rows.Count;
             return returnTb;
         }

         /// <summary>
         /// 分页操作存储过程函数
         /// </summary>
         /// <param name="pager">Pager</param>
         /// <returns>返回DataSet</returns>
         private static DataSet Pagination(ref Pager pager)
         {
             SqlParameter[] par = new SqlParameter[];
             par[] = new SqlParameter("@TableName",SqlDbType.NVarChar,);
             par[].Value = pager.TableName;
             par[] = new SqlParameter("@SelectStr", SqlDbType.NVarChar, );
             par[].Value = pager.SelectStr;
             par[] = new SqlParameter("@Pagekey", SqlDbType.NVarChar, );
             par[].Value = pager.Pagekey;
             par[] = new SqlParameter("@Subkey", SqlDbType.NVarChar, );
             par[].Value = pager.Subkey;
             par[] = new SqlParameter("@KeyOrderType", SqlDbType.Int);
             par[].Value = pager.PagekeyOrderType;

             par[] = new SqlParameter("@pageSize",SqlDbType.Int);
             par[].Value = pager.PageSize;
             par[] = new SqlParameter("@PageIndex", SqlDbType.Int);
             par[].Value = pager.pageIndex;

             par[] = new SqlParameter("@RecordCount",SqlDbType.Int);
             par[].Direction = ParameterDirection.InputOutput;
             par[] = new SqlParameter("@PageCount",SqlDbType.Int);
             par[].Direction = ParameterDirection.InputOutput;

             par[] = new SqlParameter("@Order", SqlDbType.NVarChar, );
             par[].Value = pager.Order;

             par[] = new SqlParameter("@WhereCondition", SqlDbType.NVarChar, );
             par[].Value = pager.WhereCondition;
             DataSet ds = SqlHelper.ExecuteDataset(pager.ConnString,CommandType.StoredProcedure,pager.Procedure,par);
             pager.RecordCount = (int)par[].Value;
             pager.PageCount = (int)par[].Value;
             return ds;
         }
         #region 返回分页后的页码显示
         /// <summary>
         /// 返回分页后的页码显示
         /// </summary>
         /// <param name="bolCount">是否显示 共N条信息</param>
         /// <param name="bolPage">是否显示 第N页/共N页</param>
         /// <param name="bolFirst">是否显示 首页</param>
         /// <param name="bolLast">是否显示 尾页</param>
         /// <param name="bolTurn">是否显示 跳转控件</param>
         /// <param name="IsChinese">是否 用中文显示</param>
         /// <param name="intStyle">样式选择 1:字符 2:符号</param>
         /// <param name="intShowNum">每页显示多少个数字</param>
         /// <param name="isHtml">是否HTML分页</param>
         /// <param name="exName">如果为HTML分页,要输入HTML后缀名</param>
         /// <returns>返回分页后的页码显示</returns>
         public string GetShowPageStr(bool bolCount, bool bolPage, bool bolFirst, bool bolLast, bool bolTurn, bool IsChinese, int intStyle, int intShowNum,bool isHtml,string exName)
         {
             string strPageShowww = "";
             string _FirstStr2 = "";
             string _PrevStr2 = "";
             string _NextStr2 = "";
             string _LastStr2 = "";

              #region 公共处理
             //总页数
             PageCount = (RecordCount + pageSize - ) / pageSize;

             //超出最小页码
             if (pageIndex < )
             {
                 pageIndex = ;
             }

             //超出最大页码
             if (pageIndex > PageCount)
             {
                 pageIndex = PageCount;
             }

             if (IsChinese)//中文分页
             {
                 //跳转
                 strTurnww = String.Format("<input value='{0}' id='txtPageGo' name='txtPageGo' type='text' style='width:35px;'><input name='btnGo' type='button' id='btnGo' value='跳转' onclick=\"javascript:window.location.href='{1}?Page=' + document.getElementById('txtPageGo').value + '&{2}'\">", pageIndex, _TurnUrlStr, Options);
                 //共N条信息
                 strCountww = String.Format("共 {0} 条信息", RecordCount);
                 //第N页/共N页
                 strPageww = String.Format("第{0}页/共{1}页", pageIndex, PageCount);

                 //处理页码显示样式
                 if (intStyle == )
                 {
                     if (_FirstStr == "")
                     {
                         _FirstStr = "首页";
                     }
                     if (_PrevStr == "")
                     {
                         _PrevStr = "上一页";
                     }
                     if (_NextStr == "")
                     {
                         _NextStr = "下一页";
                     }
                     if (_LastStr == "")
                     {
                         _LastStr = "尾页";
                     }
                 }
                 else
                 {
                     if (_FirstStr == "")
                     {
                         _FirstStr = " << ";
                     }
                     if (_PrevStr == "")
                     {
                         _PrevStr = " < ";
                     }
                     if (_NextStr == "")
                     {
                         _NextStr = " > ";
                     }
                     if (_LastStr == "")
                     {
                         _LastStr = " >> ";
                     }
                 }
             }
             else//英文文分页
             {
                 //跳转
                 strTurnww = String.Format("<input value='{0}' id='txtPageGo' name='txtPageGo' type='text' style='width:35px;'><input name='btnGo' type='button' id='btnGo' value='Goto' onclick=\"javascript:window.location.href='{1}?Page=' + document.getElementById('txtPageGo').value + '&{2}'\">", pageIndex, _TurnUrlStr, Options);
                 //共N条信息
                 strCountww = String.Format("Total {0} Infos", RecordCount);
                 //第N页/共N页
                 strPageww = String.Format(" {0}/{1} ", pageIndex, PageCount);

                 //处理页码显示样式
                 if (intStyle == )
                 {
                     if (_FirstStr == "")
                     {
                         _FirstStr = " First ";
                     }
                     if (_PrevStr == "")
                     {
                         _PrevStr = " Previous ";
                     }
                     if (_NextStr == "")
                     {
                         _NextStr = " Next ";
                     }
                     if (_LastStr == "")
                     {
                         _LastStr = " Last ";
                     }
                 }
                 else
                 {
                     if (_FirstStr == "")
                     {
                         _FirstStr = " << ";
                     }
                     if (_PrevStr == "")
                     {
                         _PrevStr = " < ";
                     }
                     if (_NextStr == "")
                     {
                         _NextStr = " > ";
                     }
                     if (_LastStr == "")
                     {
                         _LastStr = " >> ";
                     }
                 }
             }
             #endregion

             //没有记录
             if (RecordCount <= )
             {
                 strPageShowww = strCountww;
             }
             //有记录
             else
             {
                 //只有一页
                 if (PageCount <= )
                 {
                     strPageShowww = String.Format("{0}  {1}", strCountww, strPageww);
                 }
                 //不止一页
                 else
                 {
                     //页码链接处理
                     #region 页码链接处理
                     //第一页
                     if (pageIndex == )
                     {
                         _FirstStr2 = _FirstStr;
                         _PrevStr2 = _PrevStr;
                     }
                     else
                     {
                         if (isHtml)
                         {
                             _FirstStr2 = String.Format("<a href=\"{0}.{1}\">{2}</a>", _TurnUrlStr, exName, _FirstStr);
                             if (pageIndex -  == )
                             {
                                 _PrevStr2 = String.Format("<a href=\"{0}.{1}\">{2}</a>", _TurnUrlStr, exName, _PrevStr);
                             }
                             else
                             {
                                 _PrevStr2 = String.Format("<a href=\"{0}_{1}.{2}\">{3}</a>", _TurnUrlStr, Convert.ToString(pageIndex - ), exName, _PrevStr);
                             }
                         }
                         else
                         {
                             _FirstStr2 = String.Format("<a href=\"{0}?{1}=1&{2}\">{3}</a>", _TurnUrlStr, pageindexName, _Options, _FirstStr);
                             _PrevStr2 = String.Format("<a href=\"{0}?{1}={2}&{3}\">{4}</a>", _TurnUrlStr, pageindexName, Convert.ToString(pageIndex - ), _Options, _PrevStr);
                         }
                     }

                     //最后一页
                     if (pageIndex == PageCount)
                     {
                         _NextStr2 = _NextStr;
                         _LastStr2 = _LastStr;
                     }
                     else
                     {
                         if (isHtml)
                         {
                             _NextStr2 = String.Format("<a href=\"{0}_{1}.{2}\">{3}</a>", _TurnUrlStr, Convert.ToString(pageIndex + ), exName, _NextStr);
                             _LastStr2 = String.Format("<a href=\"{0}_{1}.{2}\">{3}</a>", _TurnUrlStr, PageCount, exName, _LastStr);
                         }
                         else
                         {
                             _NextStr2 = String.Format("<a href=\"{0}?{1}={2}&{3}\">{4}</a>", _TurnUrlStr, pageindexName, Convert.ToString(pageIndex + ), _Options, _NextStr);
                             _LastStr2 = String.Format("<a href=\"{0}?{1}={2}&{3}\">{4}</a>", _TurnUrlStr, pageindexName, PageCount, _Options, _LastStr);
                         }
                     }

                     //----处理显示页码-----------
                     if (bolCount == true)//共N条信息
                     {
                         strPageShowww = String.Format("{0}  {1}", strPageShowww, strCountww);
                     }
                     if (bolPage == true)//第N页/共N页
                     {
                         strPageShowww = String.Format("{0}  {1}", strPageShowww, strPageww);
                     }
                     if (bolFirst == true) //首页
                     {
                         strPageShowww = String.Format("{0}  {1}", strPageShowww, _FirstStr2);
                     }
                     strPageShowww = strPageShowww + "{0}";//上一页
                     //下一页

                     if (bolLast == true)
                         //尾页
                         strPageShowww = String.Format("{0}  {1}", strPageShowww + "{1}{2}", _LastStr2);
                     else
                         strPageShowww = strPageShowww + "{1}{2}";
                     if (bolTurn == true)//跳转控件
                     {
                         strPageShowww = String.Format("{0}  {1}", strPageShowww, strTurnww);
                     }

                     #endregion
                     #region 样式一: 共X条信息 第N页/共M页 首页 上一页 下一页 尾页  跳转
                     if (intStyle == )
                     {
                         strPageShowww = strPageShowww.Replace("{0}", "  " + _PrevStr2);//上一页
                         strPageShowww = strPageShowww.Replace("{1}", "  " + _NextStr2);//下一页
                         strPageShowww = strPageShowww.Replace("{2}", "");//
                     }
                     #endregion
                     #region 样式二: 共X条信息 第N页/共M页 首页 1 2 3 尾页 跳转

                     if (intStyle == )
                     {
                         int PageTemp = ;
                         string strPageNum = "";
                         string strTempNow = "";

                         //当页码超过最后一批该显示
                         if (pageIndex > PageCount - intShowNum + )
                         {
                             PageTemp = PageCount < intShowNum ?  : PageCount - intShowNum;
                             for (int i = ; i <= intShowNum; i++)
                             {
                                 if (i > PageCount) break;

                                 strTempNow = Convert.ToString(PageTemp + i);

                                 //当前页不显示超链接
                                 if( PageIndex == PageTemp + i)
                                 {
                                     strPageNum = String.Format("{0}<b>{1}</b> ", strPageNum, strTempNow);
                                 }
                                 else
                                 {
                                     if (isHtml)
                                     {
                                         if (strTempNow == "")
                                         {
                                             strPageNum = String.Format("{0}<a href=\"{1}.{2}\">{3}</a> ", strPageNum, _TurnUrlStr, exName, strTempNow);
                                         }
                                         else
                                         {
                                             strPageNum = String.Format("{0}<a href=\"{1}_{2}.{3}\">{2}</a> ", strPageNum, _TurnUrlStr, strTempNow, exName);
                                         }
                                     }
                                     else
                                     {
                                         strPageNum = String.Format("{0}<a href=\"{1}?{2}={3}&{4}\">{3}</a> ", strPageNum, _TurnUrlStr, pageindexName, strTempNow, _Options);
                                     }
                                 }
                             }
                         }
                         else
                         {
                             for (int i = ; i < intShowNum; i++)
                             {
                                 strTempNow = Convert.ToString(PageIndex + i);

                                 //当前页不显示超链接
                                 if (i == )
                                 {
                                     strPageNum = String.Format("{0}<b>{1}</b> ", strPageNum, strTempNow);
                                 }
                                 else
                                 {
                                     if (isHtml)
                                     {
                                         if (strTempNow == "")
                                         {
                                             strPageNum = String.Format("{0}<a href=\"{1}.{2}\">{3}</a> ", strPageNum, _TurnUrlStr, exName, strTempNow);
                                         }
                                         else
                                         {
                                             strPageNum = String.Format("{0}<a href=\"{1}_{2}.{3}\">{2}</a> ", strPageNum, _TurnUrlStr, strTempNow, exName);
                                         }
                                     }
                                     else
                                     {
                                         strPageNum = String.Format("{0}<a href=\"{1}?P{2}={3}&{4}\">{3}</a> ", strPageNum, _TurnUrlStr, pageindexName, strTempNow, _Options);
                                     }
                                 }
                             }
                         }

                         //
                         strPageShowww = strPageShowww.Replace("{0}", "  " + _PrevStr2);//上一页
                         strPageShowww = strPageShowww.Replace("{1}", "  " + strPageNum);//显示数字
                         strPageShowww = strPageShowww.Replace("{2}", "  " + _NextStr2);//下一页
                     }
                     #endregion
                 }
             }
             return strPageShowww;
         }
         #endregion

     }
 }

sql 存储过程。

 USE [ExamOnline]
 GO
 /****** Object:  StoredProcedure [dbo].[SPager]    Script Date: 06/13/2015 14:00:45 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 ALTER PROCEDURE [dbo].[SPager]
 (
 @TableName VARCHAR(), -- 表名(注意：可以多表链接)
 @SelectStr VARCHAR() = '', -- 需要返回的列
 @Pagekey VARCHAR()='',--必选，分页标识键，尽量保证无重复
 @Subkey VARCHAR()='', --标识键别名，用于子流程
 @KeyOrderType BIT = , -- 设置排序类型, 非  值则降序
 @PageSize INT = , -- 页尺寸
 @PageIndex INT = , -- 页码
 @RecordCount INT =  OUT, --查询到的记录数
 @PageCount INT =  OUTPUT,--总页数4
 @Order VARCHAR()='', -- 分页后排序 “order by yufa”
 @WhereCondition VARCHAR() = '' -- 查询条件 (注意: 不要加 where)
 )
 AS
 DECLARE @strSQL NVARCHAR() -- 主语句
 DECLARE @strTmp VARCHAR() -- 临时变量
 DECLARE @strPagekeyOrder VARCHAR() -- 排序类型

 IF @WhereCondition != ''
 SET @strSQL = 'select @RecordCount=count(*) from ' + @TableName + ' where '+@WhereCondition
 ELSE
 SET @strSQL = 'select @RecordCount=count(*) from ' + @TableName
 EXEC sp_executesql @strSQL,N'@RecordCount int out',@RecordCount OUT
 --以上代码的意思是如果@RecordCount传递过来的不是0，就执行总数统计。以下的所有代码都是@RecordCount为0的情况
 SET @strSQL='';
 SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

 IF @KeyOrderType !=
 BEGIN
 SET @strTmp = '<(select min'
 SET @strPagekeyOrder = ' order by ' + @Subkey +' desc'--如果@OrderType不是0，就执行降序，这句很重要！
 END
 ELSE
 BEGIN
 SET @strTmp = '>(select max'
 SET @strPagekeyOrder = ' order by ' + @Subkey +' asc'
 END
 IF @PageIndex =
 BEGIN
 IF @WhereCondition != ''
 SET @strSQL = 'select top ' + STR(@PageSize) +' '+@SelectStr+ ' from ' + @TableName + ' where ' + @WhereCondition + ' ' + @strPagekeyOrder
 ELSE
 SET @strSQL = 'select top ' + STR(@PageSize) +' '+@SelectStr+ ' from '+ @TableName + ' '+ @strPagekeyOrder--如果是第一页就执行以上代码，这样会加快执行速度
 END
 ELSE
 begin--以下代码赋予了@strSQL以真正执行的SQL代码
 SET @strSQL = 'select top ' + STR(@PageSize) +' '+@SelectStr+ ' from '
 + @TableName + ' where ' + @Pagekey + ' ' + @strTmp + '('+ @Subkey + ') from (select top ' + STR((@PageIndex-)*@PageSize) + ' '+ @SelectStr + ' from ' + @TableName + ' ' + @strPagekeyOrder + ') as tblTmp)'+ @Order
 IF @WhereCondition != ''
 SET @strSQL = 'select top ' + STR(@PageSize) +' '+@SelectStr+ ' from '
 + @TableName + ' where ' + @Pagekey + ' ' + @strTmp + '('+ @Subkey + ') from (select top ' + STR((@PageIndex-)*@PageSize) + ' '+ @SelectStr + ' from ' + @TableName + ' where ' + @WhereCondition + ' '+ @strPagekeyOrder + ') as tblTmp) and ' + @WhereCondition + ' ' + @Order
 END
 EXEC (@strSQL)