sql2005分页储存过程和C#分页类

1、sql2005分页储存过程

--sql2005，qq524365501
create Procedure up_page2005  
 
@TableName varchar(100),  -- 表名称
@File varchar(1000),　　　 -- 列 
@praname varchar(50),　　　-- 主键,用于统计总数
@OrderFile varchar(100),  -- 排序字段 ,例如 id asc 
@PageSize varchar(10),    -- 每页数量
@PageIndex varchar(10),   -- 页码
@docount int = 0,         -- 返回记录总数, 非 0 值则返回
@Where varchar(1000)      -- 查询条件必须带and,例如 and passed=1 and deleted=0  
 
as  
declare @strsql varchar(8000)      -- 主语句
declare @strsqlcount varchar(1000) -- 统计语句
------------统计总条数begin-----------
if @docount != 0
    begin
        if @Where != ''
            set @strsqlcount ='select count(' + @praname + ' ) as total from ' + @TableName +' where 1=1 '+@Where+';'
        else
            set @strsqlcount ='select count(' + @praname + ' ) as total from ' + @TableName+';'
    end 
    --以上代码的意思是如果@docount传递过来的不是0，就执行总数统计
else
    begin
        set @strsqlcount='';
    end
------------统计总条数end-----------
------------查询主数据begin---------

set @strsql = 'select ' + @File + ' from (select *,ROW_NUMBER() over(order by ' + @OrderFile + ') as ''rowNumber''  from ' + @TableName + '  where 1=1 ' + @Where + '   ) temp where rowNumber between (((' + @PageIndex + ' - 1) * ' + @PageSize + ')+1) and (' + @PageIndex + '*'+ @PageSize+');'
exec(@strsql+@strsqlcount)

 

 

2、C#分页类

using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
///PagerClass 的摘要说明
/// </summary>
public class PagerClass
{
    /// <summary>
    /// 分页数据，返回DataSet，tablse[0]=主数据，tablse[1]=总条数
    /// </summary>
    /// <param name="DBname">表名称，可以是多个</param>
    /// <param name="DBdata">返回的字段名称</param>
    /// <param name="IDname">主键，用于统计总数用</param>
    /// <param name="orderby">排序</param>
    /// <param name="pageSize">每页数量</param>
    /// <param name="pageNow">当前页</param>
    /// <param name="where">条件</param>
    /// <param name="doCount">0=不统计总条数，1=统计总条数</param>
    /// <returns></returns>
    public static DataSet pageData(string DBname, string DBdata, string IDname, string orderby, int pageSize, int pageNow, string where, int doCount)
    {
        DataSet ds = new DataSet();
        try
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@TableName", SqlDbType.VarChar,100),
                    new SqlParameter("@File", SqlDbType.VarChar,1000),
                    new SqlParameter("@praname", SqlDbType.VarChar,50),
                    new SqlParameter("@OrderFile", SqlDbType.VarChar,100),
                    new SqlParameter("@PageSize", SqlDbType.VarChar,10),
                    new SqlParameter("@PageIndex", SqlDbType.VarChar,10),
                    new SqlParameter("@docount", SqlDbType.Int),
                    new SqlParameter("@Where", SqlDbType.VarChar,1000),
            };
            parameters[0].Value = DBname;
            parameters[1].Value = DBdata;
            parameters[2].Value = IDname;
            parameters[3].Value = orderby;
            parameters[4].Value = pageSize;
            parameters[5].Value = pageNow;
            parameters[6].Value = doCount;
            parameters[7].Value = where;
            ds = DbHelperSQL.RunProcedure("up_page2005", parameters, "ds");
        }
        catch
        {
            return ds;
        }
        return ds;
    }
}