Document directory
- I wrote a paging stored procedure, and it feels pretty easy to use.
Stored Procedure for one page ()
I wrote a stored procedure for paging and found it quite easy to use -- ========================== ============================
-- Create procedure basic template
-- ===================================================== ======
-- Creating the store procedure
If exists (Select name
From sysobjects
Where name = n' chypage'
And type = 'P ')
Drop procedure chypage
Go
/*
* @ Curentpage current page
* @ Pagesize number of records per page
* @ Tablename: Table Name
* @ Key primary key (Auto sort)
* @ Where query Condition
1) null is null
2) do not include where when there are query Conditions
* @ Order '0' indicates that DESC '1' is ASC
* @ Pagecount total page number
*/
Create procedure chypage
@ Currentpage int, @ pagesize int,
@ Tablename varchar (20), @ key varchar (50 ),
@ Where varchar (30), @ order varchar (1 ),
@ Pagecount int output, @ STR varchar (300) Output
As
Begin
--------------- Executed SQL statement ------------
Declare @ SQL nvarchar (400), @ ordreby nvarchar (200)
Declare @ tempsql1 varchar (200), @ tempsql2 varchar (200)
--------------- Total number of records -----------------
Declare @ count int
--------------- Temporary Variable ------------------------
Declare @ temp1 int, @ temp2 int
Set @ tablename = '[email =]' + @ tablename + '[/Email]'
Set @ key = '[email =]' + @ key + '[/Email]'
If @ order = '0'
Set @ ordreby = 'order by [email =] '+ @ key + 'desc' [/Email]
Else
Set @ ordreby = 'order by [email =] '+ @ key [/Email]
If @ where = 'null'
Set @ SQL = 'select @ COUNT = count (*) from '+ @ tablename
Else
Set @ SQL = 'select @ COUNT = count (*) from '+ @ tablename + 'where [email =]' + @ where [/Email]
------------ @ Count to pay the value (declare the variable @ count to indicate the output type )---------------------------
Exec sp_executesql @ SQL, n' @ count int out', @ count out
------------ Calculate the total number of pages ------------------------------
If (@ count % @ pagesize) = 0
Set @ pagecount = @ count/@ pagesize
Else
Set @ pagecount = @ count/@ pagesize + 1
----------- Determine whether the current page is displayed abnormal ------------------
If @ currentpage> @ pagecount
Set @ currentpage = @ pagecount
If @ currentpage <1
Set @ currentpage = 1
---------- The number of records is less than the number of records displayed on the page -----------------
If (@ currentpage = 1)
Begin
If @ where = 'null'
Set @ where =''
Else
Set @ where = 'where [email =] '+ @ where [/Email]
Set @ SQL = 'select top '+ STR (@ pagesize) +' * from [email =] '+ @ tablename + @ where + @ ordreby [/Email]
End
Else
Begin
/* --------------- DESC ----------------------
* @ Temp1 indicates the previous record
* @ Temp2 indicates the subsequent record
* Assume a total of 77 records, with 10 records each time. Retrieve 67 ~ 58 (page 2nd), remove the previous 57 (1 ~ 57) and 10 (77 ~ 66)
*/
If @ order = 0
Begin
Set @ temp1 = @ count-@ currentpage * @ pagesize
If @ temp1 <0
Set @ temp1 = 0
Set @ temp2 = (@ currentpage-1) * @ pagesize
If @ where = 'null'
Begin
Set @ tempsql1 = 'select top '+ STR (@ temp1) + '[email =]' + @ key + '[/Email] from' + @ tablename + 'ORDER BY' + @ key
Set @ tempsql2 = 'select top '+ STR (@ temp2) +' [email =] '+ @ key +' [/Email] from' + @ tablename + @ ordreby
End
Else
Begin
Set @ tempsql1 = 'select top '+ STR (@ temp1) +'
[Email =] '+ @ key +' [/Email] from' + @ tablename + 'where
[Email =] '+ @ where +' [/Email] order by '+ @ key
Set @ tempsql2 = 'select top '+ STR (@ temp2) +'
[Email =] '+ @ key +' [/Email] from' + @ tablename + 'where
[Email =] '+ @ where + @ ordreby [/Email]
End
Set @ SQL = 'select top '+ STR (@ pagesize) + '* from' + @ tablename + 'where [email =]' + @ key [/Email] + 'not in'
Set @ SQL = @ SQL + '(' + @ tempsql1 + ') and'
Set @ SQL = @ SQL + @ key + 'not in ([email =] '+ @ tempsql2 [/Email] + ')'
If @ where = 'null'
Set @ SQL = @ SQL + @ ordreby
Else
Set @ SQL = @ SQL + 'and [email =]' + @ where + @ ordreby [/Email]
End
/* ---------------- ASC ---------------------
* @ Temp indicates the total number of records shown above
* Remove @ temp and retrieve pagesize.
*/
Else
Begin
Set @ temp1 = (@ currentpage-1) * @ pagesize
If @ where = 'null'
Set @ tempsql1 = 'select top '+ STR (@ temp1) +' [email =] '+ @ key +' [/Email] from' + @ tablename + @ ordreby
Else
Set @ tempsql1 = 'select top '+ STR (@ temp1) +'
[Email =] '+ @ key +' [/Email] from' + @ tablename ++ 'where
[Email =] '+ @ where + @ ordreby [/Email]
Set @ SQL = 'select top '+ STR (@ pagesize) + '* from' + @ tablename + 'where [email =]' + @ key [/Email] + 'not in'
Set @ SQL = @ SQL + '([email =]' + @ tempsql1 + '[/Email])'
If @ where = 'null'
Set @ SQL = @ SQL + @ ordreby
Else
Set @ SQL = @ SQL + 'and [email =]' + @ where + @ ordreby [/Email]
End
/*-------------------------------------*/
End
Set @ STR = @ SQL
Exec sp_executesql @ SQL
End
Go
---------------------------- Run the Stored Procedure ------------------------------------------------
Declare @ STR varchar (300), @ pagecount int
Exec chypage-1, 5, 'products', 'produd d', 'productid <= 48', '1', @ pagecount output, @ STR output
Print 'total page number: '+ STR (@ pagecount)
Print @ Str
// Obtain the title paging data
Public static string gettitlepaginationdata (string storename, string kindid, int page, string where)
{
Sqlparameter [] P = new sqlparameter [8];
P [0] = new sqlparameter ();
P [0]. parametername = "@ currentpage ";
P [1] = new sqlparameter ();
P [1]. parametername = "@ pagesize ";
P [2] = new sqlparameter ();
P [2]. parametername = "@ tablename ";
P [3] = new sqlparameter ();
P [3]. parametername = "@ key ";
P [4] = new sqlparameter ();
P [4]. parametername = "@ where ";
P [5] = new sqlparameter ();
P [5]. parametername = "@ order ";
P [6] = new sqlparameter ();
P [6]. parametername = "@ pagecount ";
P [7] = new sqlparameter ();
P [7]. parametername = "@ Str ";
P [0]. sqldbtype = P [1]. sqldbtype = P [6]. sqldbtype = sqldbtype. Int;
P [2]. sqldbtype = P [3]. sqldbtype = P [4]. sqldbtype = P [5]. sqldbtype = P [7]. sqldbtype = sqldbtype. varchar;
P [2]. size = P [3]. size = 30;
P [4]. size = 50;
P [5]. size = 1;
P [7]. size = 450;
P [0]. value = page;
P [1]. value = bbsclass. titliepagesize;
P [2]. value = tablename;
P [3]. value = "title_id ";
P [4]. value = where + kindid;
P [5]. value = '0 ';
P [6]. value = 0;
P [7]. Direction = parameterdirection. output;
Sqlcommand cmd = datahelpe. getcommand (storename, P );
Cmd. executenonquery ();
Return cmd. Parameters ["@ Str"]. value. tostring ();
}
/// <Summary>
/// Call the stored procedure to create a sqlcommand object
/// </Summary>
/// <Param name = "procname"> Stored Procedure </param>
/// <Param name = "prams"> transfer the sqlparameter object to the stored procedure </param>
/// <Returns> sqlcommand </returns>
Public static sqlcommand getcommand (string storename, sqlparameter [] prams)
{
Sqlconnection connection = new sqlconnection (m_constr );
Try
{
Sqlcommand cmd = new sqlcommand (storename, connection); // name of the stored procedure
Cmd. commandtype = commandtype. storedprocedure;
Connection. open ();
If (prams! = NULL)
{
Foreach (sqlparameter item in prams)
{
Cmd. Parameters. Add (item );
}
}
Cmd. Prepare ();
Return cmd;
}
Catch (sqlexception ex)
{
Throw new exception (ex. Message );
}
}
