SQL Common stored procedure paging, supporting multiple table joint _ Practical Tips

SqlPager Stored Procedures

Copy Code code as follows:

ALTER proc [dbo]. [SqlPager]
(
@tblName varchar (255),--Table name (Note: You can link multiple tables)
@strGetFields varchar (1000) = ' * ',--columns to be returned
@OrderfldName varchar (255) = ',--sorted field name
@PageSize int = 10,--page size
@PageIndex int = 1,--page number
@doCount int = 1 output,--number of records queried
@OrderType bit = 0,--set sort type, not 0 value descending
@strWhere varchar (500) = '--Query criteria (note: Do not add where)
)
As
declare @strSQL nvarchar (4000)--subject sentence
DECLARE @strTmp varchar (110)--Temporary variable
DECLARE @strOrder varchar (300)--Sort 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 that if @docount passes over 0, the total count is executed. All of the following code is @docount 0
Set @strSQL = ';

If @OrderType!= 0
Begin
Set @strTmp = ' < (select Min '
Set @strOrder = ' ORDER by [' + @OrderfldName + '] desc '--if @ordertype is not 0, it's 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 + ' + ' + @strO Rder
Else
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from ' + @tblName + ' + @strOrder--if it is the first page to execute the above code, this will speed up the enforcement Line speed
End
Else
begin--The following code gives @strsql the actual execution of the 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)

Write execution stored procedures in the SqlHelper class this is my database operations class. The method is a bit old, or three-tier architecture. (MVC should be similar.) I have a try.

Copy Code code as follows:

<summary>
The paging (stored procedure) legislation is as follows:
</summary>
<param> table Name (Note: You can link to multiple links) </param>
<param> columns that need to be returned </param>
<param> PRIMARY Key </param>
<param> sorted field name </param>
<param> Page Size </param>
<param> page </param>
<param> number of records to query </param>
<param> set Sort type, desc, asc</param>
<param> Query Criteria (Note: Do not 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];
}