分页存储过程及调用代码

数据库创建分页存储过程：

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Proc_GetInfoByPage]  
(  
  @TableName NVARCHAR(64),  
  @OrderBy NVARCHAR(256),  
  @Columns NVARCHAR(256) = '*',  
  @Where NVARCHAR(1024) = '',  
  @PageSize INT = 10,  
  @PageIndex INT = 1,  
  @RecordAmount INT = 0 OUTPUT 
)  
AS 
  DECLARE @Sql NVARCHAR(2048)  
  DECLARE @Filter NVARCHAR(2048)  
    
  -- Initialize ----------------------------------------------------------------------
    
  SET @Filter = '' 
  ----------------------------------------------------  
  IF (@Columns IS NULL OR LEN(@Columns) = 0) BEGIN 
    
    SET @Columns = '*' 
    
  END 
  ----------------------------------------------------  
  IF (LEN(@Where) > 0) BEGIN 
    
    SET @Filter = ' WHERE ' + @Where 
    
  END 
    
  -- Get record amount ---------------------------------------------------------------
    
  SET @Sql = 'SELECT @Count = COUNT(0) FROM ' + @TableName  
    
  SET @Sql = @Sql + @Filter  
    
  EXEC SP_EXECUTESQL @Sql, N'@Count INT OUTPUT',@RecordAmount OUTPUT 
    
  IF (@RecordAmount = 0) RETURN 
    
  -- Get bound -----------------------------------------------------------------------
    
  SET @PageIndex = @PageIndex - 1  
  DECLARE @TopBound INT 
  SET @TopBound = @PageIndex * @PageSize + 1  
  DECLARE @BottomBound INT 
  SET @BottomBound = @TopBound + @PageSize - 1  
    
  -- Get data ------------------------------------------------------------------------
    
  SET @Sql = 'SELECT * FROM (  
    
      SELECT ' + @Columns + ', ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS [RowNumber]  
      FROM ' + @TableName + @Filter + ' 
      
   ) AS [TempTable] WHERE [RowNumber] BETWEEN ' + CAST(@TopBound AS NVARCHAR) + ' AND ' + CAST(@BottomBound AS NVARCHAR)  
      
  EXEC SP_EXECUTESQL @Sql 

 

//服务器端调用分页存储过程

public partial class StudentDataDataContext
{
    public IList<TResultType> Proc_GetInfoByPage<TResultType>(int pageSize, int pageIndex,
         out int recordAmount, string tableName, string orderBy, string columns, string where)
    {

        DbCommand cm = this.Connection.CreateCommand();
        cm.CommandType = CommandType.StoredProcedure;
        cm.CommandText = "Proc_GetInfoByPage";

        #region Add Parameters

        DbParameter para1 = cm.CreateParameter();
        para1.DbType = DbType.String;
        para1.ParameterName = "@TableName";
        para1.Value = tableName;
        cm.Parameters.Add(para1);

        DbParameter para2 = cm.CreateParameter();
        para2.DbType = DbType.String;
        para2.ParameterName = "@OrderBy";
        para2.Value = orderBy;
        cm.Parameters.Add(para2);

        DbParameter para3 = cm.CreateParameter();
        para3.DbType = DbType.String;
        para3.ParameterName = "@Columns";
        para3.Value = columns;
        cm.Parameters.Add(para3);

        DbParameter para4 = cm.CreateParameter();
        para4.DbType = DbType.String;
        para4.ParameterName = "@Where";
        para4.Value = where;
        cm.Parameters.Add(para4);

        DbParameter para5 = cm.CreateParameter();
        para5.DbType = DbType.Int32;
        para5.ParameterName = "@PageSize";
        para5.Value = pageSize;
        cm.Parameters.Add(para5);

        DbParameter para6 = cm.CreateParameter();
        para6.DbType = DbType.Int32;
        para6.ParameterName = "@PageIndex";
        para6.Value = pageIndex;
        cm.Parameters.Add(para6);

        DbParameter para7 = cm.CreateParameter();
        para7.DbType = DbType.Int32;
        para7.ParameterName = "@RecordAmount";
        para7.Direction = ParameterDirection.Output;
        cm.Parameters.Add(para7);

        #endregion

        try
        {
            this.Connection.Open();

            DbDataReader dr = cm.ExecuteReader();

            IList<TResultType> list;

            // 这个判断不能少，否则照样和使用IMultipleResults的方法一样，出现异常。  
            if (dr.HasRows)
            {
                list = this.Translate<TResultType>(dr).ToList();
            }
            else
            {
                list = new List<TResultType>(0);
            }

            recordAmount = (int)para7.Value;

            return list;
        }
        catch
        {
            throw;
        }
        finally
        {
            cm.Dispose();
            this.Connection.Close();
        }
    }
}