sqlserver 分页

SQL语句：

create PROCEDURE [dbo].[GetPageDataOutRowNumber]
(
@tn nvarchar(30),--表名称
@idn nvarchar(20),--表主键名称
@pi int = 1,--当前页数
@ps  int = 7,--每页大小
@wh nvarchar(255) = '',--wehre查询条件
@oby nvarchar(255) = '',--orderby 排序
@rc int output,--总行数(传出参数)
@pc int output--总页数(传出参数)
)
AS
DECLARE @sql NVARCHAR(225)='',@sqlCount NVARCHAR(225)=''
--1.计算总行数和总页数
SET @sqlCount = 'SELECT @rc=COUNT(['+@idn+']),@pc=CEILING((COUNT('+@idn+')+0.0)/'+ CAST(@ps AS VARCHAR)+') FROM ' + @tn
IF LEN(@wh)>1
    set @sqlCount=@sqlCount+' WHERE '+@wh
print @sqlCount
EXEC SP_EXECUTESQL @sqlCount,N'@rc INT OUTPUT,@pc INT OUTPUT',@rc OUTPUT,@pc OUTPUT
--2.分页
--2.1如果是第一页，则直接查询
IF @pi = 1
BEGIN
    SET @sql='SELECT TOP '+str(@ps) +' * FROM '+@tn
    IF LEN(@wh)>1
        set @sql=@sql+' WHERE '+@wh
    IF LEN(@oby)>1
            SET @sql=@sql+' order by ' +@oby
EXEC(@sql)
END
ELSE--2.2如果不是第一页，则拼接查询语句
BEGIN
    SET NOCOUNT ON
    SET @sql='SELECT * FROM (select row_number() over(order by '
    IF LEN(@oby)>1
        set @sql=@sql + @oby+') as rowNum,* from '+@tn
    else
        set @sql=@sql + @idn+') as rowNum,* from '+@tn
    IF LEN(@wh)>1
        set @sql=@sql+' where '+@wh
    set @sql=@sql+')as temp where rowNum>'+str(@ps * (@pi-1))+' and rowNum<='+str(@ps*@pi)
    print @sql
    EXEC(@sql)
    SET NOCOUNT OFF
END

 

--测试语句

declare @rc int,@pc int
exec [GetPageDataOutRowNumber] 'Ams_Area','ar_id',2,5,'',' ar_id desc',@rc output,@pc output
select @rc,@pc

 

--C#调用

 

  private void getdataByfenye(HttpContext context)
        {
            int pageSize = int.Parse(context.Request["rows"].ToString());
            int page = int.Parse(context.Request["page"].ToString());

            string strCon = @"Server=192.168.10.237;database=KM;uid=sa;pwd=Password01!;";
            SqlConnection con = new SqlConnection(strCon);
            SqlCommand com = new SqlCommand("GetPageDataOutRowNumber", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.Add(new SqlParameter("@tn", "T_KNOWLEDGE_EXPERT"));
            com.Parameters.Add(new SqlParameter("@idn", "OID"));
            com.Parameters.Add(new SqlParameter("@pi", page));
            com.Parameters.Add(new SqlParameter("@ps", pageSize));
            string strWhere = "";
            string strOby = "";
            string strExpert = context.Request["expert"] == null ? "" : context.Request["expert"].ToString();
            string strDeptno = context.Request["deptno"] == null ? "" : context.Request["deptno"].ToString();
            if (!string.IsNullOrEmpty(strExpert))
            {
                strWhere += " EXPERT = '" + strExpert + "' and ";
            }
            if (!string.IsNullOrEmpty(strDeptno))
            {
                strWhere += " DEPARTMENT like '%" + strDeptno + "%' and ";
            }
            if (!string.IsNullOrEmpty(strWhere))
            {
                strWhere = strWhere.Substring(0, strWhere.LastIndexOf("and"));
            }
            com.Parameters.Add(new SqlParameter("@wh", strWhere));
            com.Parameters.Add(new SqlParameter("@oby", strOby));

            SqlParameter param = new SqlParameter("@rc", SqlDbType.Int);
            param.Direction = ParameterDirection.Output;
            com.Parameters.Add(param);
            SqlParameter param1 = new SqlParameter("@pc", SqlDbType.Int);
            param1.Direction = ParameterDirection.Output;
            com.Parameters.Add(param1);
            com.CommandTimeout = 180;
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = com;

            con.Open();
            DataSet ds = new DataSet();
            sda.Fill(ds);
            con.Close();
            object o = com.Parameters["@rc"].Value;
            int total = (o == null || o == DBNull.Value) ? 0 : Convert.ToInt32(o);

            object b = com.Parameters["@pc"].Value;
            int pageCount = (b == null || o == DBNull.Value) ? 0 : Convert.ToInt32(b);


            List<KnowledgeExpert> lst = new List<KnowledgeExpert>();
            KnowledgeExpert ke = new KnowledgeExpert();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                ke = new KnowledgeExpert();
                ke.CREATED_BY = dr["CREATED_BY"] == null ? "" : dr["CREATED_BY"].ToString();
                ke.CREATED_TIME = dr["CREATED_TIME"] == null ? "" : dr["CREATED_TIME"].ToString();
                ke.DEPARTMENT = dr["DEPARTMENT"] == null ? "" : dr["DEPARTMENT"].ToString();
                ke.DESCRIPTION = dr["DESCRIPTION"] == null ? "" : dr["DESCRIPTION"].ToString();
                ke.EXPERT = dr["EXPERT"] == null ? "" : dr["EXPERT"].ToString();
                ke.OID = dr["OID"].ToString();
                lst.Add(ke);
            }
            JavaScriptSerializer jss = new JavaScriptSerializer();
            String str = jss.Serialize(lst);
            String str2 = "{"total":" + total + ","rows":" + str + "}";
            //Thread.Sleep(3000);
            context.Response.Write(str2);
        }