예제 #1
0
        private DataTable GetData()
        {
            try
            {
                string sSql = @"Select count(store_no) 
                               From store
                               Where store_name Like '%" + sSearchStore + "%'";

                OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                OleDbCommand    cmd  = new OleDbCommand(sSql, conn);
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("store_name", sSearchStore);
                conn.Open();
                // 共有多少筆資料
                int iRecordCount = (int)cmd.ExecuteScalar();

                Pagination Page = new Pagination(iRecordCount, Request["page"]);

                // 輸出資料並綁定GridView
                sSql = @"Select * 
                         From store 
                         Where store_name Like '%" + sSearchStore + @"%'" + @"
                            And store_no 
                            Between (Select min(store_no) 
                                From (Select Top {0} store_no From store Where store_name Like '%" + sSearchStore + @"%' Order By store_no Desc))
                            And ( Select min(store_no) 
                                From (Select Top {1} store_no From store Where store_name Like '%" + sSearchStore + @"%' Order By store_no Desc))
                         Order By store_no Desc";

                sSql = Page.FormatSql(sSql);

                dt = new DataTable();

                cmd = new OleDbCommand(sSql, conn);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(dt);

                ViewState["dt_sotre"] = dt;

                if (dt.Rows.Count > 0)
                {
                    this.GridView1.DataSource = ViewState["dt_sotre"];
                    this.GridView1.DataBind();

                    this.lblPagination.Text  = Page.PaginationOut("aStore.aspx", sSearchStore).ToString();
                    this.lblResultStats.Text = "共" + Page.TotalPage + "頁," + iRecordCount.ToString() + "筆結果";
                }
                else
                {
                    dt.Rows.Add(dt.NewRow());
                    this.GridView1.DataSource = ViewState["dt_sotre"];
                    this.GridView1.DataBind();
                    this.GridView1.Rows[0].Cells.Clear();
                    this.GridView1.Rows[0].Cells.Add(new TableCell());
                    this.GridView1.Rows[0].Cells[0].ColumnSpan      = this.GridView1.Columns.Count;
                    this.GridView1.Rows[0].Cells[0].Text            = "查無資料...馬上新增!";
                    this.GridView1.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center;

                    this.lblResultStats.Text = "";
                    this.lblPagination.Text  = "";
                }
            }
            catch (Exception ex)
            {
                this.divAlert.Style.Add("display", "block");
                this.lblAlert.Text = "Error : " + ex.Message;
            }

            return(dt);
        }