MSSQL根据表名进行动态分页的sql存储过程:
该存储过程中操作的表需要设置主键,否则分页出错....
--动态按表名分页--
alter proc ShowPageByTable
@tableName varchar(40),
@pageIndex int=1 ,
@pageSize int=10,
@pageCount int =0 output
as
begin
--声明起始和结束的行号
declare @start int, @end int,@pkey varchar(20)
--创建临时表存储传入的主键
create table #temp
(
tab_qu varchar(20),owenr varchar(20),tablename varchar(20),
cname varchar(20),keys int,pname varchar(20)
)
insert #temp
exec sp_pkeys @tablename --获取该表主键的的系统存储过程
select @pkey=cname from #temp --设置主键
--设置起始和结束坐标
set @start=(@pageIndex-1 )*@pageSize +1
set @end =@pageIndex * @pageSize
--动态执行SQL语句,查询所需数据(如果该查询语句有需要其他约束,则需要传入其他约束条件)
exec('
select ROW_NUMBER() over(order by '+@pkey+') as RowID,* into #OrderTemp
from ' + @tableName +'
select * from #OrderTemp
where RowID between ' +@start + ' and '+ @end)
--执行一次全表查询,判断分页的总页数
exec('select * into #ss from '+@tableName)
set @pageCount= @@ROWCOUNT/@pageSize
if(@@ROWCOUNT%@pageSize!=0)
set @pageCount=@pageCount+1
end
C#调用代码:
/// <summary>
/// 根据表名分页,不获取分页的总页数
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">每页显示行数</param>
/// <returns>该页的数据集</returns>
public static DataSet GetDataByNamePage(string tablename, int pageIndex,int pageSize)
{
DataSet ds = new DataSet();
using (sqlconn)
{
int count=0;
ds= GetDataByNamePage(tablename, pageIndex, pageSize, out count);
}
return ds;
}
/// <summary>
/// 根据表名分页,包含输出参数总页数
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">每页显示行数</param>
/// <param name="pagetCount">输出参数:分页的总页数</param>
/// <returns>该页的数据集</returns>
public static DataSet GetDataByNamePage(string tablename, int pageIndex, int pageSize,out int pagetCount)
{
DataSet ds = new DataSet();
using (sqlconn)
{
sqlconn = new SqlConnection(connstr);
sqlconn.Open();
SqlDataAdapter sda = new SqlDataAdapter("ShowPageByTable", sqlconn);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;
sda.SelectCommand.Parameters.AddWithValue("@tableName", tablename);
sda.SelectCommand.Parameters.AddWithValue("@pageIndex", pageIndex);
sda.SelectCommand.Parameters.AddWithValue("@pageSize", pageSize);
sda.SelectCommand.Parameters.Add("@pageCount", SqlDbType.Int);
sda.SelectCommand.Parameters["@pageCount"].Direction = ParameterDirection.Output;
if (ds.Tables[tablename] != null)
ds.Tables[tablename].Clear();
sda.Fill(ds, tablename);
pagetCount = (int)sda.SelectCommand.Parameters["@pageCount"].Value;
}
return ds;
}