自从用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()分页
/// <summary>
/// 分页获取数据(Sql Server 2005) for ROW_NUMBER() /// </summary> /// <param name="connectionString"> 数据库链接 </param> /// <param name="sql"> 获取数据集的Sql </param> /// <param name="fldSort"> 排序字段,可以多个 </param> /// <param name="pageSize"> 每页显示多少条 </param> /// <param name="curPage"> 当前页码 </param> /// <param name="pageCount"> 总页数 </param> /// <param name="count"> 总记录数 </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 ]; }