在SQL里建立以下存储过程
然后在C#里调用以上建立好的存储过程,实现数据真分页功能
注: 以上存储过程只可用SQL2005,SQL2000会出错
Create PROCEDURE [dbo].[proc_ShowPage]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列,默认*
@strOrder varchar(255)='', -- 排序的字段名,必填
@strOrderType varchar(10)='ASC', -- 排序的方式,默认ASC
@PageSize int = 10, -- 页尺寸,默认10
@PageIndex int = 1, -- 页码,默认1
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000)
if @strWhere !=''
set @strWhere=' where '+@strWhere
set @strSQL=
'SELECT * FROM ('+
'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS pos,'+@strGetFields+' '+
'FROM '+@tblName+' '+@strWhere+
') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
exec (@strSQL)
然后在C#里调用以上建立好的存储过程,实现数据真分页功能
public DataTable Select(int pagesize, int pageindex, string cond)
{
DataTable dt = new DataTable();
string cmdText = "proc_ShowPage";
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@tblName","表名,如多表查询此处需要用()括起来" as object),
new SqlParameter("@strGetFields","需要选取表的字段名" as object),
new SqlParameter("@strOrder","排序字段名" as object),
new SqlParameter("@strOrderType","desc" as object),
new SqlParameter("@PageSize",pagesize as object),
new SqlParameter("@PageIndex",pageindex as object),
new SqlParameter("@strWhere",cond as object)
};
dt = sqlhelper.ExecuteQuery(cmdText, paras, CommandType.StoredProcedure);
return dt;
}
注: 以上存储过程只可用SQL2005,SQL2000会出错