多表关联分页查询存储过程（SqlServer）

1.存储过程

    数据库函数exec()执行sql字符串有“sql注入风险”，不推荐使用！

CREATE PROCEDURE [dbo].[p_PageList]
@Tables varchar(1000),              --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID
@Sort varchar(200) = '',            --排序字段和排序方向
@PageNumber int = 1,                --开始页码
@PageSize int = 10,                 --页码尺寸
@Fields varchar(1000) = '*',        --读取字段
@Filter varchar(2000) = NULL,       --Where条件 长度不够，从1000变为2000
@isCount bit = 0,                   --是否获得总记录数
@TotalCounts    int = 0 output      --查询到的总记录数
AS
DECLARE @strFilter varchar(2000)     --长度不够，从2000变为2000
declare @sql varchar(max)            --长度不够，从2000变为max
Declare @strTmp nvarchar(max)        --存放取得查询结果总数的查询语句
 
-- 过滤数据
SET @strFilter = ' 1 = 1 '
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
 SET @strFilter += @Filter
END
 
--获得总记录条数
if @isCount = 1                    
begin
    set @strTmp = 'SELECT @TotalCounts= Count(1) FROM '+ @Tables + ' where ' + @strFilter
    exec sp_executesql @strTmp,N'@TotalCounts int out ',@TotalCounts out 
end
 
--查询所有数据
IF @PageNumber < 1
    SET @PageNumber = 1
if @PageSize = 0
begin  
    if @Sort = ''
    begin
        set @sql = 'select ' + @Fields + ' from ' + @Tables + ' WHERE  ' + @strFilter
    end
    else
    begin
        set @sql = 'select ' + @Fields + ' from ' + @Tables + ' WHERE  ' + @strFilter + ' ORDER BY  '+ @Sort   
    end
end
else
begin
    if @PageNumber = 1              --第一页提高性能
    begin
        if @Sort = ''
        begin
            set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ '  from ' + @Tables + ' WHERE ' + @strFilter
        end
        else
        begin
            set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ '  from ' + @Tables + ' WHERE ' + @strFilter + ' ORDER BY  '+ @Sort
        end
    end
    else
    begin
        DECLARE @START_ID varchar(50)
        DECLARE @END_ID varchar(50)
        SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
        SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)
        if @Sort = ''
        begin
            set @sql =  ' SELECT * FROM ( ' +
                ' SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS rownum, ' + @Fields +
                ' FROM ' + @Tables +
                ' WHERE ' + @strFilter +
            ') AS D WHERE rownum BETWEEN ' + @START_ID + ' AND ' + @END_ID
        end
        else
        begin
            set @sql =  ' SELECT * FROM ( ' +
                ' SELECT ROW_NUMBER() OVER(ORDER BY ' + @Sort + ') AS rownum, ' + @Fields +
                ' FROM ' + @Tables +
                ' WHERE ' + @strFilter +
            ') AS D WHERE rownum BETWEEN ' + @START_ID + ' AND ' + @END_ID
        end
    END
END
 
PRINT @sql
EXEC(@sql)
 
GO

2.C#调用存储过程的方法

public class PageData
{
    /// <summary>
    /// 新的分页获取数据
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="fieldName"></param>
    /// <param name="pageSize"></param>
    /// <param name="currentPage"></param>
    /// <param name="sortField">排序字段和排序方向,如：SortA DESC；</param>
    /// <param name="condition"></param>
    /// <param name="isCount">是否查询总数</param>
    /// <param name="totalCount">查询到的总记录数</param> 
    /// <returns></returns>
    public static DataSet GetDataByPage(
        string tableName,
        string fieldName,
        int pageSize,
        int currentPage,
        string sortField,
        string condition,
        bool isCount,
        out int totalCount
        )
    {
        SqlConnection CONN = new SqlConnection("数据库连接字符串");
        SqlDataAdapter SDA = new SqlDataAdapter("p_PageList",CONN);
        SDA.SelectCommand.CommandType = CommandType.StoredProcedure;
        SDA.SelectCommand.CommandTimeout = 30000;
 
        SDA.SelectCommand.Parameters.Add("@Tables", SqlDbType.VarChar,1000).Value = tableName;
        SDA.SelectCommand.Parameters.Add("@Sort", SqlDbType.VarChar,200).Value = sortField;
        SDA.SelectCommand.Parameters.Add("@PageNumber", SqlDbType.Int).Value = currentPage  ;
        SDA.SelectCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value =  pageSize;
        SDA.SelectCommand.Parameters.Add("@Fields", SqlDbType.VarChar,1000).Value =   fieldName;
        SDA.SelectCommand.Parameters.Add("@Filter", SqlDbType.VarChar, 2000).Value = condition;    
        SDA.SelectCommand.Parameters.Add("@Group", SqlDbType.VarChar,1000).Value = null;
        SDA.SelectCommand.Parameters.Add("@isCount", SqlDbType.Bit).Value = isCount;
        SDA.SelectCommand.Parameters.Add("@TotalCounts", SqlDbType.Int).Direction = ParameterDirection.Output;
 
        DataSet ds = new DataSet();
        if (CONN.State == ConnectionState.Closed)
        {
             CONN.Open();
        }
        SDA.Fill(ds);
        if (CONN.State == ConnectionState.Open)
        {
             CONN.Close();
        }
 
        try
        {
            totalCount =int.Parse(SDA.SelectCommand.Parameters["@TotalCounts"].Value.ToString());
        }
        catch
        {
            totalCount = 0;
        }
        return ds;
    }