SQL Server Stored Procedures and C # paging class simplify your code

SQL Server Stored Procedures and C # paging class simplify your code! The following is the Stored Procedure (passed under sqlserver2000)

-- The most common paging Stored Procedure
-- Get data on a specified page
 
Create procedure Pagination
 
@ TblName varchar (255), -- table name
 
@ StrGetFields varchar (1000) = '*', -- the column to be returned
 
@ FldName varchar (255) = '', -- Name of the sorted Field
 
@ PageSize int = 10, -- page size
 
@ PageIndex int = 1, -- page number
 
@ DoCount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
 
@ OrderType bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.
 
@ StrWhere varchar (1500) = ''-- Query condition (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 indicates that if @ doCount is not passed over 0, the total number of statistics will be executed. All the following codes are
-- The value of @ doCount is 0.
 
Else
 
Begin
 
 
 
If @ OrderType! = 0
 
Begin
 
Set @ strTmp = '<(select min'
 
Set @ strOrder = 'order by ['+ @ fldName +'] desc'
 
-- If @ OrderType is not 0, execute the descending order. This sentence is very important!
 
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 +'' + @ strOrder
 
Else
 
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ strGetFields +' from ['+ @ tblName +'] '+ @ strOrder
 
-- Execute the above Code on the first page, which will speed up the execution.
 
End
 
Else
 
Begin
 
-- The following code gives @ strSQL the SQL code to be actually executed
 
Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ strGetFields + '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) + ''+ @ strGetFields + 'from [' + @ tblName + '] where [' + @ fldName + ']' + @ strTmp + '([' + @ fldName +' ]) from (select top '+ str (@ PageIndex-1) * @ PageSize) +' ['+ @ fldName +']
From ['+ @ tblName +'] where' + @ strWhere + ''+ @ strOrder + ') as tblTmp) and' + @ strWhere +'' + @ strOrder
 
End
 
End
 
Exec (@ strSQL)
GO
 

Below is the C # code

Using System. Data;
Using System. Data. SqlClient;
Using Microsoft. ApplicationBlocks. Data;
Using System. Web;
Using System. Web. UI;
Namespace RssLayer. PageHelper
{
/** // <Summary>
/// Summary of 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.doc ount = 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.doc ount = docount;
This. strGetFields = strGetFields;
This. fldName = fldName;
This. pagesize = pagesize;
This. pageindex = pageindex;
This. ordertype = ordertype;
This. strwhere = strwhere;
This. connectionString = connectionString;
 
}
 
 
/** // <Summary>
/// Obtain the record set Constructor
/// </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.doc ount = 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.doc ount)
{
Throw new ArgumentException ("The DoCount attribute must be false to return the record set ");
}
 

 
// 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.doc ount ),
New SqlParameter ("@ OrderType", this. ordertype ),
New SqlParameter ("@ strWhere", this. strwhere)
);
}
 
Public DataSet GetDataSet ()
{
If(this.doc ount)
{
Throw new ArgumentException ("The DoCount attribute must be false to return the record set ");
}
 
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.doc ount ),
New SqlParameter ("@ OrderType", this. ordertype ),
New SqlParameter ("@ strWhere", this. strwhere)
);
}
 
 
Public int GetCount ()
{
If (! This.doc ount)
{
Throw new ArgumentException ("to return the total count statistics, 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.doc ount ),
New SqlParameter ("@ OrderType", this. ordertype ),
New SqlParameter ("@ strWhere", this. strwhere)
);
}
 
}
 

 
 
}
 

How to call ???

Assume that I have created two classes. One is the FavList database entity class and the other is the FavListCollection collection class. FavListCollection stores a set of FavList object classes.

I can write a method like this.

/** // <Summary>
/// Return to the FavList set and use the Stored Procedure custom page.
/// </Summary>
/// <Param name = "userid"> database FavList field, user id </param>
/// <Param name = "strwhere"> search criteria </param>
/// <Param name = "ordertype"> sort, true indicates Desc, and false indicates asc </param>
/// <Param name = "fldname"> A sorted field. Only one field can be used. </param>
/// <Param name = "pagesize"> Number of records per page </param>
/// <Param name = "pageindex"> the parameter to the page number, starting from 1. 1 indicates the first page, and so on. </Param>
/// <Param name = "recordcount"> total number of records. </Param>
/// <Returns> </returns>
Public override FavListCollection GetFavListsByUser (int userid, string strwhere,
Bool ordertype, string fldname, int pagesize,
Int pageindex, out int recordcount
)
{
Recordcount = 0;
PagerHelper helper = new PagerHelper ("Vfavlist", strwhere, ConnectionString); // VFavList is View
Recordcount = helper. GetCount ();

PagerHelper helper2 = new PagerHelper ("Vfavlist", false, "*", fldname,
Pagesize, pageindex, ordertype, strwhere, ConnectionString );
 
IDataReader dr = helper2.GetDataReader ();
 
FavListCollection list = new FavListCollection ();

While (dr. Read ())
{
List. Add (PopulateFavList (dr ));
}
 
Dr. Close ();
 
Return list;
}
You don't need to call the DataGrid ..

Bugs and limitations of this page

Bug: when the content of the sorted field is the same (for example, sort by time, and the time is the same. The subsequent records cannot be displayed. I tested it)

Limitation: Only one field can be sorted, and no more than one