cs頁面調用代碼：

?

?

?　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);???????????
??????? }
??? }

//調用存儲過程的函數

? 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頁面代碼：

<table id="SXFSTable" style="width:100%;" class="table">
??????? <tr><td><b>公司名稱</b></td><td><b>公司網址</b></td><td><b>認領狀態</b></td></tr>
??????? <%for (int i = 0; i < dt.Rows.Count; i++)
????????? {
??????? %>
??????? <tr>
??????????? <td><%= dt.Rows[i]["companyName"].ToString() %>排序值：<%= dt.Rows[i]["ordernum"].ToString() %></td>
??????????? <td><%= dt.Rows[i]["webSite"].ToString() %>
??????????? 是否認領：<%=dt.Rows [i]["userid"].ToString () %></td>
??????????? <td><%= dt.Rows[i]["isRL"].ToString().Equals("0") ? "<a href=\"javascript:;\" οnclick=\"renLing(event,'"+dt.Rows[i]["RLId"].ToString()+"');\">認領該企業</a>" : "<font color=\"red\">該企業已被認領</font>"%></td>
??????? </tr>
??????? <%??????????
??????? }
??????? %>
??? </table>
??? </div>
??? <div style="margin-left:auto; margin-right:auto; width:70%; text-align:left; font-size:9pt;">
??????????? 第 <%=PageCurrent %> 頁? 共 <%=RowsCount %> 條? 共 <%=TotalPage%> 頁
???????? <% if (PageCurrent != 1)
??????????? {
??????????? %>
??????????????? <a href="test.aspx">首　頁</a>
??????????????? <a href="test.aspx?page=<%=PageCurrent-1 %>">上一頁</a>???
???????????????????????
??????????? <%
??????????? }
??????????? if (PageCurrent != TotalPage)
??????????? {
??????????? %>
??????????????? <a href="test.aspx?page=<%=PageCurrent+1 %>">下一頁</a>
??????????????? <a href="test.aspx?page=<%=TotalPage%>">末　頁</a>?????????????????
??????????? <%???????????????????
??????????? }
??????????? %>
??????????? </div>

?

?

存儲過程代碼：

CREATE?? proc [dbo].[getRecordByPage]
@TotalPage int output,--總頁數
@RowsCount int output,--總條數
@PageSize int,--每頁多少數據
@CurrentPage int,--當前頁數
@SelectFields? nvarchar(1000),--select 語句但是不包含select
@IdField nvarchar(50),--主鍵列
@OrderField nvarchar(50),--排序字段,如果是多個字段，除最后一個字段外，后面都要加排序條件（asc/desc），不包含order by,最后一個排序字段不用加排序條件
@OrderType nvarchar(4),--1升序，0降序
@TableName nvarchar(200),--表名
@strWhere nvarchar(300)--條件
As
Begin
???? declare @RecordCount float
???? declare @PageNum int --分頁依據數
???? Declare @Compare nvarchar(50)--比較字段區分min或者max
???? Declare @Compare1 nvarchar(2) --大于號“>” 或者小于號"<“
???? Declare @OrderSql nvarchar(10)--排序字段
???? 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')//這里用變量將asc和desc互換，哈哈，太神了
???? 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

轉載于:https://www.cnblogs.com/jxcia_Lai/archive/2010/06/12/1757339.html

總結

以上是生活随笔為你收集整理的存储过程分页，按多条件排序(原创)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。