GridView自定義分頁---派生類(2)

        /// <summary>
        /// 設定數據源
        /// </summary>
        private void SetDataSource(bool blnAllData)
        { 
           

 using (SqlConnection sqlConn = new SqlConnection(ConnCollection.GetSqlConnStr()))
            {
                sqlConn.Open();

                if (blnAllData)
                {
                    DataSource = SqlHelper.ExecuteDataset(sqlConn, CommandType.Text, SelectCommand + " " + OrderBy).Tables[0];
                }
                else
                {
                    string sql = String.Format("select count(*) from ({0}) r", SelectCommand);

                    TotalCount = Convert.ToInt32(SqlHelper.ExecuteScalar(sqlConn, CommandType.Text, sql));
                    
                    if (AllowCustPaging)
                    {
                        TotalPage = Convert.ToInt32(decimal.Ceiling(Convert.ToDecimal(TotalCount * 1.0 / PageSize)).ToString());

                        if (TotalPage>0 && TotalPage < CurrentPage)
                            CurrentPage = TotalPage;

                        sql = String.Format(
                            "select  *  from (select a.*,row_number() OVER ({0}) AS row_number from ({1}) a) r  " +
                            "where row_number>{2}  and row_number<{3} {0} ",
                            OrderBy, SelectCommand, (CurrentPage - 1) * PageSize,
                            CurrentPage * PageSize + 1);

                        DataSource = SqlHelper.ExecuteDataset(sqlConn, CommandType.Text, sql).Tables[0];
                    }
                    else
                    {
                        TotalPage = TotalCount == 0 ? 0 : 1;
                        PageSize = TotalCount == 0 ? 10 : TotalCount;

                        DataSource = SqlHelper.ExecuteDataset(sqlConn, CommandType.Text, SelectCommand + " " + OrderBy).Tables[0];
                    }
                }

                sqlConn.Close();
            }

        }
        /// <summary>
        /// 產生子元件
        /// </summary>
        /// <param name="dataSource">資料來源</param>
        /// <param name="dataBinding">是否繫結</param>
        /// <returns>成功筆數</returns>
        protected override int CreateChildControls(System.Collections.IEnumerable dataSource, bool dataBinding)
        {
            if (!EmptyDataText.IsEmpty())
            {
                EmptyTableRowText = EmptyDataText;
                EmptyDataText = null;
            }

            int numRows = base.CreateChildControls(dataSource, dataBinding);

            DataControlField[] fields = GetEmptyFields();
 
            if (numRows == 0)
            {
                // 加入查無資料table
                CreateEmptyTable(fields);
            }
            else if (TotalPage > 1) // custor paging
            {
                // 自定義分頁
                CreatePageRow(fields);
            }
             
            if (TotalPage > 0)
            {
                //  顯示資料筆數
                if (ShowRecordCount)
                {
                    CreateResultRow(fields);
                }

                if (ShowExportExcelButton && TotalPage < 2)
                {
                    GridViewRow row = new GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal);

                    row.CssClass = "pageRow";

                    row.Cells.Clear();

                    TableHeaderCell cell = new TableHeaderCell();

                    row.Cells.Add(cell);

                    cell.ColumnSpan = fields.Length;

                    AddExportExcelButton(cell);

                    Table t = this.Controls[0] as Table;

                    t.Rows.Add(row);
                }
            }

            return numRows;
        }


        #region Method For CreateChildControls

        /// <summary>
        /// 加入查無資料table
        /// </summary>
        /// <param name="fields"></param>
        private void CreateEmptyTable(DataControlField[] fields)
        {
            //create table
            Table table = new Table();
            table.ID = this.ID;

            //create a new header row
            GridViewRow row =
                base.CreateRow(-1, -1, DataControlRowType.Header, DataControlRowState.Normal);

            row.Width = this.Width;

            this.InitializeRow(row, fields);

            table.Rows.Add(row);

            //create the empty row
            row = new GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal);

            TableCell cell = new TableCell();

            cell.ColumnSpan = fields.Length;
            cell.Style.Add(HtmlTextWriterStyle.Color, "red");
            cell.Style.Add(HtmlTextWriterStyle.TextAlign, "center");
            cell.Controls.Add(new LiteralControl(EmptyTableRowText));

            row.Cells.Add(cell);

            table.Rows.Add(row);

            this.Controls.Add(table);
        }
        /// <summary>
        /// 加入分頁行
        /// </summary>
        /// <param name="fields"></param>
        private void CreatePageRow(DataControlField[] fields)
        {
            GridViewRow row = new GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal);

            row.CssClass = "pageRow";

            row.Cells.Clear();

            TableHeaderCell cell = new TableHeaderCell();

            row.Cells.Add(cell);

            cell.ColumnSpan = fields.Length;

            HtmlGenericControl div = new HtmlGenericControl("div");

            div.Style.Add("float", "left");
            
            cell.Controls.Add(div);

            div.Controls.Add(CreateButton(1, "首頁", "First", (CurrentPage > 1)));

            div.Controls.Add(CreateSpace());

            div.Controls.Add(CreateButton(CurrentPage - 1, "前頁", "Prev", (CurrentPage > 1)));

            div.Controls.Add(CreateSpace());

            div.Controls.Add(CreateButton(CurrentPage + 1, "後頁", "Next", (CurrentPage + 1 <= TotalPage)));

            div.Controls.Add(CreateSpace());

            div.Controls.Add(CreateButton(TotalPage, "尾頁", "Last", (CurrentPage + 1 <= TotalPage)));

            HtmlGenericControl span = new HtmlGenericControl("span");

            div.Controls.Add(span);

            Literal liter = new Literal();

            liter.Text = String.Format(" 第 {0} 頁/共 {1} 頁  ", CurrentPage, TotalPage);

            span.Controls.Add(liter);

            TextBox txt = new TextBox();

            txt.ID = "txtNewPageIndex"+ this.ID;

            txt.Text = CurrentPage.ToString();

            txt.TextChanged += new EventHandler(txt_TextChanged);

            txt.Width = Unit.Pixel(40);

            txt.Height = Unit.Pixel(15);
            
            txt.Style.Add("padding", "0");
            
            txt.CausesValidation = false;

            div.Controls.Add(txt);

            div.Controls.Add(CreateSpace());

            div.Controls.Add(CreateButton(-1, "轉", "-1", true));
             
            if (ShowExportExcelButton)
            {
                AddExportExcelButton(cell);
            }
             
            Table t = this.Controls[0] as Table;

            t.Rows.Add(row);
        }