C# + SQL 存储过程分页

一、把存储过程导入大数据库中

 

CREATE PROCEDURE dbo.sp_ListPage
(
@tblName     nvarchar( 200 ),        ----要显示的表或多个表的连接
@fldName     nvarchar( 500 ) =  ' * ' ,    ----要显示的字段列表
@pageSize    int =  10 ,        ----每页显示的记录个数
@page        int =  1 ,        ----要显示那一页的记录
@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 )    ----数据排序规则B
Declare @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 
                if @page= 1
                    set @strTmp=@SqlSelect+ '  top  ' + CAST(@pageSize as VARCHAR( 4 ))+ '   ' + @fldName+ '  from  ' +@tblName                        
                        + '  order by  ' + @fldSort + '   ' + @strFSortType
                   
                else if @Sort= 1
                begin                    
                    set @strTmp=@SqlSelect+ '  top  ' + CAST(@pageSize as VARCHAR( 4 ))+ '   ' + @fldName+ '  from  ' +@tblName
                        + '  where  ' +@ID+ '  <(select min( ' + @ID + ' ) from ( ' + @SqlSelect+ '  top  ' + CAST(@pageSize*(@page- 1 ) as Varchar( 20 )) + '   ' + @ID + '  from  ' +@tblName
                        + '  order by  ' + @fldSort + '   ' + @strFSortType+ ' ) AS TBMinID) '
                        + '  order by  ' + @fldSort + '   ' + @strFSortType
                end

                else
                begin
                    set @strTmp=@SqlSelect+ '  top  ' + CAST(@pageSize as VARCHAR( 4 ))+ '   ' + @fldName+ '  from  ' +@tblName
                        + '  where  ' +@ID+ '  >(select max( ' + @ID + ' ) from ( ' + @SqlSelect+ '  top  ' + CAST(@pageSize*(@page- 1 ) as Varchar( 20 )) + '   ' + @ID + '  from  ' +@tblName
                        + '  order by  ' + @fldSort + '   ' + @strFSortType+ ' ) AS TBMinID) '
                        + '  order by  ' + @fldSort + '   ' + @strFSortType
                end

            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 if @Sort= 1
                    set @strTmp=@SqlSelect+ '  * from ( ' +@SqlSelect+ '  top  ' + CAST(@pageSize as VARCHAR( 4 ))+ '   ' + @fldName+ '  from  ' +@tblName
                        + '  where  ' +@ID+ '  >(select max( ' + @ID + ' ) from( ' + @SqlSelect+ '  top  ' + CAST(@pageSize*(@page- 2 )+@lastcount as Varchar( 20 )) + '   ' + @ID + '  from  ' +@tblName
                        + '  order by  ' + @fldSort + '   ' + @strSortType+ ' ) AS TBMaxID) '
                        + '  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+ '  <(select min( ' + @ID + ' ) from( ' + @SqlSelect+ '  top  ' + CAST(@pageSize*(@page- 2 )+@lastcount as Varchar( 20 )) + '   ' + @ID + '  from  ' +@tblName
                        + '  order by  ' + @fldSort + '   ' + @strSortType+ ' ) AS TBMaxID) '
                        + '  order by  ' + @fldSort + '   ' + @strSortType+ ' ) AS TempTB ' + '  order by  ' + @fldSort + '   ' + @strFSortType 
                
            end
    end
    else --有查询条件
    begin
        if @pageIndex< 2   or  @page<=@pageIndex /  2  + @pageIndex %  2    --前半部分数据处理
        begin
                if @page= 1
                    set @strTmp=@SqlSelect+ '  top  ' + CAST(@pageSize as VARCHAR( 4 ))+ '   ' + @fldName+ '  from  ' +@tblName                        
                        + '  where 1=1  '  + @strCondition +  '  order by  ' + @fldSort + '   ' + @strFSortType
                else  if @Sort= 1
                begin                    
                    set @strTmp=@SqlSelect+ '  top  ' + CAST(@pageSize as VARCHAR( 4 ))+ '   ' + @fldName+ '  from  ' +@tblName
                        + '  where  ' +@ID+ ' <(select min( ' + @ID + ' ) from ( ' + @SqlSelect+ '  top  ' + CAST(@pageSize*(@page- 1 ) as Varchar( 20 )) + '   ' + @ID + '  from  ' +@tblName
                        + '  where (1=1)  '  + @strCondition + '  order by  ' + @fldSort + '   ' + @strFSortType+ ' ) AS TBMinID) '
                        + '   ' + @strCondition + '  order by  ' + @fldSort + '   ' + @strFSortType
                end
                else
                begin                    
                    set @strTmp=@SqlSelect+ '  top  ' + CAST(@pageSize as VARCHAR( 4 ))+ '   ' + @fldName+ '  from  ' +@tblName
                        + '  where  ' +@ID+ ' >(select max( ' + @ID + ' ) from ( ' + @SqlSelect+ '  top  ' + CAST(@pageSize*(@page- 1 ) as Varchar( 20 )) + '   ' + @ID + '  from  ' +@tblName
                        + '  where (1=1)  '  + @strCondition + '  order by  ' + @fldSort + '   ' + @strFSortType+ ' ) AS TBMinID) '
                        + '   ' + @strCondition + '  order by  ' + @fldSort + '   ' + @strFSortType
                end 
        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=1)  ' + @strCondition + '  order by  ' + @fldSort + '   ' + @strSortType+ ' ) AS TempTB ' + '  order by  ' + @fldSort + '   ' + @strFSortType                     
            else if @Sort= 1
                    set @strTmp=@SqlSelect+ '  * from ( ' +@SqlSelect+ '  top  ' + CAST(@pageSize as VARCHAR( 4 ))+ '   ' + @fldName+ '  from  ' +@tblName
                        + '  where  ' +@ID+ '  >(select max( ' + @ID + ' ) from( ' + @SqlSelect+ '  top  ' + CAST(@pageSize*(@page- 2 )+@lastcount as Varchar( 20 )) + '   ' + @ID + '  from  ' +@tblName
                        + '  where (1=1)  ' + @strCondition + '  order by  ' + @fldSort + '   ' + @strSortType+ ' ) AS TBMaxID) '
                        + '   ' + @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+ '  <(select min( ' + @ID + ' ) from( ' + @SqlSelect+ '  top  ' + CAST(@pageSize*(@page- 2 )+@lastcount as Varchar( 20 )) + '   ' + @ID + '  from  ' +@tblName
                        + '  where (1=1)  ' + @strCondition + '  order by  ' + @fldSort + '   ' + @strSortType+ ' ) AS TBMaxID) '
                        + '   ' + @strCondition+ '  order by  ' + @fldSort + '   ' + @strSortType+ ' ) AS TempTB ' + '  order by  ' + @fldSort + '   ' + @strFSortType                

        end    
    end
------返回查询结果-----
exec sp_executesql @strTmp
--print @strTmp
SET NOCOUNT OFF
GO

 

 

 

 

 

二、写一个读取存储过程的类，取名为 publics.cs

 

using  System;
using  System.Data;
using  System.Configuration;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;
using  System.Data.SqlClient;
using  System.Text.RegularExpressions;
using  System.Net;
///   <summary>
///  publics 的摘要说明
///   </summary>
public   class  publics
{
     static   string  connectionString  =  ConfigurationManager.ConnectionStrings[ " deepseafish " ].ConnectionString;
    SqlConnection conn  =   new  SqlConnection(connectionString);
  public  publics()
 {
   //
   //  TODO: 在此处添加构造函数逻辑
   //
 }
     ///   <summary>
     ///  存储过程分页
     ///   </summary>
     ///   <param name="tblName"> 表名 </param>
     ///   <param name="fldName"> 字段名 </param>
     ///   <param name="pageSize"> 显示记录数 </param>
     ///   <param name="page"> 当前页 </param>
     ///   <param name="fldSort"> 排序字段 </param>
     ///   <param name="Sort"> 升序，降序 </param>
     ///   <param name="strCondition"> 条件 </param>
     ///   <param name="id"> 关键字 </param>
     ///   <param name="PageCount"></param>
     ///   <param name="Counts"></param>
     ///   <returns> 返回dataset </returns>
     public  DataSet Page_list2( string  tblName,  string  fldName,  int  pageSize,  int  page,  string  fldSort,  int  Sort,  string  strCondition,  string  id,  ref   int  PageCount,  ref   int  Counts)
    {
        DataSet ds  =   null ;
        SqlParameter[] parms  = {
                MakeInParam( " @tblName " , SqlDbType.VarChar,  200 ,tblName),
                MakeInParam( " @fldName " , SqlDbType.VarChar,  500 ,fldName),
                MakeInParam( " @pageSize " , SqlDbType.Int,  4 ,pageSize),
                MakeInParam( " @page " , SqlDbType.Int,  4 ,page),
                MakeInParam( " @fldSort " , SqlDbType.VarChar,  200 ,fldSort),
                MakeInParam( " @Sort " , SqlDbType.Bit, 1 ,Sort),
                MakeInParam( " @strCondition " , SqlDbType.VarChar,  1000 ,strCondition),
                MakeInParam( " @id " , SqlDbType.VarChar,  150 ,id),
                MakeOutParam( " @pageCount " , SqlDbType.Int,  4 ),
                MakeOutParam( " @Counts " , SqlDbType.Int,  4 )
            };
         try
        {
            ds  =  RunProcSet( " sp_ListPage " , parms);
            PageCount  =  Convert.ToInt32(parms[ 8 ].Value);
            Counts  =  Convert.ToInt32(parms[ 9 ].Value);
             return  ds;
        }
         catch  (Exception ex)
        {
            ex.Message.ToString();
             return  ds;
        }
    }
        ///  输入 param.
     public  SqlParameter MakeInParam( string  ParamName, SqlDbType DbType,  int  Size,  object  Value)
    {
         return  MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
    }
     ///   <returns> New parameter. </returns>
     public  SqlParameter MakeOutParam( string  ParamName, SqlDbType DbType,  int  Size)
    {
         return  MakeParam(ParamName, DbType, Size, ParameterDirection.Output,  null );
    }
     public  SqlParameter MakeParam( string  ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction,  object  Value)
    {
        SqlParameter param;
         if  (Size  >   0 )
            param  =   new  SqlParameter(ParamName, DbType, Size);
         else
            param  =   new  SqlParameter(ParamName, DbType);
        param.Direction  =  Direction;
         if  ( ! (Direction  ==  ParameterDirection.Output  &&  Value  ==   null ))
            param.Value  =  Value;
         return  param;
    }
     // 关闭连接
     public   void  close()
    {
        conn.Close();
    }
     private  SqlCommand CreateCommand( string  procName, SqlParameter[] prams)
    {
         //  打开数据源
        conn.Open();
        SqlCommand cmd  =   new  SqlCommand(procName, conn);
        cmd.CommandType  =  CommandType.StoredProcedure;
         //  add proc parameters
         if  (prams  !=   null )
        {
             foreach  (SqlParameter parameter  in  prams)
                cmd.Parameters.Add(parameter);
        }
         //  return param
        cmd.Parameters.Add(
             new  SqlParameter( " ReturnValue " , SqlDbType.Int,  4 ,
            ParameterDirection.ReturnValue,  false ,  0 ,  0 ,
             string .Empty, DataRowVersion.Default,  null ));
         return  cmd;
    }
     /// /执行存储过程，并返回值
     public  DataSet RunProcSet( string  procName, SqlParameter[] prams)
    {
        SqlCommand cmd  =  CreateCommand(procName, prams);
        SqlDataAdapter adpt  =   new  SqlDataAdapter();
        adpt.SelectCommand  =  cmd;
        DataSet ds  =   new  DataSet();
        adpt.Fill(ds);
         return  ds;
    }

}

 

 

 

三、C#后台调用类

 

pubulics mypubulics=new publics(

DataSet ds = mypubulics.Page_list( "tabel_ ", "NewsID,title,TypeAID,city,area,dt", 20, m,paixu, 1, strCondition, "dt", ref PageCount, ref Counts);

DataView dataview = new DataView();

dataview = ds.Tables[0].DefaultView;

读取：

title = ds.Tables[0].Rows[i][ "Title"].ToString();//标题