以前用.net写分页存储过程时老是一张表就要写一个procedure.很忙烦.后来有空就整合了一下,做了一个通用的sql分页存储,支持多表联合查询分页.写的不好的地方,希望大鸟们多多指导.使的查询速度更快.
 代码如下 复制代码
sqlpager存储过程
alter proc [dbo].[sqlpager]
(
@tblname varchar(255), -- 表名(注意：可以多表链接)
@strgetfields varchar(1000) = '*', -- 需要返回的列
@orderfldname varchar(255)='', -- 排序的字段名
@pagesize int = 10, -- 页尺寸
@pageindex int = 1, -- 页码
@docount int = 1 output, --查询到的记录数
@ordertype bit = 0, -- 设置排序类型, 非 0 值则降序
@strwhere varchar(500) = '' -- 查询条件 (注意: 不要加 where)
)
as
declare @strsql nvarchar(4000) -- 主语句
declare @strtmp varchar(110) -- 临时变量
declare @strorder varchar(300) -- 排序类型
if @strwhere != ''
set @strsql = ' @docount=count(*) from ' + @tblname + ' where '+@strwhere
else
set @strsql = 'select @docount=count(*) from ' + @tblname
exec sp_executesql @strsql,n'@docount int out',@docount out
--以上代码的意思是如果@docount传递过来的不是0，就执行总数统计。以下的所有代码都是@docount为0的情况
set @strsql='';
if @ordertype != 0
begin
set @strtmp = 'set @strorder = ' order by [' + @orderfldname +'] desc'--如果@ordertype不是0，就执行降序，这句很重要！
end
else
begin
set @strtmp = '>(select max'
set @strorder = ' order by [' + @orderfldname +'] asc'
end
if @pageindex = 1
begin
if @strwhere != ''
set @strsql = 'select top ' + str(@pagesize) +' '+@strgetfields+ ' from ' + @tblname + ' where ' + @strwhere + ' ' + @strorder
else
set @strsql = 'select top ' + str(@pagesize) +' '+@strgetfields+ ' from '+ @tblname + ' '+ @strorder--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin--以下代码赋予了@strsql以真正执行的sql代码
set @strsql = 'select top ' + str(@pagesize) +' '+@strgetfields+ ' from '
+ @tblname + ' where [' + @orderfldname + ']' + @strtmp + '(['+ @orderfldname + ']) from (select top ' + str((@pageindex-1)*@pagesize) + ' ['+ @orderfldname + '] from ' + @tblname + ' ' + @strorder + ') as tbltmp)'+ @strorder
if @strwhere != ''
set @strsql = 'select top ' + str(@pagesize) +' '+@strgetfields+ ' from '
+ @tblname + ' where [' + @orderfldname + ']' + @strtmp + '(['
+ @orderfldname + ']) from (select top ' + str((@pageindex-1)*@pagesize) + ' ['
+ @orderfldname + '] from ' + @tblname + ' where ' + @strwhere + ' '
+ @strorder + ') as tbltmp) and ' + @strwhere + ' ' + @strorder
end
exec (@strsql)
sqlhelper类中写执行存储过程 这是我的操作类.方法有点老,还是三层架构.(mvc也应该差不多.我还木有试试.)
        ///
        /// （存储过程） 用法例如下：
        ///
        /// 表名(注意：可以多表链接)
        /// 需要返回的列
        /// 主键
        /// 排序的字段名
        /// 页尺寸
        /// 页码
        /// 查询到的记录数
        /// 设置排序类型, desc、asc
        /// 查询条件 (注意: 不要加 where)
        ///
        public static datatable dbpager(
            string tblname,
            string strgetfields,
            string pkeyfldname,
            string orderfldname,
            int pagesize,
            int pageindex,
            out int docount,
            string ordertype,
            string strwhere)
        {
            sqlconnection con = new sqlconnection(connstring.value);
            sqldataadapter da = new sqldataadapter(dbpager, con);
            da.selectcommand.commandtype = commandtype.storedprocedure;
            da.selectcommand.parameters.add(new sqlparameter(@tblname, tblname));
            da.selectcommand.parameters.add(new sqlparameter(@strgetfields, strgetfields));
            da.selectcommand.parameters.add(new sqlparameter(@pkeyfldname, pkeyfldname));
            da.selectcommand.parameters.add(new sqlparameter(@orderfldname, orderfldname));
            da.selectcommand.parameters.add(new sqlparameter(@pagesize, pagesize));
            da.selectcommand.parameters.add(new sqlparameter(@pageindex, pageindex));
            da.selectcommand.parameters.add(@docount, sqldbtype.int);
            da.selectcommand.parameters[@docount].direction = parameterdirection.output;
            da.selectcommand.parameters.add(new sqlparameter(@ordertype, ordertype));
            da.selectcommand.parameters.add(new sqlparameter(@strwhere, strwhere));
            dataset ds = new dataset();
            da.fill(ds);
            docount = convert.toint32(da.selectcommand.parameters[@docount].value);
            return ds.tables[0];
        }