GridViewWithPager(SQL預存程序分頁)

出自ProgWiki
跳至導覽 跳至搜尋

GridViewWithPager(SQL預存程序分頁)

參考資料

原本的用法:

  1. 先下載它的範例[1]
  2. 解壓縮後,把它的範例裡的檔案 GridViewWithPager.cs 與 PagerLinkButton.cs 複製到你的網站的 App_Code 目錄裡。
  3. 在你的.aspx 頁面中加入相關的CSS定義(參照它的Default.aspx)。
  4. 在你的.aspx 頁面上,加入 <%@ Register Namespace="App_Code" TagPrefix="ac" %>
  5. 在你的.aspx 頁面上把 <asp:GridView 與 </asp:GridView> 分別置換為 <ac:GridViewWithPager UseCustomPager="true" 與 </ac:GridViewWithPager>

自訂版本

App_Code\GridViewWithPager2.cs

using System;
using System.Globalization;
using System.Reflection;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
 
namespace App_Code
{
    public class GridViewWithPager2 : GridView
    {
        public int _PageIndex
        {
            get { return (int?)ViewState["_PageIndex"] ?? 0; }
            set { ViewState["_PageIndex"] = value; }
        }
        public int _PageCount
        {
            get { return (int?)ViewState["_PageCount"] ?? 0; }
            set { ViewState["_PageCount"] = value; }
        }
 
        public override int PageCount { get { return _PageCount; } }
        public int PageIndex
        {
            get { return _PageIndex; }
            set {
                int pageIndex = (value < 0) ? 0 : value;
 
                _PageIndex = pageIndex;
                base.PageIndex = pageIndex;
            }
        }
 
        public void CreateCustomPager2(GridViewRow row, int columnSpan)
        {
            int pageCount = _PageCount;
            int pageIndex = _PageIndex + 1;
            int pageButtonCount = PagerSettings.PageButtonCount;
 
            TableCell cell = new TableCell();
            row.Cells.Add(cell);
            if (columnSpan > 1) cell.ColumnSpan = columnSpan;
 
            if (pageCount > 1)
            {
                HtmlGenericControl pager = new HtmlGenericControl("div");
                pager.Attributes["class"] = "pagination";
                cell.Controls.Add(pager);
 
                int min = pageIndex - pageButtonCount;
                int max = pageIndex + pageButtonCount;
 
                if (max > pageCount)
                    min -= max - pageCount;
                else if (min < 1)
                    max += 1 - min;
 
                // Create "previous" button
                Control page = pageIndex > 1
                                ? BuildLinkButton(pageIndex - 2, PagerSettings.PreviousPageText, "Page", "Prev")
                                : BuildSpan(PagerSettings.PreviousPageText, "disabled");
                pager.Controls.Add(page);
 
                // Create page buttons
                bool needDiv = false;
                for (int i = 1; i <= pageCount; i++)
                {
                    if (i <= 2 || i > pageCount - 2 || (min <= i && i <= max))
                    {
                        string text = i.ToString(NumberFormatInfo.InvariantInfo);
                        page = i == pageIndex
                                ? BuildSpan(text, "current")
                                : BuildLinkButton(i - 1, text, "Page", text);
                        pager.Controls.Add(page);
                        needDiv = true;
                    }
                    else if (needDiv)
                    {
                        page = BuildSpan("&hellip;", null);
                        pager.Controls.Add(page);
                        needDiv = false;
                    }
                }
 
                // Create "next" button
                page = pageIndex < pageCount
                        ? BuildLinkButton(pageIndex, PagerSettings.NextPageText, "Page", "Next")
                        : BuildSpan(PagerSettings.NextPageText, "disabled");
                pager.Controls.Add(page);
            }
        }
 
 
        protected void InitializePager2(GridViewRow row, int columnSpan, PagedDataSource pagedDataSource)
        {
            int pageCount = _PageCount;
            TableCell cell = new TableCell();
            if (columnSpan > 1) cell.ColumnSpan = columnSpan;
            if (pageCount > 1)
            {
                HtmlGenericControl pager = new HtmlGenericControl("div");
                pager.Attributes["class"] = "pagination";
                cell.Controls.Add(pager);
 
                PagerSettings pagerSettings = this.PagerSettings;
                {
                    switch (pagerSettings.Mode)
                    {
                        case PagerButtons.NextPrevious:
                            CreateNextPrevPager2(pager, false);
                            break;
 
                        case PagerButtons.Numeric:
                            CreateNumericPager2(pager, false);
                            break;
 
                        case PagerButtons.NextPreviousFirstLast:
                            CreateNextPrevPager2(pager, true);
                            break;
 
                        case PagerButtons.NumericFirstLast:
                            CreateNumericPager2(pager, true);
                            break;
                    }
                    cell.Controls.Add(pager);
                    row.Cells.Add(cell);
                }
            }
        }
 
        private void CreateNextPrevPager2(HtmlControl pager, bool addFirstLastPageButtons)
        {
            int pageCount = _PageCount;
            int pageIndex = _PageIndex + 1;
 
            PagerSettings pagerSettings = this.PagerSettings;
            string previousPageImageUrl = pagerSettings.PreviousPageImageUrl;
            string nextPageImageUrl = pagerSettings.NextPageImageUrl;
            bool isFirstPage = (pageIndex == 1) ? true : false;
            bool isLastPage = (pageIndex == pageCount) ? true : false;
 
            //Show Page Index and Count
            string strPage = string.Format(" Page {0}/{1}", pageIndex, pageCount);
            pager.Controls.Add(BuildSpan(strPage, "current"));
 
            if (addFirstLastPageButtons && !isFirstPage)
            {
                Control control;
                string firstPageImageUrl = pagerSettings.FirstPageImageUrl;
                if (firstPageImageUrl.Length > 0)
                    control = BuildImageButton(0, firstPageImageUrl, pagerSettings.FirstPageText, "Page", "First");
                else
                    control = BuildLinkButton(0, pagerSettings.FirstPageText, "Page", "First");
                pager.Controls.Add(control);
            }
            if (!isFirstPage)
            {
                Control control;
                if (previousPageImageUrl.Length > 0)
                    control = BuildImageButton((pageIndex - 2), previousPageImageUrl, pagerSettings.PreviousPageText, "Page", "Prev");
                else
                    control = BuildLinkButton((pageIndex - 2), pagerSettings.PreviousPageText, "Page", "Prev");
                pager.Controls.Add(control);
            }
            if (!isLastPage)
            {
                Control control;
                if (nextPageImageUrl.Length > 0)
                    control = BuildImageButton(pageIndex, nextPageImageUrl, pagerSettings.NextPageText, "Page", "Next");
                else
                    control = BuildLinkButton(pageIndex, pagerSettings.NextPageText, "Page", "Next");
                pager.Controls.Add(control);
            }
            if (addFirstLastPageButtons && !isLastPage)
            {
                Control control;
                string lastPageImageUrl = pagerSettings.LastPageImageUrl;
                if (lastPageImageUrl.Length > 0)
                    control = BuildImageButton((pageCount - 1), lastPageImageUrl, pagerSettings.LastPageText, "Page", "Last");
                else
                    control = BuildLinkButton((pageCount - 1), pagerSettings.LastPageText, "Page", "Last");
                pager.Controls.Add(control);
            }
        }
 
        private void CreateNumericPager2(HtmlControl pager, bool addFirstLastPageButtons)
        {
            int pageCount = _PageCount;
            int pageIndex = _PageIndex + 1;
 
            PagerSettings pagerSettings = this.PagerSettings;
            int pageButtonCount = pagerSettings.PageButtonCount;
            int num4 = pageButtonCount;
            int num5 = this.FirstDisplayedPageIndex2 + 1;
            if (pageCount < num4)
                num4 = pageCount;
 
            int num6 = 1;
            int x = num4;
            if (pageIndex > x)
            {
                int num8 = (pageIndex - 1) / pageButtonCount;
                bool flag = ((pageIndex - num5) >= 0) && ((pageIndex - num5) < pageButtonCount);
                if ((num5 > 0) && flag)
                    num6 = num5;
                else
                    num6 = (num8 * pageButtonCount) + 1;
 
                x = (num6 + pageButtonCount) - 1;
                if (x > pageCount)
                    x = pageCount;
 
                if (((x - num6) + 1) < pageButtonCount)
                    num6 = Math.Max(1, (x - pageButtonCount) + 1);
 
                this.FirstDisplayedPageIndex2 = num6 - 1;
            }
            if ((addFirstLastPageButtons && (pageIndex != 1)) && (num6 != 1))
            {
                Control control;
                string firstPageImageUrl = pagerSettings.FirstPageImageUrl;
                if (firstPageImageUrl.Length > 0)
                    control = BuildImageButton(0, firstPageImageUrl, pagerSettings.FirstPageText, "Page", "First");
                else
                    control = BuildLinkButton(0, pagerSettings.FirstPageText, "Page", "First");
                pager.Controls.Add(control);
            }
            if (num6 != 1)
            {
                Control control = BuildLinkButton((num6 - 2), "...", "Page", (num6 - 1).ToString(NumberFormatInfo.InvariantInfo));
                pager.Controls.Add(control);
            }
            for (int i = num6; i <= x; i++)
            {
                string str2 = i.ToString(NumberFormatInfo.InvariantInfo);
                if (i == pageIndex)
                {
                    Label child = new Label();
                    child.Text = str2;
                    pager.Controls.Add(child);
                }
                else
                {
                    Control control = BuildLinkButton((i - 1), str2, "Page", str2);
                    pager.Controls.Add(control);
                }
            }
            if (pageCount > x)
            {
                Control control = BuildLinkButton(x, "...", "Page", (x + 1).ToString(NumberFormatInfo.InvariantInfo));
                pager.Controls.Add(control);
            }
            bool flag2 = x == pageCount;
            if ((addFirstLastPageButtons && (pageIndex != pageCount)) && !flag2)
            {
                Control control;
                string lastPageImageUrl = pagerSettings.LastPageImageUrl;
                if (lastPageImageUrl.Length > 0)
                    control = BuildImageButton((pageCount - 1), lastPageImageUrl, pagerSettings.LastPageText, "Page", "Last");
                else
                    control = BuildLinkButton((pageCount - 1), pagerSettings.LastPageText, "Page", "Last");
                pager.Controls.Add(control);
            }
        }
 
        private int FirstDisplayedPageIndex2
        {
            get
            {
                object obj2 = this.ViewState["FirstDisplayedPageIndex"];
                if (obj2 != null)
                {
                    return (int)obj2;
                }
                return -1;
            }
            set
            {
                this.ViewState["FirstDisplayedPageIndex"] = value;
            }
        }
 
        public bool UseCustomPager
        {
            get { return (bool?)ViewState["UseCustomPager"] ?? false; }
            set { ViewState["UseCustomPager"] = value; }
        }
 
        protected override void InitializePager(GridViewRow row, int columnSpan, PagedDataSource pagedDataSource)
        {
            if (UseCustomPager)
                CreateCustomPager2(row, columnSpan);
            else
                InitializePager2(row, columnSpan, pagedDataSource);
        }
 
        private Control BuildImageButton(int pageIndex, string imageUrl, string alternateText, string commandName, string commandArgument)
        {
            PagerImageButton imgBtn = new PagerImageButton(this);
            imgBtn.ImageUrl = imageUrl;
            imgBtn.AlternateText = HttpUtility.HtmlDecode(alternateText);
            imgBtn.EnableCallback(ParentBuildCallbackArgument(pageIndex));
            imgBtn.CommandName = commandName;
            imgBtn.CommandArgument = commandArgument;
            return imgBtn;
        }
 
        private Control BuildLinkButton(int pageIndex, string text, string commandName, string commandArgument)
        {
            PagerLinkButton link = new PagerLinkButton(this);
            link.Text = text;
            link.EnableCallback(ParentBuildCallbackArgument(pageIndex));
            link.CommandName = commandName;
            link.CommandArgument = commandArgument;
            return link;
        }
 
        private Control BuildSpan(string text, string cssClass)
        {
            HtmlGenericControl span = new HtmlGenericControl("span");
            if (!String.IsNullOrEmpty(cssClass)) span.Attributes["class"] = cssClass;
            span.InnerHtml = text;
            return span;
        }
 
        private string ParentBuildCallbackArgument(int pageIndex)
        {
            MethodInfo m =
                typeof(GridView).GetMethod("BuildCallbackArgument", BindingFlags.NonPublic | BindingFlags.Instance, null,
                                            new Type[] { typeof(int) }, null);
            return (string)m.Invoke(this, new object[] { pageIndex });
        }
    }
}

App_Code\PagerImageButton.cs

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
 
namespace App_Code
{
    /// <summary>
    /// PagerImageButton 的摘要描述
    /// </summary>
    public class PagerImageButton : ImageButton
    {
        // Fields
        private string _callbackArgument;
        private IPostBackContainer _container;
        private bool _enableCallback;
 
        // Methods
        internal PagerImageButton(IPostBackContainer container)
        {
            this._container = container;
        }
 
        internal void EnableCallback(string argument)
        {
            this._enableCallback = true;
            this._callbackArgument = argument;
        }
 
        protected sealed override PostBackOptions GetPostBackOptions()
        {
            if (this._container != null)
            {
                return this._container.GetPostBackOptions(this);
            }
            return base.GetPostBackOptions();
        }
 
        protected override void Render(HtmlTextWriter writer)
        {
            this.SetCallbackProperties();
            base.Render(writer);
        }
 
        private void SetCallbackProperties()
        {
            if (this._enableCallback)
            {
                ICallbackContainer container = this._container as ICallbackContainer;
                if (container != null)
                {
                    string callbackScript = container.GetCallbackScript(this, this._callbackArgument);
                    if (!string.IsNullOrEmpty(callbackScript))
                    {
                        this.OnClientClick = callbackScript;
                    }
                }
            }
        }
 
        // Properties
        public override bool CausesValidation
        {
            get
            {
                return false;
            }
            set
            {
                throw new NotSupportedException("Can not SetValidation On PagerImageButton");
            }
        }
    }
}

App_Code\GridViewHelper.cs

App_Code\ClassSQL.cs

其它相關檔案

  • 延用【參考資料】那邊的

使用範例

SQL預存程序

USE [db_Test]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_getTable1Paged]
(
	@UserID as int,
	@StartDate as datetime,
	@EndDate as datetime,
	@FindStr as nvarchar(255),
	@PageSize as int,
	@PageIndex as int,
	@SortField as nvarchar(255),
	@IsSortDesc as bit
) AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
	DECLARE @PageCount as int;
	DECLARE @PageIndex2 as int;
	DECLARE @TotalRowCount as int;
	DECLARE @StartRowIndex as int;
	DECLARE @EndingRowIndex as int;
	DECLARE @SortDirection as nvarchar(5);
	DECLARE @SQLString nvarchar(4000);
	DECLARE @ParmDefinition nvarchar(4000);
	DECLARE @TempTable as nvarchar(20);
 
	if (@IsSortDesc = 0)
		set @SortDirection = N' asc';
	else
		set @SortDirection = N' desc';
 
	select @TempTable = '##Temp' + convert(varchar(10),convert(int,rand()*9999999));
 
	if (len(@FindStr) = 0)
		begin
		--篩選條件,只有日期範圍
			set @SQLString = N'select x.* 
			into into '+@TempTable+N'
			from Table1 x
			where (x.UpdateTime >= @StartDate2)
				AND (x.UpdateTime < @EndDate2)
			ORDER BY x.UpdateTime desc;';
 
			set @ParmDefinition = N'@StartDate2 datetime, @EndDate2 datetime';
			EXECUTE sp_executesql @SQLString, @ParmDefinition, @StartDate2=@StartDate, @EndDate2=@EndDate;
		end
	else
		begin
		--篩選條件,日期範圍+關鍵字
 
			DECLARE @FindStr2 as nvarchar(1000);
			set @FindStr2 = replace(rtrim(ltrim(@FindStr)),'''','''''');
 
			set @SQLString = N'select x.*
			into '+@TempTable+N'
			from Table1 x
			where (x.UpdateTime >= @StartDate2)
				AND (x.UpdateTime < @EndDate2) AND
				 ((x.No like ''%''+@FindStr3+''%'')
				 or (x.UserName like ''%''+@FindStr3+''%'')
				 or (x.Title like ''%''+@FindStr3+''%'')
				 or (x.Description like ''%''+@FindStr3+''%'')
				)
			ORDER BY x.UpdateTime desc;';
 
			set @ParmDefinition = N'@StartDate2 datetime, @EndDate2 datetime, @FindStr3 nvarchar(1000)';
			EXECUTE sp_executesql @SQLString, @ParmDefinition, @StartDate2=@StartDate, @EndDate2=@EndDate, @FindStr3=@FindStr2;
		end
 
 
--------------
			set @SQLString = N'select @TotalRowCountOut=count(*) from '+@TempTable;
			set @ParmDefinition = N'@TotalRowCountOut int OUTPUT';
			EXECUTE sp_executesql @SQLString, @ParmDefinition, @TotalRowCountOut=@TotalRowCount OUTPUT;
 
			if (@PageIndex < 0)
				set @PageIndex2 = 0; 
			else if (((@PageIndex+1)*@PageSize) > @TotalRowCount)
				set @PageIndex2 = (@TotalRowCount / @PageSize);
			else
				set @PageIndex2 = @PageIndex;
 
			set @PageCount=(@TotalRowCount/@PageSize)
			if ((@PageCount*@PageSize) <@TotalRowCount)
				set @PageCount=@PageCount+1;
 
			set @StartRowIndex = (@PageSize*@PageIndex2)+1;
			set @EndingRowIndex =@PageSize*(@PageIndex2+1);
 
			if (len(@SortField) = 0)
				begin
					set @SQLString = 'SELECT x.*
					FROM 
					(
						SELECT rank() OVER (ORDER BY UpdateTime desc,ID) AS RowNum, * 
							FROM ' + @TempTable + '
					) AS x
					WHERE x.RowNum between @StartRowIndex and @EndingRowIndex;';
 
					EXECUTE sp_executesql @SQLString;
				end
			else
				begin
					set @SQLString = 'SELECT x.*
					FROM 
					(
						SELECT rank() OVER (ORDER BY '+@SortField+@SortDirection+',ID) AS RowNum, * 
							FROM ' + @TempTable + '
					) AS x
					WHERE x.RowNum between '+convert(varchar(10),@StartRowIndex)+' and '+convert(varchar(10),@EndingRowIndex);
					EXECUTE sp_executesql @SQLString;
				end
 
			select	[PageCount]=@PageCount,
					[PageIndex]=@PageIndex2,
					[TotalRowCount]=@TotalRowCount,
					[StartRow]=@StartRowIndex,
					[EndingRow]=@EndingRowIndex;
 
			set @SQLString = 'DROP TABLE '+@TempTable;
			EXECUTE sp_executesql @SQLString;
 
END

index.aspx

  • ac:GridViewWithPager 改為 ac:GridViewWithPager2
  • GridView裡不可有DataSourceID屬性

index.aspx.cs

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;
 
public partial class Index : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //已登入驗證, 略...
 
        if (!IsPostBack)
	{
            GridView1_DataBinding();
	}
    }
 
    protected void GridView1_DataBinding()
    {
        int PageSize = GridView1.PageSize;
        int PageIndex = GridView1._PageIndex;
        string strSortField = (ViewState["SortDirection"] == null) ? "" : ViewState["SortField"].ToString();
        int IsSortDesc = (ViewState["SortField"] == null) ? 0 : 
            ( (ViewState["SortDirection"].ToString() == SortDirection.Descending.ToString()) ? 1: 0);
 
        DateTime firstDate;
        DateTime lastDate = DateTime.Today.AddDays(+1);
 
        if (this.CheckBox1.Checked == true)
        {
            firstDate = DateTime.Today.AddMonths(-1);   //限定1個月內
        }
        else
        {
            firstDate = DateTime.Today.AddMonths(-120); //限定10年內
        }
 
 
	ClassSQL objSql = new ClassSQL();
	using (SqlConnection conn = objSql.OpenConnection())
	{
            string strSql = string.Format(
                "exec [dbo].[sp_getTable1Paged] {0},{1},{2},{3},{4},{5},{6},{7}",
                    Session["UserID"].ToString(),
                    SafeSQL.Quote(firstDate.ToString("yyyy/MM/dd")),
                    SafeSQL.Quote(lastDate.ToString("yyyy/MM/dd")),
                    SafeSQL.Quote(TextBox1.Text),
                    PageSize,
                    PageIndex,
                    SafeSQL.Quote(strSortField),
                    IsSortDesc);
 
 
            using (DataSet ds = objSql.ExecuteDataSet(conn, strSql))
            {
                GridView1._PageCount = (int)ds.Tables[1].Rows[0]["PageCount"]; 
                GridView1._PageIndex = (int)ds.Tables[1].Rows[0]["PageIndex"];
 
                using (DataView dv = ds.Tables[0].DefaultView)
                {
                    if ((ViewState["SortDirection"] == null) ||
                        (ViewState["SortField"] == null))
                    {
                    }
                    else
                    {
                        string strSort = ViewState["SortField"].ToString();
                        if (ViewState["SortDirection"].ToString() == SortDirection.Descending.ToString())
                            strSort += " DESC";
 
                        dv.Sort = strSort;
                    }
 
                    GridView1.DataSource = dv;
                    GridView1.DataBind();
                }
            }
    	}
    }
 
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        this.GridView1.PageIndex = e.NewPageIndex;
        GridView1_DataBinding();
    }
 
    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
        GridViewHelper.setSorting(sender, e, ViewState);
        GridView1_DataBinding();
    }
 
    protected void ButtonFind_Click(object sender, EventArgs e)
    {
        TextBox1.Text = TextBox1.Text.Trim();
        GridView1_DataBinding();
    }
 
    protected void ButtonAll_Click(object sender, EventArgs e)
    {
        TextBox1.Text = "";
        GridView1_DataBinding();
    }
}

相關