存储过程分页

====================================================================================================================
第一步：存储过程

if object_id('calcpage','P') is not null
drop procedure calcpage
go
CREATE procedure calcpage
@prc int=20, --每页记录数
@tn nvarchar(4000), --表名
@my_where nvarchar(4000) --条件
AS
declare @mysql nvarchar(4000)

--行数和页数
set @mysql=N'select count(*) as rc,(count(*)/'+str(@prc)+N'+(case when count(*)%'+str(@prc)+N'>0 then 1 else 0 end)) as pc from '+@tn+N' where '+@my_where
execute (@mysql)

go

--分页 存储过程
if object_id('paging','P') is not null
drop procedure paging
go
CREATE procedure paging
@prc int=20, --每页记录数
@pi int=1, --页索引
@sn nvarchar(4000), --选择列名s,
@tn nvarchar(4000), --表名
@on nvarchar(4000), --排序列名
@my_where nvarchar(4000) --条件
AS
declare @mysql nvarchar(4000)

--中间页
set @mysql=N'
select * from
(select top '+str(@prc)+N' * from
(select top '+str(@prc*@pi)+N' '+@sn+N' from '+@tn+N' where '+@my_where+N'
order by '+@on+N' asc) a order by '+@on+N' desc) b
order by '+@on+N' asc'
execute (@mysql)
go

===========================================================================================================================
第二步：在DAL下建Aux.cs文件

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace HxDAL
{
public class HxDALAux
{
/// 分页存储过程整体调用方法
///private void button1_Click(object sender, EventArgs e)
///{
/// int prc=20;int pi = 4; // 行/页; 页索引

/// HxDALAux aux = new HxDALAux();
/// int rc=0;int pc=0;
/// if(aux.GetCalcpageParam(prc, "products", "1=1", ref rc, ref pc))
/// {
/// if(rc>0 && pc>0)
/// {
/// System.Collections.ArrayList result=new System.Collections.ArrayList(prc);
/// if (aux.GetPagingParam(prc, pi, "productId,productName", "products", "productId", "1=1", ref result))
/// {
/// this.dataGridView1.DataSource = aux.GetPagingParamAux(result);
/// }
/// }
/// }
///}

private static string _cs = @"data source=.;initial catalog=db_ChnCity;integrated security=True";

public static string CS
{
get { return HxDALAux._cs; }
set { HxDALAux._cs = value; }
}


protected static System.Data.SqlClient.SqlConnection _cn = null;

/// <summary>
/// 返回算页参数,调用方法:
/// int prc=20;int pi = 4;
/// HxDALAux aux = new HxDALAux();
/// int rc=0;int pc=0;
/// if(aux.GetCalcpageParam(prc, "products", "1=1", ref rc, re