101 分求分页存储过程及C#调用示例

Question

要求,支持多表查询,刚刚在CSDN里看了一下,有很多都不支持
还有ASP.NET(C#调用示例)谢啦!在线等! 问题解决,马上结贴!

Answer 1

邹老大这个
通用分页存储过程--邹键 /*--实现分页的通用存储过程 显示指定表、视图、查询结果的第X页
 对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
 如果视图或查询结果中有主键,不推荐此方法
 如果使用查询语句,而且查询语句使用了order by,则查询语句必须包含top 语句--csdn.net邹建  --*//*--调用示例
 exec p_show '地区资料' exec p_show 'select top 100 percent * from 地区资料 order by 地区名称',5,3,'地区编号,地区名称,助记码'
--*/
CREATE Proc p_show
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=20,   --每页的大小(行数)
@PageCurrent int=1,   --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果不需要标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
set nocount on
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
 ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
 ,@Obj_ID int    --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
 ,@strjoin nvarchar(4000) --连接字段
 ,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@QueryStr)
 ,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
 ,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
 ,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end--如果显示第一页，可以直接用top来完成
if @PageCurrent=1 
begin
 select @Id1=cast(@PageSize as varchar(20))
 exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
 return
end--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
 select @Id1=cast(@PageSize as varchar(20))
  ,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20)) select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
 if @@rowcount=0   --如果表中无标识列,则检查表中是否有主键
 begin
  if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
   goto lbusetemp  --如果表中无主键,则用临时表处理  select @FdName=name from syscolumns where id=@Obj_ID and colid in(
   select colid from sysindexkeys where @Obj_ID=id and indid in(
    select indid from sysindexes where @Obj_ID=id and name in(
     select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
   )))
  if @@rowcount>1  --检查表中的主键是否为复合主键
  begin
   select @strfd='',@strjoin='',@strwhere=''
   select @strfd=@strfd+',['+name+']'
    ,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
    ,@strwhere=@strwhere+' and b.['+name+'] is null'
    from syscolumns where id=@Obj_ID and colid in(
    select colid from sysindexkeys where @Obj_ID=id and indid in(
     select indid from sysindexes where @Obj_ID=id and name in(
      select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
    )))
   select @strfd=substring(@strfd,2,2000)
    ,@strjoin=substring(@strjoin,5,4000)
    ,@strwhere=substring(@strwhere,5,4000)
   goto lbusepk
  end
 end
end
else
 goto lbusetemp/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity: 
 exec('select top '+@Id1+@FdShow+' from '+@QueryStr
  +' where '+@FdName+' not in(select top '
  +@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
  +')'+@FdOrder
  )
 return/*--表中有复合主键的处理方法--*/
lbusepk:  
 exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
  (select top 100 percent * from '+@QueryStr+@FdOrder+') a
  left join (select top '+@Id2+' '+@strfd+' 
  from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
  where '+@strwhere+') a'
  )
 return/*--用临时表处理的方法--*/
lbusetemp:  
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
 ,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
 ,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
  into #tb from'+@QueryStr+@FdOrder+'
 select '+@FdShow+' from #tb where '+@FdName+' between '
 +@Id1+' and '+@Id2
 )
GO

Answer 2

using System;
using System.Collections.Generic;
using System.Text;
namespace Ivony.Data.Paginations
{  /// <summary>
  /// 这是邹建的通用分页存储过程的访问类
  /// </summary>
  /// <res>
  /// 所对应的存储过程代码：
  /// <code>
  /// <![CDATA[
  /// CREATE Proc [dbo].[Pagination_zj]
  /// @QueryStr nvarchar(4000), --表名、视图名、查询语句
  /// @PageSize int=10,   --每页的大小(行数)
  /// @PageCurrent int=1,   --要显示的页
  /// @FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
  /// @FdOrder nvarchar (1000)='' --排序字段列表
  /// as
  /// declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
  ///  ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
  ///  ,@Obj_ID int    --对象ID
  /// --表中有复合主键的处理
  /// declare @strfd nvarchar(2000) --复合主键列表
  ///  ,@strjoin nvarchar(4000) --连接字段
  ///  ,@strwhere nvarchar(2000) --查询条件
  /// 
  /// 
  /// select @Obj_ID=object_id(@QueryStr)
  ///  ,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
  ///  ,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
  ///  ,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end
  /// 
  /// --如果显示第一页，可以直接用top来完成
  /// if @PageCurrent=1 
  /// begin
  ///  select @Id1=cast(@PageSize as varchar(20))
  ///  exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
  ///  return
  /// end
  /// 
  /// --如果是表,则检查表中是否有标识更或主键
  /// if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
  /// begin
  ///  select @Id1=cast(@PageSize as varchar(20))
  ///   ,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
  /// 
  ///  select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
  ///  if @@rowcount=0   --如果表中无标识列,则检查表中是否有主键
  ///  begin
  ///   if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
  ///    goto lbusetemp  --如果表中无主键,则用临时表处理
  /// 
  ///   select @FdName=name from syscolumns where id=@Obj_ID and colid in(
  ///    select colid from sysindexkeys where @Obj_ID=id and indid in(
  ///     select indid from sysindexes where @Obj_ID=id and name in(
  ///      select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
  ///    )))
  ///   if @@rowcount>1  --检查表中的主键是否为复合主键
  ///   begin
  ///    select @strfd='',@strjoin='',@strwhere=''
  ///    select @strfd=@strfd+',['+name+']'
  ///     ,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
  ///     ,@strwhere=@strwhere+' and b.['+name+'] is null'
  ///     from syscolumns where id=@Obj_ID and colid in(
  ///     select colid from sysindexkeys where @Obj_ID=id and indid in(
  ///      select indid from sysindexes where @Obj_ID=id and name in(
  ///       select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
  ///     )))
  ///    select @strfd=substring(@strfd,2,2000)
  ///     ,@strjoin=substring(@strjoin,5,4000)
  ///     ,@strwhere=substring(@strwhere,5,4000)
  ///    goto lbusepk
  ///   end
  ///  end
  ///  else
  ///   goto lbuseidentity
  /// end
  /// else
  ///  goto lbusetemp
  /// 
  /// /*--使用标识列或主键为单一字段的处理方法--*/
  /// lbuseidentity: 
  ///  exec('select top '+@Id1+@FdShow+' from '+@QueryStr
  ///   +' where '+@FdName+' not in(select top '
  ///   +@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
  ///   +')'+@FdOrder
  ///   )
  ///  return
  /// 
  /// /*--表中有复合主键的处理方法--*/
  /// lbusepk:  
  ///  exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
  ///   (select top 100 percent * from '+@QueryStr+@FdOrder+') a
  ///   left join (select top '+@Id2+' '+@strfd+' 
  ///   from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
  ///   where '+@strwhere+') a'
  ///   )
  ///  return
  /// 
  /// /*--用临时表处理的方法--*/
  /// lbusetemp:  
  /// select @FdName='[ID_'+cast(newid() as varchar(40))+']'
  ///  ,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
  ///  ,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
  /// 
  /// exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
  ///   into #tb from'+@QueryStr+@FdOrder+'
  ///  select '+@FdShow+' from #tb where '+@FdName+' between '
  ///  +@Id1+' and '+@Id2
  ///  )
  /// ]]>
  /// </code>
  /// </res>
  [Serializable]
  [PaginationInformation( StoredProcederName = "Pagination_zj", Author = "邹建" )]
  public class PaginationZJ : IPagination
  {    /*
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10,   --每页的大小(行数)
@PageCurrent int=1,   --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
    */    private string _queryString;
    private int _pagesize;
    private string _filedsString;
    private string _orderString;    private SqlDbUtility _dbUtility;    public PaginationZJ( SqlDbUtility dbUtility, string queryString, int pageSize, string fieldString, string orderString )
    {
      _dbUtility = dbUtility;
      _queryString = queryString;
      _pagesize = pageSize;
      _filedsString = fieldString;
      _orderString = orderString;
    }
    public int GetCount()
    {
      if ( _tablename == null )
        return (int) _dbUtility.ExecuteScalar( "SELECT COUNT(*) FROM ( {0:} )", _queryString );
      else
        return (int) _dbUtility.ExecuteScalar( "SELECT COUNT(*) FROM " + _tablename );
    }    public System.Data.DataTable GetPage( int pageindex )
    {
      SqlStoredProcedure sp = _dbUtility.CreateStoredProcedure( "Pagination_zj" );
      sp["QueryStr"] = _queryString;
      sp["PageSize"] = _pagesize;
      sp["PageCurrent"] = pageindex;
      sp["FdShow"] = _filedsString;
      sp["FdOrder"] = _orderString;      return sp.ExecuteData();
    }    public int PageSize
    {
      get { return _pagesize; }
    }
  }  /// <summary>
  /// 分页器接口，所有的分页器类都必须实现的接口
  /// </summary>
  public interface IPagination
  {
    /// <summary>
    /// 获取记录总数
    /// </summary>
    /// <returns>记录总数</returns>
    int GetCount();
    
    /// <summary>
    /// 获取指定页数据
    /// </summary>
    /// <param name="pageindex">页序号</param>
    /// <returns>页数据</returns>
    System.Data.DataTable GetPage( int pageindex );    /// <summary>
    /// 获取设置的页大小
    /// </summary>
    int PageSize { get; }
  }
}SqlDbUtility、SqlStoredProcuder的代码参阅：
http://community.csdn.net/Expert/topic/4583/4583043.xml?temp=.6516382

Answer 3

public int GetCount()
    {
      if ( _tablename == null )
        return (int) _dbUtility.ExecuteScalar( "SELECT COUNT(*) FROM ( {0:} )", _queryString );
      else
        return (int) _dbUtility.ExecuteScalar( "SELECT COUNT(*) FROM " + _tablename );
    }
修改为    public int GetCount()
    {
      return (int) _dbUtility.ExecuteScalar( "SELECT COUNT(*) FROM ( {0:} )", _queryString );
    }
分页存储过程当然都是封装好的，实在不知道怎么给你代码好……

Answer 4

--/*-----存储过程 分页处理 孙伟 2005-03-28创建 -------*/
--/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/
--/*-----存储过程 分页处理  2005-04-21修改 添加Distinct查询功能-------*/
--/*-----存储过程 分页处理  2005-05-18修改 多字段排序规则问题-------*/
--/*-----存储过程 分页处理  2005-06-15修改 多字段排序修改-------*/
ALTER PROCEDURE dbo.proc_ListPage
(
@tblName     nvarchar(200),        ----要显示的表或多个表的连接
@fldName     nvarchar(500) = '*',    ----要显示的字段列表
@pageSize    int = 1,        ----每页显示的记录个数
@page        int = 10,        ----要显示那一页的记录
@pageCount    int = 1 output,            ----查询结果分页后的总页数
@Counts    int = 1 output,                ----查询到的记录数
@fldSort    nvarchar(200) = null,    ----排序字段列表或条件
@Sort        bit = 0,        ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
@strCondition    nvarchar(1000) = null,    ----查询条件,不需where
@ID        nvarchar(150),        ----主表的主键
@Dist                 bit = 0           ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000)        ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000)        ----存放取得查询结果总数的查询语句
Declare @strID     nvarchar(1000)        ----存放取得查询开头或结尾ID的查询语句Declare @strSortType nvarchar(10)    ----数据排序规则A
Declare @strFSortType nvarchar(10)    ----数据排序规则BDeclare @SqlSelect nvarchar(50)         ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50)          ----对含有DISTINCT的总数查询进行SQL构造
if @Dist  = 0
begin
    set @SqlSelect = 'select '
    set @SqlCounts = 'Count(*)'
end
else
begin
    set @SqlSelect = 'select distinct '
    set @SqlCounts = 'Count(DISTINCT '+@ID+')'
end
if @Sort=0
begin
    set @strFSortType=' ASC '
    set @strSortType=' DESC '
end
else
begin
    set @strFSortType=' DESC '
    set @strSortType=' ASC '
end--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null or @strCondition=''     --没有设置显示条件
begin
    set @sqlTmp =  @fldName + ' From ' + @tblName
    set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
    set @strID = ' From ' + @tblName
end
else
begin
    set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
    set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
    set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
end----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
    set @tmpCounts = 1
else
    set @tmpCounts = @Counts    --取得分页总数
    set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize    /**//**当前页大于总页数 取最后一页**/
    if @page>@pageCount
        set @page=@pageCount    --/*-----数据分页2分处理-------*/
    declare @pageIndex int --总数/页大小
    declare @lastcount int --总数%页大小     set @pageIndex = @tmpCounts/@pageSize
    set @lastcount = @tmpCounts%@pageSize
    if @lastcount > 0
        set @pageIndex = @pageIndex + 1
    else
        set @lastcount = @pagesize    --//***显示分页
    if @strCondition is null or @strCondition=''     --没有设置显示条件
    begin
        if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理
            begin 
                set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                        +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
                        +' order by '+ @fldSort +' '+ @strFSortType+')'
                        +' order by '+ @fldSort +' '+ @strFSortType 
            end
        else
            begin
            set @page = @pageIndex-@page+1 --后半部分数据处理
                if @page <= 1 --最后一页数据显示
                    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                        +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
                else                
                    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                        +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
                        +' order by '+ @fldSort +' '+ @strSortType+')'                        +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
            end
    end    else --有查询条件
    begin
        if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理
        begin 
                set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName +' from  '+@tblName
                    +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
                    +' Where (1>0) ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType+')'
                    +' ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType                 
        end
        else
        begin 
            set @page = @pageIndex-@page+1 --后半部分数据处理
            if @page <= 1 --最后一页数据显示
                    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                        +' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
            else
                    set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                        +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
                        +' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+')'
                        + @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
        end    
    end------返回查询结果-----
exec sp_executesql @strTmp
--print @strTmp
SET NOCOUNT OFF这个存储过程我平时用，感觉还可以！

Answer 5

分页     为什么执行存储过程  也很慢  要 22秒?
--/*-----存储过程 分页处理 孙伟 2005-03-28创建 -------*/
--/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/
--/*-----存储过程 分页处理  2005-04-21修改 添加Distinct查询功能-------*/
--/*-----存储过程 分页处理  2005-05-18修改 多字段排序规则问题-------*/
--/*-----存储过程 分页处理  2005-06-15修改 多字段排序修改-------*/
ALTER PROCEDURE dbo.proc_ListPage
(
@tblName     nvarchar(200),        ----要显示的表或多个表的连接
@fldName     nvarchar(500) = '*',    ----要显示的字段列表
@pageSize    int = 1,        ----每页显示的记录个数
@page        int = 10,        ----要显示那一页的记录
@pageCount    int = 1 output,            ----查询结果分页后的总页数
@Counts    int = 1 output,                ----查询到的记录数
@fldSort    nvarchar(200) = null,    ----排序字段列表或条件
@Sort        bit = 0,        ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
@strCondition    nvarchar(1000) = null,    ----查询条件,不需where
@ID        nvarchar(150),        ----主表的主键
@Dist                 bit = 0           ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
)