SQL universal stored procedure paging support for multi table federation

SQLPager stored procedures

 
ALTER proc [dbo].[SqlPager] 
( 
@tblName varchar(255), --  The name of the table ( Note: multiple tables can be linked ) 
@strGetFields varchar(1000) = '*', --  The columns that need to be returned  
@OrderfldName varchar(255)='', --  Sort the field name  
@PageSize int = 10, --  Page size  
@PageIndex int = 1, --  The page number  
@doCount int = 1 output, -- Number of records queried  
@OrderType bit = 0, --  Set the sort type ,  non  0  Value is descending  
@strWhere varchar(500) = '' --  Query conditions  ( Pay attention to :  Don't add  where) 
) 
AS 
declare @strSQL nvarchar(4000) --  The subject line  
declare @strTmp varchar(110) --  Temporary variable  
declare @strOrder varchar(300) --  Sorting type  

if @strWhere != '' 
set @strSQL = 'select @doCount=count(*) from ' + @tblName + ' where '+@strWhere 
else 
set @strSQL = 'select @doCount=count(*) from ' + @tblName 
exec sp_executesql @strSQL,N'@doCount int out',@doCount out 
-- The above code means if @doCount It's not passed on 0 To perform total statistics. All of the following code is @doCount for 0 In the case  
set @strSQL=''; 

if @OrderType != 0 
begin 
set @strTmp = '<(select min' 
set @strOrder = ' order by [' + @OrderfldName +'] desc'-- if @OrderType not 0 , in descending order, this sentence is very important!  
end 
else 
begin 
set @strTmp = '>(select max' 
set @strOrder = ' order by [' + @OrderfldName +'] asc' 
end 
if @PageIndex = 1 
begin 
if @strWhere != '' 
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder 
else 
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder-- If it is the first 1 Page to execute the above code, which will speed up the execution  
end 
else 
begin-- The following code is given @strSQL To really execute SQL code  
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' 
+ @tblName + ' where [' + @OrderfldName + ']' + @strTmp + '(['+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @OrderfldName + '] from ' + @tblName + ' ' + @strOrder + ') as tblTmp)'+ @strOrder 
if @strWhere != '' 
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' 
+ @tblName + ' where [' + @OrderfldName + ']' + @strTmp + '([' 
+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' 
+ @OrderfldName + '] from ' + @tblName + ' where ' + @strWhere + ' ' 
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 
end 
exec (@strSQL) 

This is my database operation class. The method is a bit old, and still has a 3-tier architecture.(mvc should be similar. I haven't tried it yet.)

 
/// <summary> 
///  Paging (stored procedures)   Usage:  
/// </summary> 
/// <param> The name of the table ( Note: multiple tables can be linked )</param> 
/// <param> The columns that need to be returned </param> 
/// <param> A primary key </param> 
/// <param> Sort the field name </param> 
/// <param> Page size </param> 
/// <param> The page number </param> 
/// <param> Number of records queried </param> 
/// <param> Set the sort type , desc , asc</param> 
/// <param> Query conditions  ( Pay attention to :  Don't add  where)</param> 
/// <returns></returns> 
public static DataTable DbPager( 
string tblName, 
string strGetFields, 
string PkeyfldName, 
string OrderfldName, 
int PageSize, 
int PageIndex, 
out int doCount, 
string OrderType, 
string strWhere) 
{ 
SqlConnection con = new SqlConnection(ConnString.Value); 
SqlDataAdapter da = new SqlDataAdapter("DbPager", con); 
da.SelectCommand.CommandType = CommandType.StoredProcedure; 
da.SelectCommand.Parameters.Add(new SqlParameter("@tblName", tblName)); 
da.SelectCommand.Parameters.Add(new SqlParameter("@strGetFields", strGetFields)); 
da.SelectCommand.Parameters.Add(new SqlParameter("@PkeyfldName", PkeyfldName)); 
da.SelectCommand.Parameters.Add(new SqlParameter("@OrderfldName", OrderfldName)); 
da.SelectCommand.Parameters.Add(new SqlParameter("@PageSize", PageSize)); 
da.SelectCommand.Parameters.Add(new SqlParameter("@PageIndex", PageIndex)); 
da.SelectCommand.Parameters.Add("@doCount", SqlDbType.Int); 
da.SelectCommand.Parameters["@doCount"].Direction = ParameterDirection.Output; 
da.SelectCommand.Parameters.Add(new SqlParameter("@OrderType", OrderType)); 
da.SelectCommand.Parameters.Add(new SqlParameter("@strWhere", strWhere)); 
DataSet ds = new DataSet(); 
da.Fill(ds); 
doCount = Convert.ToInt32(da.SelectCommand.Parameters["@doCount"].Value); 
return ds.Tables[0]; 
}