存储过程如下:
/*----------------------------------------------------------------------------------
-- 名稱 :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万条记录也非常的快。