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

其实在很多时候设计的度还是要把握的，不至于让自己陷入【非要把它设计成万能的分页存储过程】的怪圈中才是最重要的，因为我们还要留出时间还解决其他的很多问题，个人认为适度就可以了，留出一定的空间。也因为万能是不存在的，万物在一定的范畴之内都是合理的，出了范畴可能就没有合理的了。

1、Not in select top

2、idselect top

3、sql

C#中常用的分页存储过程小结
http://read.newbooks.com.cn/info/174545.html

2005CTE,CTE

http://www.cnblogs.com/nokiaguy/archive/2009/01/31/1381562.html

2005row_number(),rank,dense_rank,ntile

http://blog.csdn.net/htl258/archive/2009/03/20/4006717.aspx

row_number()CTE

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
--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,@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,@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,'+@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,@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 下载地址：

　　http://www.microsoft.com/downloads/details.aspx?FamilyId=1643758B-2986-47F7-B529-3E41584B6CE5&displaylang=en

示例代码，前台页面，前台为用户控件
代码如下:<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="StudentDetailsTable.ascx.cs" Inherits="Kimbanx.UCS.ForeignStudentAdmin.UserControl.UserControl.StudentDetailsTable" %>

人人共页
现为第页
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;
}
///