自從用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];
        }