列表分页通用存储过程

这是我的第一篇博客，不晓得咋写，先写一个分页的存储过程吧，这个比较常用，也是从别处Copy的。

在使用ado.net做列表分页时，使用存储过程还是很有必要的。

以下为存储过程代码

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[DB_SlitPage]    Script Date: 2018-03-22 15:47:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER  PROCEDURE [dbo].[Pro_Pagination] 
 @TableName varchar(300),           --表名
 @Fields varchar(5000) = '*',      --字段名(全部字段为*)
 @OrderField varchar(5000),        --排序字段(必须!支持多字段)
 @sqlWhere varchar(5000) = Null,   --条件语句(不用加where)
 @pageSize int,                    --每页多少条记录
 @pageIndex int = 1 ,			   --指定当前为第几页
 @totalRecord int output,
 @TotalPage int output            --返回总页数
AS
BEGIN
    
    Begin Tran --开始事务

    Declare @sql nvarchar(4000); 

    --if @totalRecord<=0 begin
        --计算总记录数
             
        if (@sqlWhere='' or @sqlWhere IS NULL)
            set @sql = 'select @totalRecord = count(*) from ' + @TableName
        else
            set @sql = 'select @totalRecord = count(*) from ' + @TableName + '  where ' + @sqlWhere

        EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数      
    --end
    --计算总页数
    select @TotalPage=CEILING((@totalRecord+0.0)/@pageSize)

    if (@sqlWhere='' or @sqlWhere IS NULL)
		if(@OrderField=''or @OrderField is null)
			set @sql = 'Select * FROM (select ROW_NUMBER() as rowId,' + @Fields + ' from ' + @TableName 
		else
			set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 
    else
		if(@OrderField=''or @OrderField is null)
			set @sql = 'Select * FROM (select ROW_NUMBER() as rowId,' + @Fields + ' from ' + @TableName + '  where ' + @sqlWhere    
		else
			set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + '  where ' + @sqlWhere    
    --处理页数超出范围情况
    if @pageIndex<=0 
        Set @pageIndex = 1
    
	--if @pageIndex>@TotalPage
	--	Set @pageIndex = @TotalPage

     --处理开始点和结束点
    Declare @StartRecord int
    Declare @EndRecord int
    
	set @StartRecord = (@pageIndex-1)*@pageSize + 1
	set @EndRecord = @StartRecord + @pageSize - 1

	set @sql = @sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
		
     print @sql   
    Exec(@sql)
    ---------------------------------------------------
    If @@Error <> 0
      Begin
        RollBack Tran
        Return -1
      End
     Else
      Begin
        Commit Tran
        Return @totalRecord ---返回记录总数
      End   
END

　　下为后台在调用此存储过程时的代码

 //声明变量，赋值         
 string strwhere = "1=1";
 string pageIndex = "1";                  //当前页码
 string Fields = "";             //需要得到的字段
 string TableName = "";                   //需要查看的表名
 string OrderField = "";                //排序的字段名

   IDataParameter[] para ={
                                     new SqlParameter("@TableName",SqlDbType.NVarChar,500),
                                     new SqlParameter("@Fields",SqlDbType.NVarChar,500),
                                     new SqlParameter("@OrderField",SqlDbType.NVarChar,500),
                                     new SqlParameter("@sqlWhere",SqlDbType.NVarChar,500),
                                     new SqlParameter("@pageSize",SqlDbType.Int),
                                     new SqlParameter("@pageIndex",SqlDbType.Int),
                                     new SqlParameter("@totalRecord",SqlDbType.Int),
                                     new SqlParameter("@TotalPage",SqlDbType.Int)
                };
            para[0].Value = TableName;
            para[1].Value = Fields;
            para[2].Value = OrderField;
            para[3].Value = strwhere;
            para[4].Value = 20;
            para[5].Value = pageIndex;
            para[6].Direction = ParameterDirection.Output;
            para[7].Direction = ParameterDirection.Output;
            string sql = "Pro_Pagination";

//下为存储过程调用的方法
  /// <summary>
        /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            SqlConnection connection = Conn();
            SqlDataReader returnReader;
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            //returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            SqlDataAdapter da = new SqlDataAdapter(command);
            DataSet ds = new DataSet ();
            da.Fill(ds);
            return ds;

        }


        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        public SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }