存儲過程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER 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 +' desc'
--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')
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,regdate desc'
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 +' desc) ' + ' order by '+ @OrderField + ' ' +@OrderSql+','+@IdField +' desc'
exec sp_executeSql @Sql
--Print(@sql)
End
end

?/// <summary>
??????? /// 分頁數據庫操作函數
??????? /// </summary>
??????? /// <param name="TotalPage">總頁數</param>
??????? /// <param name="RowsCount">總條數</param>
??????? /// <param name="PageSize">每頁多少數據</param>
??????? /// <param name="CurrentPage">當前頁</param>
??????? /// <param name="SelectFields">返回的列</param>
??????? /// <param name="IdField">主鍵</param>
??????? /// <param name="OrderField">排序字段,如果是多個字段，除最后一個字段外，后面都要加排序條件（asc/desc），不包含order by,最后一個排序字段不用加排序條件 </param>
??????? /// <param name="OrderType">1升序，0降序</param>
??????? /// <param name="TableName">表名</param>
??????? /// <param name="strWhere">條件</param>
??????? /// <returns></returns>
??????? public static DataTable GetDataTableDuoDuo(int TotalPage, int RowsCount, int PageSize, int CurrentPage, string SelectFields, string IdField, string OrderField, string OrderType, string TableName, string strWhere)
??????? {
??????????? SqlParameter[] param = new SqlParameter[]
??????????? {
??????????????? new SqlParameter("@TotalPage", TotalPage),
??????????????? new SqlParameter("@RowsCount", RowsCount),
??????????????? new SqlParameter("@PageSize", PageSize),
??????????????? new SqlParameter("@CurrentPage", CurrentPage),?
??????????????? new SqlParameter("@SelectFields", SelectFields),
??????????????? new SqlParameter("@IdField", IdField),???
??????????????? new SqlParameter("@OrderField", OrderField),???
??????????????? new SqlParameter("@OrderType", OrderType),
??????????????? new SqlParameter("@TableName",TableName),
??????????????? new SqlParameter("@strWhere",strWhere)
??????????? };
??????????? return SqlHelper.ExecuteTable(CommandType.StoredProcedure, "getRecordByPage", param);
??????? }

前臺頁面 分頁字符串

?/// <summary>
??????? /// 獲取分頁字符串
??????? /// </summary>
??????? /// <param name="page">當前頁碼</param>
??????? /// <param name="pageSize">頁面大小</param>
??????? /// <param name="Url">分頁鏈接</param>
??????? /// <param name="strWhere">查詢條件</param>
??????? /// <param name="tblName">數據表</param>
??????? ///? <param name="pageType">頁面類型（文件夾名字）</param>
??????? /// <param name="typeId">類別id</param>
??????? /// <returns></returns>
??????? public static string GoToPager(int page, int pageSize, string Url, string strWhere, string tblName, string pageType, int typeId)
??????? {
??????????? if (tblName == null) goto Err;

??????????? DataTable tb = GetDataTable(strWhere, tblName);
??????????? int Count = Convert.ToInt32(tb.Rows[0]["Total"]);? //取得總的記錄數

??????????? StringBuilder strHtml = new StringBuilder();
??????????? int prevPage = page - 1;??? //上一頁
??????????? int nextPage = page + 1;??? //下一頁
??????????? int startPage;
??????????? int pageCount = (int)Math.Ceiling((double)Count / pageSize);??? //總頁數
??????????? if (pageCount <= 1)
??????????? {
??????????????? return "";
??????????? }

??????????? strHtml.Append("<div class=\"pg mar_top\">");
??????????? strHtml.Append("<p class=\"page\">");
??????????? if (prevPage < 1)
??????????? {
??????????????? strHtml.Append("<span class=\"page1\">首頁</span>");
??????????????? strHtml.Append("<span class=\"page1\">上一頁</span>");
??????????? }
??????????? else
??????????? {
??????????????? strHtml.Append("<span class=\"page1\"><a title=\"\" href=\"" + Url + "1" + Base.BasePage.Config.Rewrite + "\">首頁</a></span>");
??????????????? strHtml.Append("<span class=\"page1\"><a title=\"\" href=\"" + Url + prevPage + Base.BasePage.Config.Rewrite + "\">上一頁</a></span>");
??????????? }
??????????? if (page % 6 == 0)
??????????? {
??????????????? startPage = page - 5;
??????????? }
??????????? else
??????????? {
??????????????? startPage = page - page % 6 + 1;
??????????? }
??????????? if (startPage > 6)
??????????? {
??????????????? strHtml.Append(@"<span><a href='");
??????????????? strHtml.Append(Url);
??????????????? strHtml.Append(startPage - 1 + Base.BasePage.Config.Rewrite);
??????????????? strHtml.Append(@"'>...</a></span>");
??????????? }
??????????? for (int i = startPage; i < startPage + 6; i++)
??????????? {
??????????????? if (i > pageCount) break;
??????????????? if (i == page)
??????????????? {
??????????????????? //strHtml.Append("<span class=\"page2\">" + i + "</span>");
??????????????? }
??????????????? else
??????????????? {
??????????????????? //strHtml.Append("<span class=\"page1\"> <a href=" + Url + i + Base.BasePage.Config.Rewrite + ">" + i + "</a> </span>");
??????????????? }
??????????? }
??????????? //if (pageCount >= startPage + 6) strHtml.Append(@"<span><a href='" + Url + (startPage + 6) + Base.BasePage.Config.Rewrite + "'>...</a></span>");
??????????? if (nextPage > pageCount)
??????????? {
??????????????? strHtml.Append("<span class=\"page1\">下一頁</span>");
??????????????? strHtml.Append("<span class=\"page1\">末頁</span>");
??????????? }
??????????? else
??????????? {
??????????????? strHtml.Append("<span class=\"page1\"><a href='" + Url + nextPage + Base.BasePage.Config.Rewrite + "'>下一頁</a></span>");
??????????????? strHtml.Append("<span class=\"page1\"><a href='" + Url + pageCount + Base.BasePage.Config.Rewrite + "'>末頁</a></span>");
??????????? }

??????????? strHtml.Append("當前頁：<font class=\"f_c00\">" + page + "</font>/" + pageCount + "頁 共<font class=\"f_c00\">" + Count + "</font>條");
??????????? strHtml.Append(" 轉到<SELECT id=select1 LANGUAGE=javascript onchange=\"location.href=this.value\">");
??????????? strHtml.Append("<OPTION>請選擇</OPTION>");
??????????? for (int i = 1; i <= pageCount; i++)
??????????? {
??????????????? strHtml.Append("<OPTION value=\"" + Base.BasePage.Config.WebURL + "" + pageType + "/" +Url+ i + ".aspx\">第" + i + "頁</OPTION>");
??????????? }
??????????? strHtml.Append("</SELECT>");
??????????? strHtml.Append("</p>");
??????????? strHtml.Append("</div>");

??????????? return strHtml.ToString();

??????? Err:
??????????? return "缺少數據表或視圖";
??????? }

分頁存儲過程