Stored Procedure page, sorted by multiple conditions (original)

CS page call code:

 

 

Public int totalpage = 0;
Public int pagecurrent = 1;
Public int pagesize = 25;
Public int rowscount = 0;
String userid, username;
Public datatable dt = new datatable ();
Public String path, userwelcome;
Public String opt, CID;
Protected void page_load (Object sender, eventargs E)
{
If (! Ispostback)
{
If (request. Params ["page"] = NULL | request. Params ["page"]. tostring (). Equals (""))
Pagecurrent = 1;
Else
Pagecurrent = int. parse (request. Params ["page"]. tostring ());
This. getpage (Out totalpage, out rowscount, pagesize, pagecurrent );
}
}

// Call the Stored Procedure Function

Private void getpage (Out int totalpage, out int rowscount, int pagesize, int currentpage)
{
Sqlparameter [] parameters = {
New sqlparameter ("@ totalpage", sqldbtype. Int, 4 ),
New sqlparameter ("@ rowscount", sqldbtype. Int, 4 ),
New sqlparameter ("@ pagesize", sqldbtype. Int, 4 ),
New sqlparameter ("@ currentpage", sqldbtype. Int, 4 ),
New sqlparameter ("@ selectfields", sqldbtype. nvarchar, 700 ),
New sqlparameter ("@ idfield", sqldbtype. nvarchar, 50 ),
New sqlparameter ("@ orderfield", sqldbtype. nvarchar, 200 ),
New sqlparameter ("@ ordertype", sqldbtype. nvarchar, 2 ),
New sqlparameter ("@ tablename", sqldbtype. nvarchar, 300 ),
New sqlparameter ("@ strwhere", sqldbtype. nvarchar, 300 ),
};
Parameters [0]. Direction = parameterdirection. output;
Parameters [1]. Direction = parameterdirection. output;
Parameters [2]. value = pagesize;
Parameters [3]. value = currentpage;
Parameters [4]. value = "A. rlid, A. companyName, A. Website, A. isrl, A. ordernum, A. isrl, A. userid ";
Parameters [5]. value = "A. rlid ";

Parameters [6]. value = "A. isrl ASC, A. ordernum ";
Parameters [7]. value = "1 ";
Parameters [8]. value = "qiyerenling ";
Parameters [9]. value = "1 = 1 ";//

Dataset DS = wm23abc. dbutility. dbhelpersql. runprocedure ("getrecordbypage", parameters, "DT ");
Dt = Ds. Tables [0];
Totalpage = int. parse (parameters [0]. value. tostring ());
Rowscount = int. parse (parameters [1]. value. tostring ());
}

. ASPX page code:

<Table id = "sxfstable" style = "width: 100%;" class = "table">
<Tr> <TD> <B> company name </B> </TD> <B> company website </B> </TD> <B> Claim status </B> </TD> </tr>
<% For (INT I = 0; I <DT. Rows. Count; I ++)
{
%>
<Tr>
<TD> <% = DT. rows [I] ["companyName"]. tostring () %> sorting value: <% = DT. rows [I] ["ordernum"]. tostring () %> </TD>
<TD> <% = DT. Rows [I] ["website"]. tostring () %>
Claim?: <% = DT. Rows [I] ["userid"]. tostring () %> </TD>
<TD> <% = DT. Rows [I] ["isrl"]. tostring (). Equals ("0 ")? "<A href = \" javascript:; \ "onclick = \" renling (event, '"+ dt. rows [I] ["rlid"]. tostring () + "'); \"> claim the enterprise </a> ": "<font color = \" Red \ "> this enterprise has been claimed </font>" %> </TD>
</Tr>
<%
}
%>
</Table>
</Div>
<Div style = "margin-left: auto; margin-Right: auto; width: 70%; text-align: Left; font-size: 9pt;">
Page <% = pagecurrent %> total page <% = rowscount %> total page <% = totalpage %>
<% IF (pagecurrent! = 1)
{
%>
<A href = "test. aspx"> first page </a>
<A href = "test. aspx? Page = PageCurrent-1 %> "> previous </a>

<%
}
If (pagecurrent! = Totalpage)
{
%>
<A href = "test. aspx? Page = <% = pagecurrent + 1%> "> next page </a>
<A href = "test. aspx? Page = <% = totalpage %> "> last page </a>
<%
}
%>
</Div>

 

 

Stored Procedure Code:

Create proc [DBO]. [getrecordbypage]
@ Totalpage int output, -- total number of pages
@ Rowscount int output, -- total number of entries
@ Pagesize int, -- how much data per page
@ Currentpage int, -- current page number
@ Selectfields nvarchar (1000), -- SELECT statement but does not contain select
@ Idfield nvarchar (50), -- primary key column
@ Orderfield nvarchar (50), -- sorting field. If multiple fields exist, the sorting conditions (ASC/DESC) must be added except the last field, excluding order, the last sorting field does not need to be added with sorting conditions.
@ Ordertype nvarchar (4), -- 1 ascending, 0 descending
@ Tablename nvarchar (200), -- table name
@ Strwhere nvarchar (300) -- Condition
As
Begin
Declare @ recordcount float
Declare @ pagenum int -- number of pages
Declare @ compare nvarchar (50) -- Compare fields to distinguish between min and Max
Declare @ compare1 nvarchar (2) -- greater than ">" or less than "<"
Declare @ ordersql nvarchar (10) -- Sort Field
Declare @ SQL nvarchar (4000)
Declare @ temsql nvarchar (1000)
Declare @ NRD int
Declare @ afterrows int
Declare @ temptablename nvarchar (10)

If (@ ordertype = '1 ')
Begin
Set @ ordersql = 'asc'
End
Else
Begin
Set @ ordersql = 'desc'
End

If (isnull (@ strwhere, '') <> '')
Set @ strwhere = @ strwhere
If (@ strwhere = '')
Set @ strwhere = '1 = 1'

Set @ temsql = 'select @ recordcount = count (1) from '+ @ tablename + 'where' + @ strwhere
Exec sp_executesql @ temsql, n' @ recordcount float output', @ recordcount output
Set @ rowscount = @ recordcount
Set @ totalpage = ceiling (@ recordcount/@ pagesize)
If (@ currentpage> @ totalpage)
Set @ currentpage = @ totalpage
If (@ currentpage <1)
Set @ currentpage = 1
If (@ pagesize <1)
Set @ pagesize = 1
Print (@ recordcount)

 
If (@ currentpage = 1)
Begin
Set rowcount @ pagesize
Set @ SQL = 'select' + @ selectfields + 'from' + @ tablename + 'where' + @ strwhere + 'ORDER BY' + @ orderfield +'

'+ @ Ordersql +', '+ @ idfield + 'asc'
-- Print (@ SQL)
Exec sp_executesql @ SQL

End
Else if (@ currentpage = @ totalpage)
Begin
Set @ afterrows = @ rowscount-(@ CurrentPage-1) * @ pagesize
Set rowcount @ afterrows
If (@ ordertype = '1 ')
Begin
Set @ orderfield = Replace (@ orderfield, 'asc ', 'lai512343975') // use the variables here to swap ASC and desc. Haha, it's amazing.
Set @ orderfield = Replace (@ orderfield, 'desc', 'asc ')
Set @ orderfield = Replace (@ orderfield, 'lai512343975', 'desc ')
Set @ SQL = 'select' + @ selectfields + 'from' + @ tablename + 'where' + @ strwhere + 'ORDER BY' + @ orderfield + 'desc' + ', '+ @ idfield + 'asc'

End
Else
Begin
Set @ orderfield = Replace (@ orderfield, 'desc', 'lai512343975 ')
Set @ orderfield = Replace (@ orderfield, 'asc ', 'desc ')
Set @ orderfield = Replace (@ orderfield, 'lai512343975', 'asc ')
Set @ SQL = 'select' + @ selectfields + 'from' + @ tablename + 'where' + @ strwhere + 'ORDER BY' + @ orderfield + 'asc '+ ', '+ @ idfield + 'asc'
Print (@ SQL)
End
-- Print (@ SQL)
Exec sp_executesql @ SQL
End
Else
Begin
Set @ NRD = @ pagesize * (@ CurrentPage-1)
Print (@ NRD)

Set rowcount @ pagesize
Set @ SQL = 'select' + @ selectfields + 'from' + @ tablename + 'where' + @ strwhere + 'and' + @ idfield + 'not in (select top '+ cast (@ NRD as nvarchar (10 )) + ''+ @ idfield + 'from' + @ tablename + 'where' + @ strwhere + 'ORDER BY' + @ orderfield +'' + @ ordersql + ', '+ @ idfield + 'asc)' + 'ORDER BY' + @ orderfield + ''+ @ ordersql + ',' + @ idfield + 'asc'
Exec sp_executesql @ SQL
-- Print (@ SQL)
End
End
Go