SQL Server stored procedure paging (sorted by multiple criteria) _mssql

CS Page calling code:

Copy Code code 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 Code code 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