快捷導(dǎo)航

asp.net中用DataReader高效率分頁

更新時間：2011年09月09日 17:43:52 作者：

自從用Sql2005版本以后一直用ROW_NUMBER()分頁，最近一個項目維護sqlserver是2000，沒辦法重寫了分頁。寫完測試分析比ROW_NUMBER()明顯快啊

分享一下兩種方式的分頁代碼
1.用DataReader分頁

 
/// <summary> 
/// PageList for DataReader 
/// </summary> 
/// <param name="connectionString"></param> 
/// <param name="sql"></param> 
/// <param name="pageSize"></param> 
/// <param name="curPage"></param> 
/// <param name="pageCount"></param> 
/// <param name="count"></param> 
/// <param name="cmdParms"></param> 
/// <returns></returns> 
public DataTable PageListReader(string connectionString, string sql, int pageSize, int curPage, out int pageCount, out int count, params DbParameter[] cmdParms) 
{ 
int first = 0; 
int last = 0; 
int fieldCount = 0; 
using (SqlConnection conn = new SqlConnection(connectionString)) 
{ 
SqlCommand cmd = conn.CreateCommand(); 
PrepareCommand(cmd, conn, null, CommandType.Text, sql, cmdParms); 
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
DataTable dt = new DataTable(); 
fieldCount = reader.FieldCount; 
for (int i = 0; i < fieldCount; i++) 
{ 
DataColumn col = new DataColumn(); 
col.ColumnName = reader.GetName(i); 
col.DataType = reader.GetFieldType(i); 
dt.Columns.Add(col); 
} 
count = 0; 
first = (curPage - 1) * pageSize+1; 
last = curPage * pageSize; 
while (reader.Read()) 
{ 
count++; 
if (count >= first && last >= count) 
{ 
DataRow r = dt.NewRow(); 
for (int i = 0; i < fieldCount; i++) 
{ 
r[i] = reader[i]; 
} 
dt.Rows.Add(r); 
} 
} 
reader.Close(); 
pageCount = Convert.ToInt32(Math.Ceiling((double)count / (double)pageSize)); 
return dt; 
} 
} 

2.用ROW_NUMBER()分頁

復(fù)制代碼代碼如下:

 
/// <summary> 
/// 分頁獲取數(shù)據(jù)(Sql Server 2005) for ROW_NUMBER() 
/// </summary> 
/// <param name="connectionString">數(shù)據(jù)庫鏈接</param> 
/// <param name="sql">獲取數(shù)據(jù)集的Sql</param> 
/// <param name="fldSort">排序字段,可以多個</param> 
/// <param name="pageSize">每頁顯示多少條</param> 
/// <param name="curPage">當(dāng)前頁碼</param> 
/// <param name="pageCount">總頁數(shù)</param> 
/// <param name="count">總記錄數(shù)</param> 
///<param name="cmdParms">DbParameter</param> 
/// <returns>DataTable</returns> 
public DataTable PageList(string connectionString, string sql, string fldSort, int pageSize, int curPage, out int pageCount, out int count, params DbParameter[] cmdParms) 
{ 
StringBuilder strSql = new StringBuilder(); 
strSql.AppendFormat(@"SELECT count(0) from {0} as MyTableCount; 
select * from ( 
SELECT ROW_NUMBER() OVER(order by {1}) RowNumber,* 
from {0} mytable 
) mytable2 
where RowNumber between {2} and {3}" 
, sql, fldSort, Convert.ToString((curPage - 1) * pageSize + 1), Convert.ToString((curPage * pageSize))); 
DataSet ds = ExecuteQuery(connectionString, CommandType.Text, strSql.ToString(), cmdParms); 
count = Convert.ToInt32(ds.Tables[0].Rows[0][0]); 
pageCount = Convert.ToInt32(Math.Ceiling((double)count / (double)pageSize)); 
return ds.Tables[1]; 
} 