SQL Server Stored Procedure page (sorted by multiple conditions)

Cs page call code:
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
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