posts - 14,  comments - 69,  trackbacks - 0

 自从用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];
        }
posted on 2011-04-26 11:30  @清风......  阅读(...)  评论(... 编辑 收藏