SQLPager stored procedure
ALTER proc [dbo]. [SqlPager]
(
@ TblName varchar (255), -- table name (Note: Multiple table links are allowed)
@ StrGetFields varchar (1000) = '*', -- the column to be returned
@ OrderfldName varchar (255) = '', -- Name of the sorted field
@ PageSize int = 10, -- page size
@ PageIndex int = 1, -- page number
@ DoCount int = 1 output, -- number of records queried
@ OrderType bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ StrWhere varchar (500) = ''-- query condition (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 indicates that if @ doCount is not passed over 0, the total number of statistics will be executed. All the code below is 0 @ doCount
Set @ strSQL = '';
If @ OrderType! = 0
Begin
Set @ strTmp = '<(select min'
Set @ strOrder = 'Order by ['+ @ OrderfldName +'] 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 ['+ @ 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 -- execute the above code on the first page, which will speed up execution.
End
Else
Begin -- The following code grants @ strSQL the SQL code to be actually executed
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)
In the SQLHelper class, write and execute the stored procedure. This is my database operation class. The method is a bit old, or a layer-3 architecture. (mvc should be similar. I still have a try .)
/// <Summary>
/// The usage of paging (stored procedure) is as follows:
/// </Summary>
/// <Param> table name (Note: Multiple table links are supported) </param>
/// <Param> columns to be returned </param>
/// <Param> primary key </param>
/// <Param> name of the sorted field </param>
/// <Param> page size </param>
/// <Param> Page number </param>
/// <Param> Number of records queried </param>
/// <Param> set the sorting type, desc, asc </param>
/// <Param> query condition (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];
} |
