asp.net中如何调用sql存储过程实现分页

首先看下面的代码创建存储过程

1、创建存储过程，语句如下：

 create proc p_viewpage
 @tablename varchar(200), --表名
 @fieldlist varchar(2000), --显示列名，如果是全部字段则为*
 @primarykey varchar(100), --单一主键或唯一值键
 @where varchar(2000), --查询条件 不含'where'字符，如id>10 and len(userid)>9
 @order varchar(1000), --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc     
               --注意当@sorttype=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
 @sorttype int,   --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
 @recordercount int,  --记录总数 0:会返回总记录
 @pagesize int,   --每页输出的记录数
 @pageindex int,   --当前页数
 @totalcount int output,  --记返回总记录
 @totalpagecount int output  --返回总页数
as
 set nocount on

 if isnull(@totalcount,'') = '' set @totalcount = 0
 set @order = rtrim(ltrim(@order))
 set @primarykey = rtrim(ltrim(@primarykey))
 set @fieldlist = replace(rtrim(ltrim(@fieldlist)),' ','')

 while charindex(', ',@order) > 0 or charindex(' ,',@order) > 0
 begin
  set @order = replace(@order,', ',',')
  set @order = replace(@order,' ,',',') 
 end

 if isnull(@tablename,'') = '' or isnull(@fieldlist,'') = '' 
  or isnull(@primarykey,'') = ''
  or @sorttype < 1 or @sorttype >3
  or @recordercount < 0 or @pagesize < 0 or @pageindex < 0 
 begin 
  print('err_00')  
  return
 end 

 if @sorttype = 3
 begin
  if (upper(right(@order,4))!=' asc' and upper(right(@order,5))!=' desc')
  begin print('err_02') return end
 end

 declare @new_where1 varchar(1000)
 declare @new_where2 varchar(1000)
 declare @new_order1 varchar(1000)  
 declare @new_order2 varchar(1000)
 declare @new_order3 varchar(1000)
 declare @sql varchar(8000)
 declare @sqlcount nvarchar(4000)

 if isnull(@where,'') = ''
  begin
   set @new_where1 = ' '
   set @new_where2 = ' where '
  end
 else
  begin
   set @new_where1 = ' where ' + @where 
   set @new_where2 = ' where ' + @where + ' and '
  end

 if isnull(@order,'') = '' or @sorttype = 1 or @sorttype = 2 
  begin
   if @sorttype = 1 
   begin 
    set @new_order1 = ' order by ' + @primarykey + ' asc'
    set @new_order2 = ' order by ' + @primarykey + ' desc'
   end
   if @sorttype = 2 
   begin 
    set @new_order1 = ' order by ' + @primarykey + ' desc'
    set @new_order2 = ' order by ' + @primarykey + ' asc'
   end
  end
 else
  begin
   set @new_order1 = ' order by ' + @order
  end

 if @sorttype = 3 and charindex(','+@primarykey+' ',','+@order)>0
  begin
   set @new_order1 = ' order by ' + @order
   set @new_order2 = @order + ','  
   set @new_order2 = replace(replace(@new_order2,'asc,','{asc},'),'desc,','{desc},')  
   set @new_order2 = replace(replace(@new_order2,'{asc},','desc,'),'{desc},','asc,')
   set @new_order2 = ' order by ' + substring(@new_order2,1,len(@new_order2)-1)  
   if @fieldlist <> '*'
    begin  
     set @new_order3 = replace(replace(@order + ',','asc,',','),'desc,',',')     
     set @fieldlist = ',' + @fieldlist   
     while charindex(',',@new_order3)>0
     begin
      if charindex(substring(','+@new_order3,1,charindex(',',@new_order3)),','+@fieldlist+',')>0
      begin 
      set @fieldlist = 
       @fieldlist + ',' + substring(@new_order3,1,charindex(',',@new_order3))   
      end
      set @new_order3 = 
      substring(@new_order3,charindex(',',@new_order3)+1,len(@new_order3))
     end
     set @fieldlist = substring(@fieldlist,2,len(@fieldlist))   
    end  
  end

 set @sqlcount = 'select @totalcount=count(*),@totalpagecount=ceiling((count(*)+0.0)/'
     + cast(@pagesize as varchar)+') from ' + @tablename + @new_where1
 if @recordercount = 0
  begin
    exec sp_executesql @sqlcount,n'@totalcount int output,@totalpagecount int output',
         @totalcount output,@totalpagecount output
  end
 else
  begin
    select @totalcount = @recordercount  
  end

 if @pageindex > ceiling((@totalcount+0.0)/@pagesize)
  begin
   set @pageindex = ceiling((@totalcount+0.0)/@pagesize)
  end

 if @pageindex = 1 or @pageindex >= ceiling((@totalcount+0.0)/@pagesize)
  begin
   if @pageindex = 1 --返回第一页数据
    begin
     set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ' 
         + @tablename + @new_where1 + @new_order1
    end
   if @pageindex >= ceiling((@totalcount+0.0)/@pagesize) --返回最后一页数据
    begin
     set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from (' 
         + 'select top ' + str(abs(@pagesize*@pageindex-@totalcount-@pagesize)) 
         + ' ' + @fieldlist + ' from '
         + @tablename + @new_where1 + @new_order2 + ' ) as tmp '
         + @new_order1   
    end 
  end 
 else
  begin
   if @sorttype = 1 --仅主键正序排序
    begin
     if @pageindex <= ceiling((@totalcount+0.0)/@pagesize)/2 --正向检索
      begin
       set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ' 
           + @tablename + @new_where2 + @primarykey + ' > '
           + '(select max(' + @primarykey + ') from (select top '
           + str(@pagesize*(@pageindex-1)) + ' ' + @primarykey 
           + ' from ' + @tablename
           + @new_where1 + @new_order1 +' ) as tmp) '+ @new_order1
      end
     else --反向检索
      begin
       set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from (' 
           + 'select top ' + str(@pagesize) + ' ' 
           + @fieldlist + ' from '
           + @tablename + @new_where2 + @primarykey + ' < '
           + '(select min(' + @primarykey + ') from (select top '
           + str(@totalcount-@pagesize*@pageindex) + ' ' + @primarykey 
           + ' from ' + @tablename
           + @new_where1 + @new_order2 +' ) as tmp) '+ @new_order2 
           + ' ) as tmp ' + @new_order1
      end
    end
   if @sorttype = 2 --仅主键反序排序
    begin
     if @pageindex <= ceiling((@totalcount+0.0)/@pagesize)/2 --正向检索
      begin
       set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ' 
           + @tablename + @new_where2 + @primarykey + ' < '
           + '(select min(' + @primarykey + ') from (select top '
           + str(@pagesize*(@pageindex-1)) + ' ' + @primarykey 
           +' from '+ @tablename
           + @new_where1 + @new_order1 + ') as tmp) '+ @new_order1     
      end 
     else --反向检索
      begin
       set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from (' 
           + 'select top ' + str(@pagesize) + ' ' 
           + @fieldlist + ' from '
           + @tablename + @new_where2 + @primarykey + ' > '
           + '(select max(' + @primarykey + ') from (select top '
           + str(@totalcount-@pagesize*@pageindex) + ' ' + @primarykey 
           + ' from ' + @tablename
           + @new_where1 + @new_order2 +' ) as tmp) '+ @new_order2 
           + ' ) as tmp ' + @new_order1
      end 
    end    
   if @sorttype = 3 --多列排序，必须包含主键，且放置最后，否则不处理
    begin
     if charindex(',' + @primarykey + ' ',',' + @order) = 0 
     begin print('err_02') return end
     if @pageindex <= ceiling((@totalcount+0.0)/@pagesize)/2 --正向检索
      begin
       set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ( '
           + 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ( '
           + ' select top ' + str(@pagesize*@pageindex) + ' ' + @fieldlist
           + ' from ' + @tablename + @new_where1 + @new_order1 + ' ) as tmp '
           + @new_order2 + ' ) as tmp ' + @new_order1 
      end
     else --反向检索
      begin
       set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ( ' 
           + 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ( '
           + ' select top ' + str(@totalcount-@pagesize*@pageindex+@pagesize) + ' ' + @fieldlist
           + ' from ' + @tablename + @new_where1 + @new_order2 + ' ) as tmp '
           + @new_order1 + ' ) as tmp ' + @new_order1
      end
    end
  end
 print(@sql)
 exec(@sql)
go

2、sql server 中调用测试代码

--执行存储过程

declare @totalcount int,
    @totalpagecount int
exec p_viewpage 't_module','*','moduleid','','',1,0,10,1,@totalcount output,@totalpagecount output
select @totalcount,@totalpagecount;

asp.net 代码实现：

#region ===========通用分页存储过程===========
  public static dataset runprocedureds(string connectionstring, string storedprocname, idataparameter[] parameters, string tablename)
  {
 using (sqlconnection connection = new sqlconnection(connectionstring))
 {
   dataset dataset = new dataset();
   connection.open();
   sqldataadapter sqlda = new sqldataadapter();
   sqlda.selectcommand = buildquerycommand(connection, storedprocname, parameters);
   sqlda.fill(dataset, tablename);
   connection.close();
   return dataset;
 }
  }
  /// <summary>
  /// 通用分页存储过程
  /// </summary>
  /// <param name="connectionstring"></param>
  /// <param name="tblname"></param>
  /// <param name="strgetfields"></param>
  /// <param name="primarykey"></param>
  /// <param name="strwhere"></param>
  /// <param name="strorder"></param>
  /// <param name="sorttype"></param>
  /// <param name="recordcount"></param>
  /// <param name="pagesize"></param>
  /// <param name="pageindex"></param>
  /// <param name="totalcount"></param>
  /// <param name="totalpagecount"></param>
  /// <returns></returns>
  public static dataset pagelist(string connectionstring, string tblname, string strgetfields, string primarykey, string strwhere, string strorder, int sorttype, int recordcount,
 int pagesize, int pageindex,ref int totalcount,ref int totalpagecount)
  {
 sqlparameter[] parameters ={ new sqlparameter("@tablename ",sqldbtype.varchar,200),
  new sqlparameter("@fieldlist",sqldbtype.varchar,2000),
  new sqlparameter("@primarykey",sqldbtype.varchar,100),
  new sqlparameter("@where",sqldbtype.varchar,2000),
  new sqlparameter("@order",sqldbtype.varchar,1000),
  new sqlparameter("@sorttype",sqldbtype.int),
  new sqlparameter("@recordercount",sqldbtype.int),
  new sqlparameter("@pagesize",sqldbtype.int),
  new sqlparameter("@pageindex",sqldbtype.int),
  new sqlparameter("@totalcount",sqldbtype.int),
  new sqlparameter("@totalpagecount",sqldbtype.int)};

 parameters[0].value = tblname;
 parameters[1].value = strgetfields;
 parameters[2].value = primarykey;
 parameters[3].value = strwhere;
 parameters[4].value = strorder;
 parameters[5].value = sorttype;
 parameters[6].value = recordcount;
 parameters[7].value = pagesize;
 parameters[8].value = pageindex;
 parameters[9].value = totalcount;
 parameters[9].direction = parameterdirection.output;
 parameters[10].value = totalpagecount;
 parameters[10].direction = parameterdirection.output;

 dataset ds = runprocedureds(connectionstring, "p_viewpage", parameters, "pagelisttable");
 totalcount = int.parse(parameters[9].value.tostring());
 totalpagecount = int.parse(parameters[10].value.tostring());
 return ds;
  }
  #endregion
dataset ds = sqlhelper.pagelist(sqlhelper.localsqlserver, "t_user", "*", "userid", "", "", 1, 0, pagesize, 1, ref totalcount, ref totalpagecount);
this.rptdata.datasource = ds;
this.rptdata.databind();

以上内容就是本文介绍asp.net中如何调用sql存储过程实现分页的全部内容，希望对大家今后的学习有所帮助，当然方法不止本文所述，欢迎与大家分享好的方案。