SQL Server Stored Procedure page (sorted by multiple conditions)

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.