使用SQL Server 2005的新函式構造分頁儲存過程
分頁儲存過程一直是很多人經常用到的東西，怎麼樣才能設計一個既實用，又強大，還可以解決大資料量的分頁儲存過程呢？其實在很多時候設計的度還是要把握的，不至於讓自己陷入【非要把它設計成萬能的分頁儲存過程】的怪圈中才是最重要的，因為我們還要留出時間還解決其他的很多問題，個人認為適度就可以了，留出一定的空間。也因為萬能是不存在的，萬物在一定的範疇之內都是合理的，出了範疇可能就沒有合理的了。
分頁儲存過程大致有下列幾種
1、 利用Not in 和select top
2、 利用id大於多少和select top
3、 利用sql中的遊標
4、臨時表
可以參看網上的以下連結
http://www.zxbc.cn/html/20090625/71918.html
http://read.newbooks.com.cn/info/174545.html
在2005中我們的選擇就多了，可以利用新語法CTE（公用表表示式）,關於CTE的介紹大家可以參看部落格園中一位仁兄的系列教程
http://www.cnblogs.com/nokiaguy/archive/2009/01/31/1381562.html
或者乾脆上微軟的官網
http://msdn.microsoft.com/zh-cn/library/ms190766(SQL.90).aspx
檢視具體內容。
除此之外還可以利用在2005中新增的一些函式，分別是：row_number(),rank,dense_rank,ntile，這些新函式是您可以有效的分析資料以及向查詢餓結果行提供排序值。您可能發現這些新函式有用的典型方案包括：將連續整數分配給結果行，以便進行表示、分頁、計分和繪製直方圖。
詳細介紹參見下列連結
http://blog.csdn.net/htl258/archive/2009/03/20/4006717.aspx
我這裡主要使用的就是row_number()結合新語法CTE，先貼上我的儲存過程。設計，開發，測試儲存過程和相關的C#程式碼就花費我兩天的時間，不過後面的相似介面就很快了，一上午就可以搞兩個分頁顯示的頁面，就算是複雜的查詢，一上午也可以搞定。
下面的儲存過程沒有將總頁數和總條目數返回，如果你有興趣，可以自己加上，可以參看http://www.zxbc.cn/html/20090625/71918.html中的下列部分
Declare @sql nvarchar(4000);
Declare @totalRecord int;
--計算總記錄數
if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--計算總記錄數
--計算總頁數
select @TotalPage=@totalRecord --CEILING((@totalRecord+0.0)/@PageSize)
儲存過程SQL如下，支援不定列，不定條件，多表聯合，排序任意
分頁儲存過程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: shiwenbin
-- MSN: jorden008@hotmail.com
-- Email: jorden008@163.com
-- Create date: 2009-10-20
-- Description: 分頁儲存過程，根據傳遞的引數返回查詢的受訓學員資訊
-- Parameters：
-- =============================================
ALTER PROCEDURE [dbo].[StudentPaging]
-- Add the parameters for the stored procedure here
@StrSelect varchar(max)=null, --欲顯示的列（多列用逗號分開），例如：id,name
@StrFrom varchar(max)= null, --表名稱，或者是表連線字串，多表連線例如:student as s inner join dwinfo as dw on s.dwbh=dw.bh
@StrWhere varchar(max)=null, --查詢條件,''代表沒有條件，單條件或者多條件，多條件例如：name='啊' and id=10
@StrOrder varchar(max) =null, --排序列(多個排序列用逗號分開），例如:id desc,name as
--@PageCount int output, --總頁數
--@ItemCount bigint output, --總記錄數
@PageSize int =null, --每頁顯示條數
@PageIndex int =null --當前頁
--@ClassCode char(10) =null, --單位編號（班級編號）
AS
BEGIN
SET NOCOUNT ON;
declare @SqlQuery varchar(max)
if(@PageIndex=1)
begin
if(@StrWhere is null)--if(@StrWhere='')
set @SqlQuery='select top '+convert(varchar,@PageSize)
+ ' row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+
' from '+@StrFrom;
else
--set @sql='select top @PageSize * from @TableName order by id desc';
--select top @PageSize * from @TableName order by id desc;
set @SqlQuery='select top '+convert(varchar,@PageSize)
+ ' row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom+' where '+@StrWhere;
--exec (@SqlQuery)
-- @SqlQuery
end
else
begin
if(@StrWhere is null)--if(@StrWhere='')
begin
set @SqlQuery='with cte as (
select row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom+'
)
select * from cte where RowNumber between '+ convert(varchar,((@PageIndex-1)*@PageSize)+1)+' and '+
convert(varchar,@PageIndex*@PageSize)
--print @SqlQuery
end
else
begin
set @SqlQuery='with cte as (
select row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom+' where '+@StrWhere+'
)
select * from cte where RowNumber between '+ convert(varchar,((@PageIndex-1)*@PageSize)+1)+' and '+
convert(varchar,@PageIndex*@PageSize)
--print @SqlQuery
end
end
--set @SqlQuery=@SqlQuery+';select @ItemCount =count(*) from '+@TableName
--set @PageCount=@ItemCount/@PageSize
--print '共'+@PageConut+'頁'+@ItemCount+'條'
--print @ItemCount
print @SqlQuery
exec (@SqlQuery)
END
c#相關程式碼的資料庫訪問使用的是微軟的企業庫 V4.1
Enterprise Library 4.1 下載地址：
http://www.microsoft.com/downloads/details.aspx?FamilyId=1643758B-2986-47F7-B529-3E41584B6CE5&displaylang=en
示例程式碼，前臺頁面，前臺為使用者控制元件
| 單位： | 級別：級節點 |
| 該單位共有學員 人 | 每頁顯示 現為第頁 |
示例程式碼，後臺程式碼
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Kimbanx.UCS.ForeignStudentAdmin.Model;
using Kimbanx.UCS.ForeignStudentAdmin.Common;
namespace Kimbanx.UCS.ForeignStudentAdmin.UserControl.UserControl
{
public partial class StudentDetailsTable : System.Web.UI.UserControl
{
private Database _db = DatabaseFactory.CreateDatabase();
private DbCommand _command;
private DbConnection _connection;
private DataSet _ds;
private string _classCode;
private string _classFullName;
private string _studentType;
private string _studentCount;
private string _queryStringWhere;
private DataTable _studentTable;
protected string SetBirthDate(object obj)
{
string result = string.Empty;
string temp = obj.ToString();
result = DateTime.Parse(temp).ToShortDateString();
return result;
}
protected string SetEnrollDate(object obj)
{
string result = string.Empty;
string temp = obj.ToString();
result = DateTime.Parse(temp).ToShortDateString();
return result;
}
protected void Filldata_dllPageSize()
{
for (int i = 1; i {
ddlPageSize.Items.Add(i.ToString());
}
ddlPageSize.SelectedIndex = 14;
}
protected void InitSession()
{
//Session["PageSize"] = 0;
Session["PageIndex"] = 1;
Session["PageCount"] = int.Parse(_studentCount) / 15 + 1;
}
///
/// 獲取QueryString傳遞引數
///
protected void GetQueryStringPara()
{
_classCode = Request.QueryString["dwbh"];
_classFullName =HttpUtility.UrlDecode( Request.QueryString["dwmc"]);
_studentCount = Request.QueryString["studentCount"];
_studentType =HttpUtility.UrlDecode( Request.QueryString["studentType"]);
_queryStringWhere = Request.QueryString["where"];
}
protected void SetLabelText()
{
this.lblClassName.Text = _classFullName;
this.lblClassLevel.Text = GetClassInfo(_classCode).Level.ToString();
this.lblStudentCount.Text = _studentCount;
this.lblStudentType.Text = _studentType;
}
#region
/////
///// 獲取學員資料
/////
///// 顯示的欄位
///// 用到的
/////查詢條件
///// 每頁顯示條數
///// 當前頁
/////
//protected DataTable GetStudentData(string strSelect,string strFrom,string strWhere,int pageSize,int pageIndex)
//{
// _command = _db.GetStoredProcCommand("StudentPaging");
// _db.AddInParameter(_command, "StrSelect", DbType.String, "zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh");
// _db.AddInParameter(_command, "StrFrom", DbType.String, "tx_xyzl");
// _db.AddInParameter(_command, "StrWhere", DbType.String, strWhere );
// _db.AddInParameter(_command, "StrOrder", DbType.String, "id");
// _db.AddInParameter(_command, "PageSize", DbType.Int32, pageSize );
// _db.AddInParameter(_command, "PageIndex", DbType.Int32,pageIndex );
// _studentTable = _db.ExecuteDataSet(_command).Tables[0];
// return _studentTable;
//}
#endregion
protected string GetStudentImageUrl(object imageUrl)
{
string serverUrl = http://192.168.0.1/admin;
string imageurl = string.Empty;
if (!(imageUrl == null))
{
string temp = imageUrl.ToString().Trim();
if (!string.IsNullOrEmpty(temp))
{ imageurl = string.Format("{0}{1}", serverUrl, temp.Substring(temp.IndexOf("/"))); }
}
return imageurl;
}
///
/// 繫結分頁之後的資料
///
/// 每頁顯示的資料量
/// 當前頁
protected void BindStudentData(int pageSize, int pageIndex)
{
switch (_queryStringWhere)
{
case "jx":
this.gvStudent.DataSource = Helper.StudentPagingResult(
"zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh",
"student",
string.Format("dwbh='{0}' and jx='{1}'", _classCode, _studentType),
"id",
pageSize,
pageIndex);
this.gvStudent.DataBind();
break;
case "gj":
this.gvStudent.DataSource = Helper.StudentPagingResult(
"zpadress,xmjz,xmjy,jx,zw,gj,sjyqk,zj,csrq,rwrq,xzz,dhd,dhx,fcjp,hzh,xh",
"student",
string.Format("dwbh='{0}' and gj='{1}'", _classCode, _studentType),
"id",
pageSize,
pageIndex);
this.gvStudent.DataBind();
break;
case "allyear":
this.gvStudent.DataSource = Helper.StudentPagingResult(
"s.zpadress,s.xmjz,s.xmjy,s.jx,s.zw,s.gj,s.sjyqk,s.zj,s.csrq,s.rwrq,s.xzz,s.dhd,s.dhx,s.fcjp,s.hzh,s.xh",
"student as s inner join class as dw on s.dwbh=dw.bh",
string.Format(@"s.dwbh='{0}' and (dw.kxsj>=convert(datetime,'{1}'+'-01-01',120) and
dw.kxsj<=convert(datetime,'{1}'+'-12-31',120) or dw.bysj>=convert(datetime,'{1}'+'-01-01',120) and
dw.bysj<=convert(datetime,'{1}'+'-12-31',120)) ", _classCode, _studentType),
"s.id",
pageSize,
pageIndex);
this.gvStudent.DataBind();
break;
case "new":
this.gvStudent.DataSource = Helper.StudentPagingResult(
"s.zpadress,s.xmjz,s.xmjy,s.jx,s.zw,s.gj,s.sjyqk,s.zj,s.csrq,s.rwrq,s.xzz,s.dhd,s.dhx,s.fcjp,s.hzh,s.xh",
"student as s inner join class as dw on s.dwbh=dw.bh",
string.Format(@"s.dwbh='{0}' and (dw.kxsj>=convert(datetime,'{1}'+'-01-01',120) and
dw.kxsj<=convert(datetime,'{1}'+'-12-31',120)) ", _classCode, _studentType),
"s.id",
pageSize,
pageIndex);
this.gvStudent.DataBind();
break;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (UserAuthHelper.GetUserAuthType("1") == UserAuthEnum.Admin||
UserAuthHelper.GetUserAuthType("2") == UserAuthEnum.CurrentStudentDetails)
{
GetQueryStringPara();
SetLabelText();
if (GetStudentCount() == 0)
{
StudentCountZero();
return;
}
if (!IsPostBack)
{
Filldata_dllPageSize();
SetPageIndex(1);
SetPageCount();
BindStudentData(GetPageSize(), GetPageIndex());
}
else
{
}
}
else
{
this.Controls.Add(new LiteralControl("您沒有相應的許可權，請聯絡管理員"));
}
}
///
/// 獲取班級資訊，班級全稱，班級級別
///
/// 班級編號
///
protected ClassEntity GetClassInfo(string classCode)
{
ClassEntity entity = new ClassEntity();
entity.Code = classCode;
_command = _db.GetStoredProcCommand("ClassInfo");
_db.AddInParameter(_command, "bh", DbType.String, classCode);
using (IDataReader reader = _db.ExecuteReader(_command))
{
while (reader.Read())
{
entity.FullName = reader.GetString(1);
entity.Level = reader.GetInt32(2);
}
}
return entity;
}
#region Get and Set PageSize
protected int GetPageSize()
{
return int.Parse(ddlPageSize.SelectedValue);
}
protected void SetPageSize(int pageSize)
{
this.ddlPageSize.Text = pageSize.ToString();
}
#endregion
#region Get and Set PageIndex
protected int GetPageIndex()
{
return int.Parse(this.lblPageIndex.Text.Trim());
}
protected void SetPageIndex(int pageIndex)
{
this.lblPageIndex.Text = pageIndex.ToString();
}
#endregion
#region Get and Set PageCount
protected int GetPageCount()
{
return int.Parse(this.lblPageCount.Text.Trim());
}
protected void SetPageCount()
{
int studentCount = GetStudentCount();
int pageSize = GetPageSize();
if (studentCount % pageSize == 0)
{
this.lblPageCount.Text = (studentCount / pageSize).ToString();
}
else
{
this.lblPageCount.Text = (studentCount / pageSize + 1).ToString();
}
}
#endregion
#region Get and Set StudentCount
protected int GetStudentCount()
{
return int.Parse(this.lblStudentCount.Text.Trim());
}
protected void SetStudentCount(int studentCount)
{
this.lblStudentCount.Text = studentCount.ToString();
}
#endregion
protected void StudentCountZero()
{
this.lblPageIndex.Text = "0";
this.lblPageCount.Text = "0";
}
protected void LinkButton_Command(object sender, CommandEventArgs e)
{
if (GetStudentCount() == 0)
{
StudentCountZero();
return;
}
int pageCount = GetPageCount();
int pageIndex = GetPageIndex();
int pageSize = GetPageSize();
switch (e.CommandArgument.ToString())
{
case "first":
if (pageIndex == 1) { }
else
{
pageIndex = 1;
SetPageIndex(pageIndex);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
break;
case "next":
if (pageCount == pageIndex & pageIndex == 1)
{ }
else if (pageIndex == 1 && pageCount > pageIndex)
{
SetPageIndex(++pageIndex);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
else if (pageIndex > 1 && pageCount == pageIndex)
{ }
else
{
SetPageIndex(++pageIndex);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
break;
case "prev":
if (pageIndex == 1)
{ }
else if (pageIndex == pageCount && pageIndex > 1)
{
SetPageIndex(--pageIndex);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
else if (pageIndex == 2)
{
SetPageIndex(1);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
else
{
SetPageIndex(--pageIndex);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
break;
case "last":
if (pageCount == pageIndex)
{ }
else
{
SetPageIndex(pageCount);
pageIndex = GetPageIndex();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
break;
default:
SetPageIndex(1);
pageSize = GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
break;
}
}
protected void ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
{
int pageIndex = GetPageIndex();
int pageCount = GetPageCount();
int pageSize = GetPageSize();
pageIndex = 1;
SetPageIndex(pageIndex);
SetPageSize(int.Parse(((DropDownList)sender).SelectedValue));
pageSize=GetPageSize();
SetPageCount();
BindStudentData(pageSize, pageIndex);
}
}
}
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-617448/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL server儲存過程函式SQLServer儲存過程函式
- SQL Server 2005的複製儲存過程選項BYSQLServer儲存過程
- SQL 分頁儲存過程SQL儲存過程
- Q&A：在SQL Server 2005中編寫儲存過程RVSQLServer儲存過程
- Sql儲存過程分頁--臨時表儲存SQL儲存過程
- 儲存過程與儲存函式儲存過程儲存函式
- SQL Server儲存過程的優缺點SQLServer儲存過程
- SQL SERVER儲存過程AS和GO的含義SQLServer儲存過程Go
- 【SQL Server】常見系統儲存過程SQLServer儲存過程
- SqlServer-儲存過程分頁SQLServer儲存過程
- mysql和orcale的儲存過程和儲存函式MySql儲存過程儲存函式
- MySQL儲存過程 (即函式)MySql儲存過程函式
- mySql 儲存過程與函式MySql儲存過程函式
- MySQL 儲存過程和函式MySql儲存過程函式
- MySQL儲存過程和函式MySql儲存過程函式
- Oracle 儲存過程分頁 + Sqlsugar呼叫Oracle儲存過程SqlSugar
- Ms Sql Server查詢儲存過程中的內容SQLServer儲存過程
- SQL Server 2005詳細安裝過程及配置SQLServer
- openGauss 函式及儲存過程支援函式儲存過程
- 儲存過程vs.函式QM儲存過程函式
- 造數儲存過程儲存過程
- 使用SQL SERVER儲存過程實現歷史資料遷移SQLServer儲存過程
- SQL Server 資料訪問策略：儲存過程QCSQLServer儲存過程
- SQL Server實戰六：T-SQL、遊標、儲存過程的操作SQLServer儲存過程
- mysql儲存過程procedure、函式function的用法MySql儲存過程函式Function
- 儲存過程_造使用者資料儲存過程
- MySQL入門--儲存過程(PROCEDURE)和儲存函式(FUNCTION)MySql儲存過程儲存函式Function
- SQL 儲存過程裡呼叫另一個儲存過程SQL儲存過程
- SQL---------儲存函式SQL儲存函式
- MySQL自定義函式與儲存過程MySql函式儲存過程
- 函式儲存過程併發控制-案例函式儲存過程
- 七、函式-儲存過程-觸發器函式儲存過程觸發器
- SQL Server實戰五：儲存過程與觸發器SQLServer儲存過程觸發器
- MySQL儲存過程裡動態SQL的使用UXMySql儲存過程UX
- mysql儲存過程及日期函式實踐MySql儲存過程函式
- 瞭解使用mysql 的檢視、儲存過程、觸發器、函式....MySql儲存過程觸發器函式
- SQL Server的巢狀儲存過程中使用同名的臨時表怪像淺析SQLServer巢狀儲存過程
- EF中使用SQL語句或儲存過程SQL儲存過程
- 關於SQL server2008除錯儲存過程的完整步驟SQLServer除錯儲存過程