SQL Server stored procedures paging (sorted by multiple criteria)

Source: Internet
Author: User
Tags copy count end sql string sort table name tostring
CS Page calling 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 of Totalpage, out Rowscount, PageSize, pagecurrent);
}
}


Functions that call stored procedures

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 a";
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><td><b> Company website </b></td><td> <b> Claim Status </b></td></tr>
<%for (int i = 0; i < dt.) Rows.Count; i++)
{
%>
<tr>
<td><%= dt. rows[i]["CompanyName"]. ToString ()%> sorted value: <%= dt. rows[i]["Ordernum"]. ToString ()%></td>
<td><%= dt. rows[i]["WebSite"]. ToString ()%>
Whether to 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\" > The 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; " >
<%=pagecurrent%> page <%=rowscount%> Total <%=TotalPage%> page
<% if (pagecurrent!= 1)
{
%>
<a href= "test.aspx" > Home </a>
<a href= "Test.aspx?page=<%=pagecurrent-1%>" > Prev </a>

<%
}
if (pagecurrent!= totalpage)
{
%>
<a href= "Test.aspx?page=<%=pagecurrent+1%>" > next page </a>
<a href= "test.aspx?page=<%=totalpage%>" > Last </a>
<%
}
%>
</div>


Stored Procedure Code:
Copy CodeThe code is as follows:
CREATE proc [dbo]. [Getrecordbypage]
@TotalPage int output,--Total pages
Total number of @RowsCount int output,--
@PageSize int,--How much data per page
@CurrentPage int,--Current page
@SelectFields nvarchar (1000),--SELECT statement but does not contain a select
@IdField nvarchar,--primary key columns
@OrderField nvarchar,--sort field, if multiple fields, except the last field, followed by a sort condition (ASC/DESC), no order by, last sorted field without sorting criteria
@OrderType nvarchar (4),--1 ascending, 0 descending
@TableName nvarchar,--table name
@strWhere nvarchar (300)--condition
As
Begin
DECLARE @RecordCount float
DECLARE @PageNum INT--Number of paging basis
Declare @Compare nvarchar (50)--comparison field distinguish min or max
Declare @Compare1 nvarchar (2)--Greater than sign ">" or less than sign "<"
Declare @OrderSql nvarchar (10)--Sort fields
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 ')//here with the variable will ASC and DESC interchange, haha, too god
Set @OrderField =replace (@OrderField, ' desc ', ' ASC ')
Set @OrderField =replace (@OrderField, ' lai512343975 ', ' desc ')
Set @Sql = ' SELECT ' + @SelectFields + ' from ' + @TableName + ' where ' + @strWhere + ' ORDER BY ' + @OrderField + ' desc ' + ', ' + @IdFi Eld + ' 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 ' + ', ' + @IdF ield+ ' 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) + ' + @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.