通用存储过程分页，分页效率还可以

存储过程如下：

/*----------------------------------------------------------------------------------
--   名稱 ：Pro_CommonPager
--   功能說明：萬能分頁程序
--   輸入資料:
  @PageIndex int,--索引页 1
  @PageSize int,--每页数量2
  @TableName nvarchar(500),--查询表名3
  @Order  nvarchar(500),--排序的列4
  @SelectStr nvarchar(500) = '*',--查询的列5
  @WhereCondition Nvarchar(1000)='',--查询条件6
  @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 7
  @Groupby NVarChar(100) = '' ,--分組 8
--   輸出資料:
  @RecordCount int = -1 out,--总行数9
  @PageCount int = -1 out,--总页数10
  @error nvarchar(100)='' out --錯誤信息11
--   注意事項:exec Pro_CommonPager 5000,15,'[User]','Id'
  本程序本摘自網上﹐經過修改﹐提高了它的效率。
--   原設計者:Achieve
--   設立日期:2008/05/15
--   ------------------------ 異動紀錄明細 -------------------------------
--   異動日期 異動者  異  動  原  因
--  2008/05/25 me  发现了一另外一种计算总行数的方法，这种方法应该还是比临时表快
    EXEC SP_EXECUTESQL @strTmp,N'@RecordCount int output',@RecordCount output
-----------------------------------------------------------------------------------*/
ALTER   PROCEDURE [dbo].[Pro_CommonPager]
(
@PageIndex int,--索引页 1
@PageSize int,--每页数量2
@TableName nvarchar(500),--查询表名3
@Order  nvarchar(500),--排序的列4
@SelectStr nvarchar(500) = '*',--查询的列5
@WhereCondition Nvarchar(1000)='',--查询条件6
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 7
@Groupby NVarChar(100) = '' ,--分組 8
@RecordCount int=-1 output,--总行数9
@PageCount int=-1 output,--总页数10
@error nvarchar(100)='' output --錯誤信息11
)
AS
declare @strSQL nvarchar(2000) -- 主语句
declare @strTmp nvarchar(1000) -- 临时变量
declare @strOrder nvarchar(1000) -- 排序类型
if @OrderType != 0
begin
 set @strTmp = '<(select min'
 set @strOrder = ' order by ' + @Order +' desc'
end
else
begin
 set @strTmp = '>(select max'
 set @strOrder = ' order by ' + @Order +' asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '
 + @TableName + ' where ' + @Order + '' + @strTmp + '(['
 + @Order + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
 + @Order + '] from ' + @TableName + '' + @strOrder + ') as tblTmp)'
 + @Groupby + @strOrder
if @WhereCondition != ''
 set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '
  + @TableName + ' where ' + @Order + '' + @strTmp + '(['
  + @Order + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
  + @Order + '] from ' + @TableName + ' where (' + @WhereCondition + ') '
  + @strOrder + ') as tblTmp) and (' + @WhereCondition + ') ' + @Groupby + @strOrder
if @PageIndex = 1
begin
 set @strTmp = ''
 if @WhereCondition != ''
  set @strTmp = ' where (' + @WhereCondition + ')'
 set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '
  + @TableName + '' + @strTmp + ' ' + @Groupby + @strOrder
end
exec (@strSQL)
if(@@error<>0)
begin
 set @error='分頁數據出錯﹗';
 return;
end
--print @strSQL
 --改進本業計算行數的方法﹐創建一個臨時表用于快速計算行數
 --create table  #tmp ( counts int );---創建臨時表
 --取得總記錄數
 IF @WhereCondition <>''
  Begin
   SET @strTmp = 'SELECT  @RecordCount=Count(*) FROM ' + @TableName + ' Where ' + (@WhereCondition)
  End
 ELSE
  Begin
   SET @strTmp = 'SELECT @RecordCount=Count(*) FROM ' + @TableName
  End
 EXEC SP_EXECUTESQL @strTmp,N'@RecordCount int output',@RecordCount output
 if(@@error<>0)
 begin
  set @error='分頁數據出錯﹗';
  --drop table #tmp;
  return;
 end
 
 --select @RecordCount=counts from #tmp
 --SET @RecordCount = @@RowCount
  --    获取总页数
 --    "CEILING"函数:取得不小于某数的最小整数
 SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
 if(@@error<>0)
 begin
  set @error='分頁數據出錯﹗';
  --drop table #tmp;
 end
 return
GO

另外，我还写一个类来调用这个存储过程进行分页。（注意，数据库操作底层是企业库）

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
namespace DAL
{
    /// <summary>
    /// 對List數據源進行分頁處理
    /// </summary>
    public class PageList
    {
        private Database _dataBase;//數據操作對像
        private DbCommand _dbCommand;
        private string _dataBaseStr = "";//數據庫連接字符

        private string _tableName = "";//查询表名
        private string _order = "";//排序的列
        private string _selectStr = "*";//查询的列
        private string _whereCondition = "";//查询条件
        private string _orderType = "0";//设置排序类型, 非 0 值则降序
        private string _groupby = "";//分組

        private int _pageSize = 15;//每頁記錄數
        private int _currentPageIndex = 1;//當前頁數
        private int _pageCount;//總頁數
        private int _recordCount;//總記錄數
        private int _currentRecordCount;//當前記當數
        private bool _isFirstPage=false;//是否為第一頁
        private bool _isLastPage=false;//是否為最后一頁

        private string _error = "";//錯誤信息

        public DbCommand DbCommands
        {
            get { return _dbCommand; }
            set { _dbCommand = value; }
        }
        /// <summary>
        /// 數據操作對像
        /// </summary>
        public Database DataBase
        {
            get { return _dataBase; }
            set { _dataBase = value; }
        }
        //數據庫連接字符
        public string DataBaseStr
        {
            get { return _dataBaseStr; }
            set { _dataBaseStr = value; }
        }
        /// <summary>
        /// 設置查询表名(必須)
        /// </summary>
        public string TableName
        {
            get { return _tableName; }
            set { _tableName = value; }
        }
        /// <summary>
        /// 設置排序的列(必須)
        /// </summary>
        public string Order
        {
            get { return _order; }
            set { _order = value; }
        }
        /// <summary>
        /// 設置查询的列,默認為所有
        /// </summary>
        public string SelectStr
        {
            get { return _selectStr; }
            set { _selectStr = value; }
        }
        /// <summary>
        /// 設置查询条件
        /// </summary>
        public string WhereCondition
        {
            get { return _whereCondition; }
            set { _whereCondition = value; }
        }
        /// <summary>
        /// 设置排序类型,默認為0 非 0 值则降序
        /// </summary>
        public string OrderType
        {
            get { return _orderType; }
            set { _orderType = value; }
        }
        /// <summary>
        /// 設置分組
        /// </summary>
        public string Groupby
        {
            get { return _groupby; }
            set { _groupby = value; }
        }
        /// <summary>
        /// 錯誤信息
        /// </summary>
        public string Error
        {
            get { return _error; }
        }
        /// <summary>
        /// 每頁記錄數(必須)默認為10
        /// </summary>
        public int PageSize
        {
            get { return _pageSize; }
            set { _pageSize = value; }
        }
        /// <summary>
        /// 當前頁數(必須)默認為1
        /// </summary>
        public int CurrentPageIndex
        {
            get { return _currentPageIndex; }
            set { _currentPageIndex = value; }
        }
        /// <summary>
        /// 總頁數
        /// </summary>
        public int PageCount
        {
            get { return _pageCount; }
        }
        /// <summary>
        /// 總記錄數
        /// </summary>
        public int RecourdCount
        {
            get { return _recordCount; }
        }
        /// <summary>
        /// 當前記錄數
        /// </summary>
        public int CurrentRecordCount
        {
            get { return _currentRecordCount; }
        }
        /// <summary>
        /// 是否為第一頁
        /// </summary>
        public bool IsFirstPage
        {
            get { return _isFirstPage; }
        }
        /// <summary>
        /// 是否為最后一頁
        /// </summary>
        public bool IsLastPage
        {
            get { return _isLastPage; }
        }

        private bool ValiData()
        {
            if (this._tableName.Trim() == "")
            {
                this._error = "沒有設置表名﹗";
                return false;
            }
            if (this._order.Trim() == "")
            {
                this._error = "沒有設置排序字段﹗";
                return false;
            }
            return true;
        }
        private void CountOther()
        {
            //是否為第一頁
            if (this._currentPageIndex == 1)
            {
                this._isFirstPage = true;  
            }
            //是否為最后一面
            if (this._currentPageIndex == this._pageCount)
            {
                this._isLastPage = true;
            }
            //當前記錄數
            this._currentRecordCount = this._currentPageIndex * this._pageSize - this._currentPageIndex + 1;
        }
        /// <summary>
        /// 返回一個DataReader用于轉換List,效率高﹐推荐使用
        /// </summary>
        /// <returns></returns>
        public List<object[]> GetPageDataList()
        {
            if (!ValiData())//檢驗設置的數據正確性
            {
                return null;
            }
            if (_dataBase == null)
            {
                this._dataBase = DatabaseFactory.CreateDatabase(this._dataBaseStr);
            }
            this._dbCommand = this._dataBase.GetStoredProcCommand("Pro_CommonPager");

            this._dataBase.AddInParameter(_dbCommand, "PageIndex", DbType.Int32, this._currentPageIndex);
            this._dataBase.AddInParameter(_dbCommand, "PageSize", DbType.Int32, this._pageSize);
            this._dataBase.AddInParameter(_dbCommand, "TableName", DbType.String, this._tableName);
            this._dataBase.AddInParameter(_dbCommand, "Order", DbType.String, this._order);
            this._dataBase.AddInParameter(_dbCommand, "SelectStr", DbType.String, this._selectStr);
            this._dataBase.AddInParameter(_dbCommand, "WhereCondition", DbType.String, this._whereCondition);
            this._dataBase.AddInParameter(_dbCommand, "OrderType", DbType.String, this._orderType);
            this._dataBase.AddInParameter(_dbCommand, "Groupby", DbType.String, this._groupby);
            this._dataBase.AddOutParameter(_dbCommand, "RecordCount", DbType.Int32, 4);
            this._dataBase.AddOutParameter(_dbCommand, "PageCount", DbType.Int32, 4);
            this._dataBase.AddOutParameter(_dbCommand, "error", DbType.String, 100);
            List<object[]> tmpList= new List<object[]>();
            using (IDataReader dr = this._dataBase.ExecuteReader(_dbCommand))
            {           
                while (dr.Read())
                {
                    object[] obj=new object[dr.FieldCount];
                    for(int i=0;i<dr.FieldCount;i++)
                    {
                        obj[i]=new object();
                        obj[i] = dr[i];
                    }
                    tmpList.Add(obj);
                }
            }
            this._error = this._dataBase.GetParameterValue(_dbCommand, "error").ToString();
            this._recordCount = (int)this._dataBase.GetParameterValue(_dbCommand, "RecordCount");
            this._pageCount = (int)this._dataBase.GetParameterValue(_dbCommand, "PageCount");
            CountOther();
            return tmpList;

        }
        /// <summary>
        /// 返回一個Table
        /// </summary>
        /// <returns></returns>
        public DataTable GetPageDataTable()
        {
            if (!ValiData())//檢驗設置的數據正確性
            {
                return null;
            }
            if (_dataBase == null)
            {
                this._dataBase = DatabaseFactory.CreateDatabase(this._dataBaseStr);
            }
            this._dbCommand = this._dataBase.GetStoredProcCommand("Pro_CommonPager");

            this._dataBase.AddInParameter(_dbCommand, "@PageIndex", DbType.Int32, this._currentPageIndex);
            this._dataBase.AddInParameter(_dbCommand, "@PageSize", DbType.Int32, this._pageSize);
            this._dataBase.AddInParameter(_dbCommand, "@TableName", DbType.String, this._tableName);
            this._dataBase.AddInParameter(_dbCommand, "@Order", DbType.String, this._order);
            this._dataBase.AddInParameter(_dbCommand, "@SelectStr", DbType.String, this._selectStr);
            this._dataBase.AddInParameter(_dbCommand, "@WhereCondition", DbType.String, this._whereCondition);
            this._dataBase.AddInParameter(_dbCommand, "@OrderType", DbType.String, this._orderType);
            this._dataBase.AddInParameter(_dbCommand, "@Groupby", DbType.String, this._groupby);
            this._dataBase.AddOutParameter(_dbCommand, "@RecordCount", DbType.Int32, 9);
            this._dataBase.AddOutParameter(_dbCommand, "@PageCount", DbType.Int32, 9);
            this._dataBase.AddOutParameter(_dbCommand, "@error", DbType.String, 100);

            DataTable dt = this._dataBase.ExecuteDataSet(_dbCommand).Tables[0];
            this._recordCount = (int)this._dataBase.GetParameterValue(_dbCommand, "@RecordCount");
            this._pageCount = (int)this._dataBase.GetParameterValue(_dbCommand, "@PageCount");
            this._error = this._dataBase.GetParameterValue(_dbCommand, "@error").ToString();

            CountOther();

            return dt;
        }
       
    }
}

使用方法：
两种调用方法，一种返回Table,一种返回List。因为取得的数据量少所以效率也没有什么多大差别。

   1.返回LIST     
        public List<Model.UserInfo> GetList(int pageIndex, ref int pageCount, ref int Count)
        {
            List<Model.UserInfo> userlist = new List<Model.UserInfo>();
            PageList pagelist = new PageList();
            pagelist.TableName = "dbo.[User]";
            pagelist.Order = "Id";
            pagelist.CurrentPageIndex = pageIndex;
            pagelist.DataBase = this._db;
            List<object[]> tmpList = pagelist.GetPageDataList();
            for (int i = 0; i < tmpList.Count; i++)
            {
                object[] obj = tmpList[i];
                Model.UserInfo userinfo = new Model.UserInfo();
                userinfo.Id = (int)obj[0];
                userinfo.Name = (string)obj[1];
                userinfo.Sex = (string)obj[2];
                userinfo.Birthday = (string)obj[3];
                userinfo.Address = (string)obj[4];
                userinfo.Email = (string)obj[5];
                userinfo.Tel = (string)obj[6];

                userlist.Add(userinfo);
            }
            Count = pagelist.RecourdCount;
            pageCount = pagelist.PageCount;
            return userlist;
        }

2.返回TABLE
        public DataTable GetDataTable(int pageIndex, ref int pageCount, ref int Count)
        {
            DataTable dt = new DataTable();
            PageList pagelist = new PageList();
            pagelist.TableName = "dbo.[User]";
            pagelist.Order = "Id";
            pagelist.CurrentPageIndex = pageIndex;
            pagelist.DataBase = this._db;
            dt=pagelist.GetPageDataTable();
            pageCount = pagelist.PageCount;
            Count = pagelist.RecourdCount;
            return dt;
        }

自己试了一下，插入1000万条记录也非常的快。

转载于:https://www.cnblogs.com/achieveLuo/archive/2008/05/26/1207462.html