分页存储过程（一）使用sql2005的新函数构造分页存储过程

分页存储过程一直是很多人经常用到的东西，怎么样才能设计一个既实用，又强大，还可以解决大数据量的分页存储过程呢？

Not in select top

idselect top

sql

4

2005CTE,CTE

2005row_number(),rank,dense_rank,ntile

row_number()CTE

存储过程SQL如下，支持不定列，不定条件，多表联合，排序任意
代码如下:

set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
go 
--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) 
-- ============================================= 
-- Author: shiwenbin 
-- MSN:　　　　jorden008@hotmail.com 
-- Email:　　 jorden008@163.com 
-- Create date: 2009-10-20 
-- Description: 分页存储过程，根据传递的参数返回分页的结果 
-- Parameters： 
-- ============================================= 
ALTER PROCEDURE [dbo].[Proc_GetDataPaged] 
-- 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 =50, --每页显示条数 
@BeginIndex int=1,--记录开始数 
@DoCount bit =0 --是否统计总数，为0不统计，为1统计 
-- @PageIndex int =1 --当前页 
--@ClassCode char(10) =null, --单位编号（班级编号） 
AS 
BEGIN 
SET NOCOUNT ON; 
Declare @sql nvarchar(4000); 
Declare @totalRecord int; 
--计算总记录数 
if (@StrWhere ='''' or @StrWhere='' or @StrWhere is NULL) 
set @sql = 'select @totalRecord = count(*) from ' + @StrFrom 
else 
set @sql = 'select @totalRecord = count(*) from ' + @StrFrom + ' where ' + @StrWhere 
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@ItemCount OUTPUT--计算总记录数 
declare @SqlQuery varchar(max) 
-- if(@PageIndex=1) 
if(@BeginIndex=1 or @BeginIndex=0 or @BeginIndex <0) 
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) 
convert(varchar,@BeginIndex)+' and '+ 
convert(varchar,@BeginIndex+@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) 
convert(varchar,@BeginIndex)+' and '+ 
convert(varchar,@BeginIndex+@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 下载地址：

示例代码，前台页面，前台为用户控件
 代码如下:

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="StudentDetailsTable.ascx.cs" Inherits="Kimbanx.UCS.ForeignStudentAdmin.UserControl.UserControl.StudentDetailsTable" %> 
 









 
 
 单位：
 
级别：级节点
 
 
 
该单位共有学员 
人
 
每页显示onselectedindexchanged="ddlPageSize_SelectedIndexChanged"> 
人  共页 
  现为第页   
oncommand="LinkButton_Command">首页   
oncommand="LinkButton_Command">下一页   
oncommand="LinkButton_Command">上一页   
oncommand="LinkButton_Command">末页 
 
 
 

 
EmptyDataText="没有符合条件的数据"> 
 
 
 
 runat="server" /> 
 
 
 
 
'> 

 
'> 
 
 
 
 
 
 
 
 
 
'> 

 
'> 
 
 
 
 
 
'> 

 
'> 
 
 
 
 
 
 
 
示例代码，后台代码 
 代码如下:

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 < 100; 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; 
/