分页存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

 

/*
作者：许松
CompanyName: 深圳神话时代网络
Date: 2010-11-03
*/

ALTER PROCEDURE [dbo].[SP_Pagination]
 -- Add the parameters for the stored procedure here
 --<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
 --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
 
 @strTableName varchar(2000) = '',  --查询的表
 @strWhere varchar(500) = '',   --查询条件
 @strSelectColumns varchar(1000) = '*', --需要得到的字段
 @currentPage int=1,    --当前页页码
 @pageSize int =12,    --设置页面的大小
 @orderby varchar(400) = 'ID',  --排序的字段名 (即 order by column asc/desc)
 @PKName varchar(50) = 'Id',  --主键名称
 @total int output    --记录总数
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 declare @startRow int
 declare @endRow int
 declare @strsql nvarchar(4000)

 set @strsql = 'select @total= count('+@PKName+') from ' + @strTableName + @strWhere

 exec sp_executesql @strsql,N'@total int output',@total output
 
 if @orderby is null or @orderby = ''
  begin
   set @orderby='Order by '+@PKName
  end
 
 if @currentPage >0 and @pageSize >0
 begin
  set @startRow = (@currentPage-1) * @pageSize
  set @endRow = @currentPage * @pageSize

  set @strsql = N'select * from (select  ROW_NUMBER() OVER ('+@orderby+') AS RowNum ,'+@strSelectColumns+' from '+ @strTableName +
  @strWhere +') as mainTable Where mainTable.RowNum>'+Convert(varchar(15),@startRow)+' and mainTable.RowNum<='+Convert(varchar(15),@endRow)
  
 

 end
 else
  set @strsql = N'select * from (select ROW_NUMBER() OVER ('+@orderby+') AS RowNum ,'+@strSelectColumns+' from '+ @strTableName + @strWhere +') as mainTable'
  
  EXECUTE sp_executesql @strsql

print @strsql  
  --
 --select @strsql
END

 

//-----------------------------------------------   调用   ---------------

 

  /// <summary>
  /// 执行分页存储过程方法
  /// </summary>
  /// <param name="strTableName">要查询的表名称和连接的表</param>
  /// <param name="strWhere">Where查询条件</param>
  /// <param name="strSelectColumns">需要选择查询的列名</param>
  /// <param name="CurrentPageIndex">当前页码</param>
  /// <param name="PageSize">每页的记录数大小</param>
  /// <param name="strOrderBy">Order By 排序语句</param>
  /// <param name="PKName">主查询表的主键名称</param>
  /// <param name="TotalCount">记录总数</param>
  /// <returns></returns>
  public static DataSet ExecPagenationSP(string strTableName,string strWhere,string strSelectColumns,int iCurrentPage,int iPageSize,string strOrderBy,string strPKName)
  {
   DataSet ds= new DataSet ();

   SqlConnection sqlCon = new SqlConnection(SqlHelper.ConnectionString);

   sqlCon.Open();

   try
   {
    SqlParameter[] m_SqlParameters = new SqlParameter[8];

    if (!string.IsNullOrEmpty(strTableName))
    {
     m_SqlParameters[0] = new SqlParameter("@strTableName", strTableName);
    }
    else
    {
     m_SqlParameters[0] = new SqlParameter("@strTableName",null);
    }
    if (!string.IsNullOrEmpty(strWhere))
    {
     m_SqlParameters[1] = new SqlParameter("@strWhere", strWhere);
    }
    else
    {
     m_SqlParameters[1] = new SqlParameter("@strWhere",null);
    }
    if (!string.IsNullOrEmpty(strSelectColumns))
    {
     m_SqlParameters[2] = new SqlParameter("@strSelectColumns", strSelectColumns);
    }
    else
    {
     m_SqlParameters[2] = new SqlParameter("@strSelectColumns",null);
    }
    m_SqlParameters[3] = new SqlParameter("@currentPage",iCurrentPage);

    m_SqlParameters[4] = new SqlParameter("@pageSize",iPageSize);

    if (!string.IsNullOrEmpty(strOrderBy))
    {
     m_SqlParameters[5] = new SqlParameter("@orderby", strOrderBy);
    }
    else
    {
     m_SqlParameters[5] = new SqlParameter("@orderby",null);
    }
    if (!string.IsNullOrEmpty(strPKName))
    {
     m_SqlParameters[6] = new SqlParameter("@PKName", strPKName);
    }
    else
    {
     m_SqlParameters[6] = new SqlParameter("@PKName",null);
    }
    m_SqlParameters[7] = new SqlParameter("@total",0);

    m_SqlParameters[7].Direction = ParameterDirection.Output;

                rs.DataResult = SqlHelper.ExecuteDataset(sqlCon, CommandType.StoredProcedure, "SP_Pagination", m_SqlParameters);

    int iTotal = DataConvert.GetInt32Value(m_SqlParameters[7].Value);

    rs.pageInfo = new PageInfo(iCurrentPage, iPageSize, iTotal);

    rs.IsSuccess = true;
   }
   catch (Exception ex)
   {
    rs.IsSuccess = false;
    rs.Msg = ex.Message;
    throw ex;
   }
   finally
   {
    if (sqlCon.State == System.Data.ConnectionState.Open)
    {
     sqlCon.Close();
    }
    sqlCon.Dispose();
   }
   return ds;
  }