Paging stored procedures that support arbitrary sorting (asp.net/sqlserver)

The code is as follows

Copy Code

------------------------------------ -Purpose: Paging stored procedures that support arbitrary sorting --Description: ------------------------------------ CREATE PROCEDURE [dbo]. [Up_getrecordbypageorder] @tblName varchar (255),--table name @fldName varchar (255),--Display field name @OrderfldName varchar (255),--Sort field name @StatfldName varchar (255),--Statistics field name @PageSize int = 10,--page size @PageIndex int = 1,--page number @IsReCount bit = 0--Returns the total number of records, not 0 values. @OrderType bit = 0,--set sort type, not 0 value descending @strWhere varchar (1000) = '--Query criteria (note: Do not add where) As DECLARE @strSQL varchar (6000)--subject sentence DECLARE @strTmp varchar (100)--Temporary variable (error may occur if query condition is too long, 100 can be modified 1000) DECLARE @strOrder varchar (400)--Sort type If @OrderType!= 0 Begin Set @strTmp = ' < (select Min ' Set @strOrder = ' ORDER by [' + @OrderfldName + '] desc ' End Else Begin Set @strTmp = ' > select Max ' Set @strOrder = ' ORDER by [' + @OrderfldName + '] ASC ' End Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @fldName + ' 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) + ' + @fldName + ' from [' + @tblName + '] where [' + @OrderfldName + '] ' + @strTmp + ' ([' + @OrderfldName + ']) from (select Top + str (@PageIndex-1) * @PageSize) + ' [' + @OrderfldName + '] from [' + @tblName + '] where ' + @strWhere + ' + @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder If @PageIndex = 1 Begin Set @strTmp = ' If @strWhere!= ' Set @strTmp = ' where ' + @strWhere Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @fldName + ' from [' + @tblName + '] ' + @strTmp + ' + @strOrder End If @IsReCount!= 0 Set @strSQL = @strSQL + ' SELECT COUNT (1) as total from [' + @tblName + '] ' If @strWhere!= ' Set @strSQL = @strSQL + ' where ' + @strWhere EXEC (@strSQL)

------------------------------------
-Purpose: Paging stored procedures (highly efficient for tables with primary keys)
--Description:
------------------------------------

The code is as follows

Copy Code

CREATE PROCEDURE [dbo]. [Up_getrecordbypage] @tblName varchar (255),--table name @fldName varchar (255),--primary key field name @PageSize int = 10,--page size @PageIndex int = 1,--page number @IsReCount bit = 0--Returns the total number of records, not 0 values. @OrderType bit = 0,--set sort type, not 0 value descending @strWhere varchar (1000) = '--Query criteria (note: Do not add where) As DECLARE @strSQL varchar (6000)--subject sentence DECLARE @strTmp varchar (100)--Temporary variable (error may occur if query condition is too long, 100 can be modified 1000) DECLARE @strOrder varchar (400)--Sort type If @OrderType!= 0 Begin Set @strTmp = ' < (select Min ' Set @strOrder = ' ORDER by [' + @fldName + '] desc ' End Else Begin Set @strTmp = ' > select Max ' Set @strOrder = ' ORDER by [' + @fldName + '] ASC ' End Set @strSQL = ' SELECT top ' + str (@PageSize) + ' * FROM [' + @tblName + '] where [' + @fldName + '] ' + @strTmp + ' ([' + @fldName + ']) from (select Top + str (@PageIndex-1) * @PageSize) + ' [' + @fldName + '] from [' + @tblName + '] ' + @strOrder + ') as Tbltmp ' + @strOrder If @strWhere!= ' Set @strSQL = ' SELECT top ' + str (@PageSize) + ' * FROM [' + @tblName + '] where [' + @fldName + '] ' + @strTmp + ' ([' + @fldName + ']) from (select Top + str (@PageIndex-1) * @PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' + @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder If @PageIndex = 1 Begin Set @strTmp = ' If @strWhere!= ' Set @strTmp = ' where ' + @strWhere Set @strSQL = ' SELECT top ' + str (@PageSize) + ' * FROM [' + @tblName + '] ' + @strTmp + ' + @strOrder End If @IsReCount!= 0 Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] ' + ' where ' + @strWhere EXEC (@strSQL)

The following is the stored procedure (sqlserver2000)

--The most common paging stored procedure
--Gets the data for the specified page

The code is as follows

Copy Code

CREATE PROCEDURE Pagination @tblName varchar (255),--table name @strGetFields varchar (1000) = ' * ',--columns to be returned @fldName varchar (255) = ',--sorted field name @PageSize int = 10,--page size @PageIndex int = 1,--page number @doCount bit = 0--Returns the total number of records, not 0 values. @OrderType bit = 0,--set sort type, not 0 value descending @strWhere varchar (1500) = '--Query criteria (note: Do not add where) As DECLARE @strSQL varchar (5000)--subject sentence DECLARE @strTmp varchar (110)--Temporary variable DECLARE @strOrder varchar (400)--Sort type If @doCount!= 0 Begin If @strWhere!= ' Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] where ' + @strWhere Else Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] ' End --The above code means that if @docount passes over 0, the total count is executed. All of the following code is --It's the case of @docount 0. Else Begin If @OrderType!= 0 Begin Set @strTmp = ' < (select Min ' Set @strOrder = ' ORDER by [' + @fldName + '] desc ' If @ordertype is not 0, it is important to perform descending order. End Else Begin Set @strTmp = ' > select Max ' Set @strOrder = ' ORDER by [' + @fldName + '] ASC ' End If @PageIndex = 1 Begin If @strWhere!= ' Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] where ' + @strWhere + ' + @str Order Else Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] ' + @strOrder --If the first page executes the above code, this will speed up execution End Else Begin

--The following code gives @strsql the SQL code to actually execute

Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] where [' + @fldName + '] ' +

The code is as follows

Copy Code

@strTmp + ' ([' + @fldName + ']) From (select Top + str (@PageIndex-1) * @PageSize) + ' [' + @fldName + '] from [' + @tblName + '] ' + @strOrder + ') as Tbltmp ) ' + @strOrder If @strWhere!= ' Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] where [' + @fldName + '] ' + @str TMP + ' ([' + @fldName + ']) from (select Top + str (@PageIndex-1) * @PageSize) + ' [' + @fldName + '] From [' + @tblName + '] where ' + @strWhere + ' + @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder End End EXEC (@strSQL) Go

The following is the code for C #

The code is as follows

Copy Code

Using System.Data; Using System.Data.SqlClient; Using Microsoft.ApplicationBlocks.Data; Using System.Web; Using System.Web.UI; Namespace Rsslayer.pagehelper { /**////<summary> A summary description of the paging class Pagerhelper. </summary> public class Pagerhelper { private string connectionString; Public Pagerhelper (String tblname,string sortname,bool docount,string connectionString) { This.tblname = tblname; This.fldname = Sortname; this.connectionstring = connectionString; This.docount = Docount; } Public Pagerhelper (String Tblname,bool docount, String Strgetfields, String Fldname,int pagesize, int Pageindex,bool ordertype,string strwhere,string connectionString ) { This.tblname = tblname; This.docount = Docount; This.strgetfields = Strgetfields; This.fldname = Fldname; This.pagesize = pagesize; This.pageindex = pageindex; This.ordertype = OrderType; This.strwhere = strwhere; this.connectionstring = connectionString; } /**////<summary> Get the constructor of the recordset </summary> <param name= "Tblname" ></param> <param name= "Strwhere" ></param> <param name= "ConnectionString" ></param> Public Pagerhelper (String tblname,string strwhere,string connectionString) { This.tblname = tblname; This.strwhere = strwhere; This.docount = true; this.connectionstring = connectionString; } private string tblname; public string Tblname { Get{return Tblname;} Set{tblname =value;} } private String strgetfields= "*"; public string Strgetfields { Get{return Strgetfields;} Set{strgetfields =value;} } private string fldname=string. Empty; public string Fldname { Get{return Fldname;} Set{fldname =value;} } private int pagesize = 10; public int PageSize { Get{return pagesize;} Set{pagesize =value;} } private int pageindex = 1; public int PageIndex { Get{return pageindex;} Set{pageindex =value;} } private bool Docount=false; public bool Docount { Get{return Docount;} Set{docount =value;} } private bool Ordertype=false; public bool OrderType { Get{return OrderType;} Set{ordertype =value;} } private string strwhere=string. Empty; public string strwhere { Get{return strwhere;} Set{strwhere =value;} } Public IDataReader Getdatareader () { if (This.docount) { throw new ArgumentException ("To return the Recordset, the Docount property must be false"); } System.Web.HttpContext.Current.Response.Write (pageindex); Return Sqlhelper.executereader (connectionstring,commandtype.storedprocedure, "pagination", New SqlParameter ("@tblName", This.tblname), New SqlParameter ("@strGetFields", This.strgetfields), New SqlParameter ("@fldName", This.fldname), New SqlParameter ("@PageSize", This.pagesize), New SqlParameter ("@PageIndex", This.pageindex), New SqlParameter ("@doCount", This.docount), New SqlParameter ("@OrderType", This.ordertype), New SqlParameter ("@strWhere", This.strwhere) ); } Public DataSet GetDataSet () { if (This.docount) { throw new ArgumentException ("To return the Recordset, the Docount property must be false"); } Return SqlHelper.ExecuteDataset (connectionstring,commandtype.storedprocedure, "pagination", New SqlParameter ("@tblName", This.tblname), New SqlParameter ("@strGetFields", This.strgetfields), New SqlParameter ("@fldName", This.fldname), New SqlParameter ("@PageSize", This.pagesize), New SqlParameter ("@PageIndex", This.pageindex), New SqlParameter ("@doCount", This.docount), New SqlParameter ("@OrderType", This.ordertype), New SqlParameter ("@strWhere", This.strwhere) ); } public int GetCount () { if (!this.docount) { throw new ArgumentException ("to return the total count, the Docount attribute must be true"); } return (int) sqlhelper.executescalar (connectionstring,commandtype.storedprocedure, "pagination", New SqlParameter ("@tblName", This.tblname), New SqlParameter ("@strGetFields", This.strgetfields), New SqlParameter ("@fldName", This.fldname), New SqlParameter ("@PageSize", This.pagesize), New SqlParameter ("@PageIndex", This.pageindex), New SqlParameter ("@doCount", This.docount), New SqlParameter ("@OrderType", This.ordertype), New SqlParameter ("@strWhere", This.strwhere) ); } } }