一个通用的数据分页的存储过程

来源：互联网发布：linux memset 头文件编辑：程序博客网时间：2024/05/19 17:25

CREATE PROCEDURE sp_page
@tb         varchar(50), --表名
@col        varchar(50), --按该列来进行分页
@coltype    int,         --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby    bit,         --排序,0-顺序,1-倒序
@collist    varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize   int,         --每页记录数
@page       int,         --指定页
@condition varchar(800),--查询条件
@pages      int OUTPUT   --总页数
AS
/*
功能描述：对指定表中满足条件的记录按指定列进行分页查询，分页可以顺序、倒序
         查询可以指定页大小、指定查询任意页、指定输出字段列表，返回总页数
作    者：pbsql
版    本：1.10
最后修改：2004-11-29
*/
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
         ') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
           @col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
    @where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO

详情见：
http://blog.csdn.net/pbsql/archive/2004/11/30/199657.aspx

http://community.csdn.net/Expert/topic/3587/3587201.xml?temp=.4292414

然后在新建一个aspx页面，代码如下：
<%@ Page language="c#" Codebehind="custompage.aspx.cs" AutoEventWireup="false" Inherits="cyc_test.custompage" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>custompage</title>
<meta content="Microsoft Visual Studio 7.0" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
<style>A { BEHAVIOR: url(MouseOver.htc) }
HR { COLOR: black; HEIGHT: 2px }
.StdText { FONT-WEIGHT: bold; FONT-SIZE: 9pt; FONT-FAMILY: verdana }
.StdTextBox { BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; FONT-SIZE: 9pt; FILTER: progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true'); BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; FONT-FAMILY: verdana }
.Shadow { FILTER: progid:DXImageTransform.Microsoft.dropshadow(OffX=2, OffY=2, Color='gray', Positive='true') }
</style>
</HEAD>
<body style="FONT-FAMILY: arial" bgColor="ivory" MS_POSITIONING="GridLayout">
<form id="custompage" method="post" runat="server">
<h2>项目总结之datagrid自定义分页篇
</h2>
<asp:label id="Label1" runat="server" font-bold="true" cssclass="StdText">当前路径: </asp:label><asp:label id="lblURL" style="COLOR: blue" runat="server" cssclass="StdText"></asp:label>
<br>
<asp:label id="Label2" runat="server" cssclass="stdtext" Text="查询语句:"></asp:label>
<asp:textbox id="Textbox1" runat="server" cssclass="stdtextbox" text="SELECT employeeid, firstname, lastname,title FROM Employees where employeeid>0 order by employeeid" width="765px" Enabled="false"></asp:textbox>
<hr>

<asp:datagrid id="grid" runat="server" OnPageIndexChanged="PageIndexChanged" AllowCustomPaging="True" AllowPaging="True" PageSize="5" BorderWidth="1" BorderColor="black" BorderStyle="solid" BackColor="White" CssClass="Shadow" GridLines="vertical" CellSpacing="0" CellPadding="2" Font-Names="Verdana" Font-Size="Smaller">
<PagerStyle Font-Bold="true" Mode="NumericPages" BackColor="palegreen" />
<AlternatingItemStyle BackColor="#eeeeee" />
<ItemStyle BackColor="White" />
<HeaderStyle Font-Bold="True" ForeColor="White" BackColor="Navy" />
</asp:datagrid></form>
</body>
</HTML>

再写后台的源代码：
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace cyc_test
{
/// <summary>
/// custompage 的摘要说明。
/// </summary>
public class custompage : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Label lblURL;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.TextBox Textbox1;
protected System.Web.UI.WebControls.DataGrid grid;

private void Page_Load(object sender, System.EventArgs e)
{
// 第一次加载页时,初始化
if (!Page.IsPostBack)
{
lblURL.Text = Request.Url + "<hr>";
createdatasource(1);
}
}

//绑定datagrid的函数
protected void createdatasource(int current_page)
{
string str_table_name,str_column_list,str_order_column,str_condition;
//查询的表名
str_table_name="employees";
//返回的列名列表
str_column_list="employeeid,firstname,lastname,title";
//排序列的列名
str_order_column="employeeid";
//查询的表件
str_condition="employeeid>0";
string strConn = "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;";
SqlConnection conn = new SqlConnection(strConn);
//声明执行存储过程的SqlCommand
SqlCommand scd_sel=new SqlCommand("up_custompage",conn);
scd_sel.CommandType=CommandType.StoredProcedure;
//给存储过程的参数赋值
SqlParameter sp_temp;
sp_temp=scd_sel.Parameters.Add("@vc_order_column_name",SqlDbType.VarChar,100);
sp_temp.Direction=ParameterDirection.Input;
sp_temp.Value=str_order_column;
sp_temp=scd_sel.Parameters.Add("@vc_select_column_list",SqlDbType.VarChar,100);
sp_temp.Direction=ParameterDirection.Input;
sp_temp.Value=str_column_list;
sp_temp=scd_sel.Parameters.Add("@vc_select_table_list",SqlDbType.VarChar,100);
sp_temp.Direction=ParameterDirection.Input;
sp_temp.Value=str_table_name;
sp_temp=scd_sel.Parameters.Add("@vc_condition",SqlDbType.VarChar,100);
sp_temp.Direction=ParameterDirection.Input;
sp_temp.Value=str_condition;
sp_temp=scd_sel.Parameters.Add("@page_size",SqlDbType.Int);
sp_temp.Direction=ParameterDirection.Input;
sp_temp.Value=grid.PageSize;
sp_temp=scd_sel.Parameters.Add("@current_page",SqlDbType.Int);
sp_temp.Direction=ParameterDirection.Input;
sp_temp.Value=current_page;
sp_temp=scd_sel.Parameters.Add("@total1",SqlDbType.Int);
sp_temp.Direction=ParameterDirection.Output;

//执行存储过程
SqlDataAdapter sda=new SqlDataAdapter();
sda.SelectCommand=scd_sel;
if (conn.State==ConnectionState.Closed)
conn.Open();
DataSet ds=new DataSet();
sda.Fill(ds,"tb1");
conn.Close();
//设置VirtualItemCount属性
grid.VirtualItemCount=(int)scd_sel.Parameters["@total1"].Value;
//绑定数据源
grid.DataSource=ds.Tables["tb1"].DefaultView;
grid.DataBind();
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN：该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.grid.ItemCreated += new System.Web.UI.WebControls.DataGridItemEventHandler(this.grid_ItemCreated);
this.grid.PageIndexChanged += new System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.PageIndexChanged);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion
//datagrid的ItemCreated事件，用于定制分页导航行
private void grid_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{

ListItemType elemType = e.Item.ItemType;
//定制分页导航行,样式为[1] [2] 第 3 页 [4]
if (elemType == ListItemType.Pager)
{

TableCell pager = (TableCell) e.Item.Controls[0];

for (int i=0; i<pager.Controls.Count; i+=2)
{
Object o = pager.Controls[i];
if (o is LinkButton)
{
LinkButton h = (LinkButton) o;
h.Text = "[ " + h.Text + " ]";
}
else
{
Label l = (Label) o;
l.Text = "第" + l.Text + “页”;
}
}
}
}
//页选中（分页）事件
public void PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
grid.CurrentPageIndex = e.NewPageIndex;
//页码值是从零开始的，所以要加一
createdatasource(grid.CurrentPageIndex+1);
}
}
}

要运行上面的示例，请按上面的顺序先创建存储过程，再写页面的代码，最后是源代码。示例参考了《构建Web解决方案---应用ASP.NET和ADO.NET》中的分页思路。在本机调试通过。大家只要明白存储过程分页的思想就可以了。如果大家有更好的方案，请提出来，也许你的更好！
这一段时间又开始了新的项目，所以这篇文章写得比较仓促，可能有些地方讲得不明白，大家可以提问和讨论。希望对大家有所帮助。

私奔

在众多分析性能的文章中,很庆幸翻到了这一篇http://www.codeproject.com/aspnet/PagingLarge.asp(感谢园子里一位朋友的提示,具体名字已经不记得了),对分页存储过程有兴趣的朋友可以去看,写的很详尽,随便PF一下国外同行的认真和专业.

在上面提到的那篇文章的一开始大力推崇使用RowCoun的t方法,
可是由于原文中提供的方法不支持非unique字段的排序
大多场合都并不适用
文章末尾连作者自己提到,已经开始考虑改用cursor方法
可我对RowCount方法算是情有独中,于是对原文中该方法进行了改进
改进后的方法已基本上满足我的需要,现在发布出来,有用的朋友可以下载试用

修改记录:
    1)增加对非unique字段排序的支持,但必须设定一个PK字段(注:只要是unique字段都可以作为pk字段)
    2)增加记录总数输出参数
    3)修改原过程若干BUG

注:
     1)如表名参数为多表连接时,sort列必须指定表名;
     2)只支持单字段排序,有朋友如果问为什么不做成可以多字段排序的,理论上确实有这种可能性,但需要以一定的效率损失为代价,而且会使方法过于复杂,如真有这种需要,完全可以写一个单独的分页存储过程,无论在性能还是复杂度上都比通用要简单.
     3)由于时间原因没有大量测试,如有BUG,请您提出,立刻修正;

1if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_RowCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
2drop procedure [dbo].[Paging_RowCount]
3GO
4
5SET QUOTED_IDENTIFIER ON
6GO
7SET ANSI_NULLS ON
8GO
9---------------------------------------------------------------
10-- 分页存储过程(使用RowCount) --edit by SiBen
11-- summary:
12--          获取表或表集合的分页数据
13--          当多表连接时,sort列必须指定表名
14---------------------------------------------------------------
15
16CREATE PROCEDURE Paging_RowCount
17(
18@Tables varchar(1000),
19@PK varchar(100),
20@Sort varchar(200) = NULL,
21@PageNumber int = 1,
22@PageSize int = 10,
23@Fields varchar(1000) = '*',
24@Filter varchar(1000) = NULL,
25@Group varchar(1000) = NULL,
26@RecordCount int = 0 output
27)
28AS
29
30/**//*Default Sorting*/
31IF @Sort IS NULL OR @Sort = ''
32    SET @Sort = @PK
33
34/**//*Find the @PK type*/
35DECLARE @SortTable varchar(100)
36DECLARE @SortName varchar(100)
37DECLARE @strSortColumn varchar(200)
38DECLARE @operator char(2)
39DECLARE @type varchar(100)
40DECLARE @prec int
41
42/**//*Set sorting variables.*/
43IF CHARINDEX('DESC',@Sort)>0
44    BEGIN
45        SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
46        SET @operator = '<'
47    END
48ELSE
49    BEGIN
50        IF CHARINDEX('ASC', @Sort) > 0
51            SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
52                ELSE
53                        SET @strSortColumn = @Sort
54
55        SET @operator = '>'
56    END
57
58
59IF CHARINDEX('.', @strSortColumn) > 0
60    BEGIN
61        SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
62        SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
63    END
64ELSE
65    BEGIN
66        SET @SortTable = @Tables
67        SET @SortName = @strSortColumn
68    END
69
70SELECT @type=t.name, @prec=c.prec
71FROM sysobjects o
72JOIN syscolumns c on o.id=c.id
73JOIN systypes t on c.xusertype=t.xusertype
74WHERE o.name = @SortTable AND c.name = @SortName
75
76IF CHARINDEX('char', @type) > 0
77   SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
78
79DECLARE @strPageSize varchar(50)
80DECLARE @strStartRow varchar(50)
81DECLARE @strFilter varchar(1000)
82DECLARE @strSimpleFilter varchar(1000)
83DECLARE @strGroup varchar(1000)
84
85/**//*Default Page Number*/
86IF @PageNumber < 1
87    SET @PageNumber = 1
88
89/**//*Set paging variables.*/
90SET @strPageSize = CAST(@PageSize AS varchar(50))
91SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))
92
93/**//*Set filter & group variables.*/
94IF @Filter IS NOT NULL AND @Filter != ''
95    BEGIN
96        SET @strFilter = ' WHERE ' + @Filter + ' '
97        SET @strSimpleFilter = ' AND ' + @Filter + ' '
98    END
99ELSE
100    BEGIN
101        SET @strSimpleFilter = ''
102        SET @strFilter = ''
103    END
104IF @Group IS NOT NULL AND @Group != ''
105    SET @strGroup = ' GROUP BY ' + @Group + ' '
106ELSE
107    SET @strGroup = ''
108
109/**//*Get rows count.*/
110DECLARE @str_Count_SQL nvarchar(500)
111SET @str_Count_SQL= 'SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter
112EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output
113
114/**//*Execute dynamic query*/
115IF @strSortColumn =@PK
116    BEGIN
117        EXEC(
118        '
119        DECLARE @SortColumn ' + @type + '
120        SET ROWCOUNT ' + @strStartRow + '
121        SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
122        SET ROWCOUNT ' + @strPageSize + '
123        SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + '= @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
124        '
125        )
126    END
127ELSE
128    BEGIN
129        EXEC(
130        '
131        DECLARE @SortColumn ' + @type + '
132        DECLARE @SortNullValue ' + @type + '
133        DECLARE @PKStartValue int
134        SET @SortNullValue=CAST('''' as '+ @type +')
135        SET ROWCOUNT ' + @strStartRow + '
136        SELECT @SortColumn= isNull(' + @strSortColumn + ',@SortNullValue), @PKStartValue = '+ @PK +' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
137        SET ROWCOUNT ' + @strPageSize + '
138        SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull(' + @strSortColumn+',@SortNullValue)' + @operator + ' @SortColumn or (isNull(' + @strSortColumn+',@SortNullValue)=@SortColumn and '+ @PK +'<=@PKStartValue))' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
139        '
140        )
141    END
142GO
143SET QUOTED_IDENTIFIER OFF
144GO
145SET ANSI_NULLS ON
146GO
147
148