使用SQL Server2005的新函式構造分頁儲存過程

分頁儲存過程一直是很多人經常用到的東西，怎麼樣才能設計一個既實用，又強大，還可以解決大資料量的分頁儲存過程呢？其實在很多時候設計的度還是要把握的，不至於讓自己陷入【非要把它設計成萬能的分頁儲存過程】的怪圈中才是最重要的，因為我們還要留出時間還解決其他的很多問題，個人認為適度就可以了，留出一定的空間。也因為萬能是不存在的，萬物在一定的範疇之內都是合理的，出了範疇可能就沒有合理的了。

分頁儲存過程大致有下列幾種

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如下，支援不定列，不定條件，多表聯合，排序任意

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt

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

　　示例程式碼，前臺頁面，前臺為使用者控制元件

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt@ Control Language="C#" AutoEventWireup="true" CodeBehind="StudentDetailsTable.ascx.cs" Inherits="Kimbanx.UCS.ForeignStudentAdmin.UserControl.UserControl.StudentDetailsTable" %>
<link href="../css/core.css" rel="stylesheet" type="text/css" />

<table class="StudentPagingTablePanel">
    <tr>
        <td> 單位：<asp:Label ID="lblClassName" runat="server" Text="Label">asp:Label>td>
        <td>級別：<asp:Label ID="lblClassLevel" runat="server" Text="Label">asp:Label>級節點td>
    tr>
    <tr>
        <td>該單位共有<asp:Label ID="lblStudentType" runat="server" Text="Label">asp:Label>學員
        <asp:Label ID="lblStudentCount" runat="server" Text="Label">asp:Label>人td>
        <td>每頁顯示<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="True"
                onselectedindexchanged="ddlPageSize_SelectedIndexChanged">
            asp:DropDownList>人  共<asp:Label ID="lblPageCount" runat="server" Text="Label">asp:Label>頁
              現為第<asp:Label ID="lblPageIndex" runat="server" Text="Label">asp:Label>頁
            <asp:LinkButton ID="firstPage" runat="server" CommandArgument="first"
                oncommand="LinkButton_Command">首頁asp:LinkButton>

            <asp:LinkButton ID="nextPage" runat="server" CommandArgument="next"
                oncommand="LinkButton_Command">下一頁asp:LinkButton>
                <asp:LinkButton ID="prevPage" runat="server" CommandArgument="prev"
                oncommand="LinkButton_Command">上一頁asp:LinkButton>
            <asp:LinkButton ID="lastPage" runat="server" CommandArgument="last"
                oncommand="LinkButton_Command">末頁asp:LinkButton>
        td>
    tr>
table>
<br />
<asp:GridView ID="gvStudent" runat="server" AutoGenerateColumns="False"
    EmptyDataText="沒有符合條件的資料">
    <Columns>
        <asp:TemplateField HeaderText="照片">
            <ItemTemplate>
                <asp:Image ID="Image1" CssClass="studentImage" ImageUrl =GetStudentImageUrl(Eval("zpadress")) %> runat="server" />
            ItemTemplate>
        asp:TemplateField>
        <asp:TemplateField HeaderText="姓名(中英簡)">
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='Eval("xmjz") %>'>asp:Label>
                <br />
                <asp:Label ID="Label2" runat="server" Text='Eval("xmjy") %>'>asp:Label>
            ItemTemplate>
        asp:TemplateField>
        <asp:BoundField DataField="jx" HeaderText="軍銜" />
        <asp:BoundField DataField="zw" HeaderText="職務" />
        <asp:BoundField DataField="gj" HeaderText="國家" />
        <asp:BoundField DataField="sjyqk" HeaderText="文化程度" />
        <asp:BoundField DataField="zj" HeaderText="宗教" />
        <asp:TemplateField HeaderText="出生/入伍">
            <ItemTemplate>
                <asp:Label ID="Label3" runat="server" Text='SetBirthDate(Eval("csrq")) %>'>asp:Label>
                <br />
                <asp:Label ID="Label4" runat="server" Text='SetEnrollDate(Eval("rwrq")) %>'>asp:Label>
            ItemTemplate>
        asp:TemplateField>
        <asp:BoundField DataField="xzz" HeaderText="房間/樓號" />
        <asp:TemplateField HeaderText="電話/小號">
            <ItemTemplate>
                <asp:Label ID="Label5" runat="server" Text='Eval("dhd") %>'>asp:Label>
                <br />
                <asp:Label ID="Label6" runat="server" Text='Eval("dhx") %>'>asp:Label>
            ItemTemplate>
        asp:TemplateField>
        <asp:BoundField DataField="fcjp" HeaderText="返程機票" />
        <asp:BoundField DataField="xh" HeaderText="學號" />
    Columns>
asp:GridView>

　　示例程式碼，後臺程式碼

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtusing 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;
        //}
        #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-617127/，如需轉載，請註明出處，否則將追究法律責任。

使用SQL Server2005的新函式構造分頁儲存過程

相關文章