[新闻系统]自定义分页控件(针对MS SQL数据库)

参考了MSDN的分页控件  他写的太麻烦也用不到那么多东西,对于这个新闻系统来讲也没必要做那么麻烦
参考地址:http://www.microsoft.com/china/msdn/archives/library/dnaspp/html/PagerControls.asp

针对MS SQL数据库 采用SQL语句查询

下载https://files.cnblogs.com/zhzkl/Control.rar
代码贴出来

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Data;

namespace zhzklControls
{
    /**//// <summary>
    /// WebCustomControl1 的摘要说明。
    /// </summary>
    [DefaultProperty("Text"),
        ToolboxData("<{0}:ControlTest runat=server></{0}:ControlTest>")]
    public class ControlTest : System.Web.UI.WebControls.WebControl , INamingContainer
    {
        public class PageChangedEventArgs : EventArgs
        {
            public int OldPageIndex;
            public int NewPageIndex;
        }
        
        private string text;
        private Control _controlToPaginate = null;
        private string QueryPageCommandText = "SELECT * FROM " + 
            "(SELECT TOP {0} * FROM " + 
            "(SELECT TOP {1} * FROM ({2}) AS t0 ORDER BY {3} {4}) AS t1 " + 
            "ORDER BY {3} {5}) AS t2 " + 
            "ORDER BY {3}";
        private string QueryCountCommandText = "SELECT COUNT(*) FROM ({0}) AS t0";

        Programme Interface#region Programme Interface        
        [Bindable(true),Category("Appearance"),DefaultValue("")]
        public string Text
        {
            get
            {
                return text;
            }

            set
            {
                text = value;
            }
        }

        public string Test()
        {
            return "欢迎来到自定义控件的世界";
        }

        [Description("表中的排序字段")]
        public string SortField
        {
            get {return Convert.ToString(ViewState["SortKeyField"]);}
            set {ViewState["SortKeyField"] = value;}
        }

        public string ConnectionString
        {
            get { return Convert.ToString(ViewState["ConnectionString"]); }
            set { ViewState["ConnectionString"] = value; }
        }
        public string SelectCommand
        {
            get { return Convert.ToString(ViewState["SelectCommand"]); }
            set { ViewState["SelectCommand"] = value; }
        }
        
        public int PageSize
        {
            get { return Convert.ToInt32(ViewState["PageSize"]); }
            set { ViewState["PageSize"] = value; }
        }

        private int TotalPages
        {
            get { return Convert.ToInt32(ViewState["TotalPages"]); }
            set { ViewState["TotalPages"] = value; }
        }
        public int CurrentPageIndex
        {
            get {return Convert.ToInt32(ViewState["CurrentPageIndex"]);}
            set {ViewState["CurrentPageIndex"] = value;}
        }

        public delegate void PageChangedEventHandler(object sender, PageChangedEventArgs e);
        public event PageChangedEventHandler PageIndexChanged;//呵呵 第一次看到代理与事件的应用
        protected virtual void OnPageIndexChanged(PageChangedEventArgs e)
        {
            if (PageIndexChanged != null)
                PageIndexChanged(this, e);
        }

        public string ControlToPaginate
        {
            get { return Convert.ToString(ViewState["ControlToPaginate"]); }
            set { ViewState["ControlToPaginate"] = value; }
        }

        /**//// <summary>
        /// 执行数据绑定，覆盖了基类的数据绑定
        /// </summary>
        public override void DataBind()
        {
            base.DataBind();
            ChildControlsCreated = false;

            _controlToPaginate = Page.FindControl(ControlToPaginate);

            TotalPages = GetPageCount();

            BaseDataList baseDataListControl = null;
            ListControl listControl = null;
            Repeater repeater = null;
            if(_controlToPaginate is Repeater)
            {
                repeater = (Repeater) _controlToPaginate;
                repeater.DataSource = FetchPageData();
                repeater.DataBind();            
            }
            if (_controlToPaginate is BaseDataList)
            {
                baseDataListControl = (BaseDataList) _controlToPaginate;
                baseDataListControl.DataSource = FetchPageData(); 
                baseDataListControl.DataBind();
            }
            if (_controlToPaginate is ListControl)
            {
                listControl = (ListControl) _controlToPaginate;
                listControl.Items.Clear(); 
                listControl.DataSource = FetchPageData(); 
                listControl.DataBind();
            }        
        }
        #endregion

        UserInterface#region UserInterface    
        private void BuildUI()
        {
            bool CanMoveBack = CurrentPageIndex > 0;
            bool CanMoveNext = CurrentPageIndex < TotalPages - 1;

            Table t = new Table();
            t.Font.Name = Font.Name;
            t.Font.Size = Font.Size;
            t.BorderStyle = BorderStyle;
            t.BorderWidth = BorderWidth;
            t.BorderColor = BorderColor;
            t.Width = Width;
            t.Height = Height;
            t.BackColor = BackColor;
            t.ForeColor = ForeColor;

            // Build the table row
            TableRow row = new TableRow();
            t.Rows.Add(row);
            
            // Build the cell with navigation bar
            TableCell cell = new TableCell();
            LinkButton first = new LinkButton();
            first.ID = "First";
            first.Click += new EventHandler(first_Click);
            first.ForeColor = ForeColor;
            first.ToolTip = "第一页";
            first.Text = "第一页";
            first.Enabled = CanMoveBack;
            cell.Controls.Add(first);

            // Add a separator
            cell.Controls.Add(new LiteralControl("&nbsp;"));

            // Render the < button
            LinkButton prev = new LinkButton();
            prev.ID = "Prev";
            prev.Click +=new EventHandler(prev_Click);
            prev.ForeColor = ForeColor;
            prev.ToolTip = "上一页";
            prev.Text = "上一页";
            prev.Enabled = CanMoveBack;
            cell.Controls.Add(prev);

            // Add a separator
            cell.Controls.Add(new LiteralControl("&nbsp;"));
            
            // Render the > button
            LinkButton next = new LinkButton();
            next.ID = "Next";
            next.Click += new EventHandler(next_Click);
            next.ForeColor = ForeColor;
            next.ToolTip = "下一页";
            next.Text = "下一页";    
            next.Enabled = CanMoveNext;
            cell.Controls.Add(next);

            // Add a separator
            cell.Controls.Add(new LiteralControl("&nbsp;"));

            // Render the >> button
            LinkButton last = new LinkButton();
            last.ID = "Last";
            last.Click += new EventHandler(last_Click);
            last.ForeColor = ForeColor;
            last.ToolTip = "最后一页";
            last.Text = "最后一页";
            last.Enabled = CanMoveNext;
            cell.Controls.Add(last);

            cell.Controls.Add(new LiteralControl("&nbsp;&nbsp;"));

            // 跳转
            cell.Controls.Add(new LiteralControl("转到"));

            DropDownList dpPageIndex = new DropDownList();
            dpPageIndex.ID = "dpPageIndex";
            dpPageIndex.AutoPostBack = true;
            dpPageIndex.ForeColor = ForeColor;
            for(int i=1; i<=TotalPages; i++)
            {
                ListItem item = new ListItem(i.ToString(), (i-1).ToString());
                dpPageIndex.Items.Add(item);
            }
            dpPageIndex.SelectedIndex = CurrentPageIndex;
            dpPageIndex.SelectedIndexChanged += new EventHandler(dpPageIndex_SelectedIndexChanged);
            cell.Controls.Add(dpPageIndex);

            cell.Controls.Add(new LiteralControl("页&nbsp;共"));

            Literal lt = new Literal();
            lt.Text = TotalPages.ToString();
            cell.Controls.Add(lt);

            cell.Controls.Add(new LiteralControl("页"));

            row.Cells.Add(cell);
            Controls.Add(t);
        }
        
        #endregion

        HelpMethod#region HelpMethod 
        protected DataTable FetchPageData()
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter dp = new SqlDataAdapter();

            int recordsToRec = PageSize;
            int lastPageRecord = PageSize;
            if( CurrentPageIndex == TotalPages - 1) 
            {
                if((GetQueryVirtualCount()%PageSize) > 0)
                    lastPageRecord = this.GetQueryVirtualCount() % PageSize;

                recordsToRec = lastPageRecord;
            }
            
            string cmdText = String.Format(QueryPageCommandText, 
                recordsToRec,                            // {0} --> page size
                PageSize*(CurrentPageIndex+1),        // {1} --> size * index
                SelectCommand,                        // {2} --> base query
                SortField,                            // {3} --> key field in the query
                "ASC",                                // Default to ascending order
                "DESC");
            dp.SelectCommand = new SqlCommand(cmdText,conn);

            DataTable dt = new DataTable();
            dp.Fill(dt);

            return dt;
        }

        protected void GoToPage(int pageIndex)
        {
            // Prepares event data
            PageChangedEventArgs e = new PageChangedEventArgs();
            e.OldPageIndex = CurrentPageIndex;
            e.NewPageIndex = pageIndex;

            // Updates the current index
            CurrentPageIndex = pageIndex;
            DropDownList dpPageIndex = (DropDownList)this.FindControl("dpPageIndex");
            dpPageIndex.SelectedIndex = pageIndex;

            // Fires the page changed event
            OnPageIndexChanged(e);

            this.DataBind();
        }

        private int GetPageCount()
        {
            return (GetQueryVirtualCount()%PageSize) == 0?(GetQueryVirtualCount()/PageSize):(GetQueryVirtualCount()/PageSize) + 1;
        }

        private int GetQueryVirtualCount()
        {
            string cmdText = String.Format(QueryCountCommandText, SelectCommand);
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(cmdText, conn);

            cmd.Connection.Open();
            int recCount = (int) cmd.ExecuteScalar(); 
            cmd.Connection.Close();

            return recCount;
        }
        #endregion 

        private void first_Click(object sender, EventArgs e)
        {
            GoToPage(0);
        }
        private void prev_Click(object sender, EventArgs e)
        {
            GoToPage(this.CurrentPageIndex - 1);
        }

        private void next_Click(object sender, EventArgs e)
        {
            GoToPage(this.CurrentPageIndex + 1);
        }

        private void last_Click(object sender, EventArgs e)
        {
            GoToPage(this.TotalPages - 1);
        }

        protected override void CreateChildControls()
        {
            Controls.Clear();
            ClearChildViewState();

            BuildUI();
        }

        /**//// <summary>
        /// 将此控件呈现给指定的输出参数。    
        /// </summary>
        /// <param name="output"> 要写出到的 HTML 编写器 </param>
        protected override void Render(HtmlTextWriter output)
        {
            if (Site != null && Site.DesignMode) 
                CreateChildControls();            
            
            base.Render(output);
        }

        private void dpPageIndex_SelectedIndexChanged(object sender, EventArgs e)
        {
            DropDownList pageList = (DropDownList) sender;
            int pageIndex = Convert.ToInt32(pageList.SelectedItem.Value);
            GoToPage(pageIndex);
        }
    }
}