WPF之分頁查詢

標籤：

分頁查詢，很早就聽過了，尤其是在“高校”的項目中更是聽得比較多，一直想著自己研究一下，看看到底分頁查詢有什麼神秘之處。但是，一直因為自己在做其他的內容，所以一直沒有好好的學習一下。現在，做統計查詢的時候，因為查詢的資料量比較大，差不多一秒一條資料，甚至是一秒鐘600條資料的時間查詢，所以不得不採用分頁查詢了。

我們常說的分頁查詢分為兩種。一種是假分頁，也就是一次性的把需要的資料全部查出，然後在顯示的時候是分頁顯示的。這樣做的好處就是直觀的給使用者顯示出需要的內容，而不用採用捲軸來滾動查看。另外就是，分頁的速度是比較快的。

在這裡我主要想總結的就是另一種分頁方式——真分頁。所謂真分頁，就是所見即所得 (WYSIWYG)，我們看到的資料，就是我們當前查詢到的資料。這樣做的好處就是，當我們查詢的資料量非常大的時候，比如當我們查詢一萬條資料的時候，可以非常迅速的查詢出先展示給使用者的那部分資料，而不用去額外的查詢此刻不需要的內容。因此，這樣做大大增加了查詢的速度，提高了系統的效能，給使用者以非常好的使用者體驗。

以下就是我在項目中做的一個執行個體代碼，供大家參考：

<span style="font-size:24px;"> public ucDataPage()        {            InitializeComponent();            pIndex = 1;            Loaded += delegate            {                //首頁                btnFirst.MouseLeftButtonUp += new MouseButtonEventHandler(btnFirst_Click);                               //上一頁                btnPrev.MouseLeftButtonUp += new MouseButtonEventHandler(btnPrev_Click);                               //下一頁                btnNext.MouseLeftButtonUp += new MouseButtonEventHandler(btnNext_Click);                               //末頁                btnLast.MouseLeftButtonUp += new MouseButtonEventHandler(btnLast_Click);                               btnGo.Click += new RoutedEventHandler(btnGo_Click);            };        }        private DataTable _dt = new DataTable();        //每頁顯示多少條        public static int pageNum = 10;        //當前是第幾頁        public static int pIndex = 1;        //對象        public DataGrid grdList;        //最大頁數        public static int MaxIndex = 1;        //總記錄數        public static int allNum = 0;        public static int Count = 0;        /// <summary>        /// 初始化資料        /// </summary>        /// <param name="grd"></param>        /// <param name="ds"></param>        /// <param name="Num"></param>        public void ShowPages(DataGrid grd, DataTable ds, int Num)        {            if (ds == null || ds.Rows.Count == 0)            {                return;            }            if (ds.Rows.Count == 0)            {                return;            }            DataTable dt = ds;            _dt = dt.Clone();            grdList = grd;            pageNum = Num;            pIndex = 1;            foreach (DataRow r in dt.Rows)            {                _dt.ImportRow(r);            }            SetMaxIndex();            ReadDataTable();            if (MaxIndex > 1)            {                pageGo.IsReadOnly = false;                btnGo.IsEnabled = true;            }        }        /// <summary>        /// 畫資料        /// </summary>        private void ReadDataTable()        {                DisplayPagingInfo();                    }        /// <summary>        /// 畫每頁顯示等資料        /// </summary>        private void DisplayPagingInfo()        {            if (pIndex == 1)            {                btnPrev.IsEnabled = false;                btnFirst.IsEnabled = false;            }            else            {                btnPrev.IsEnabled = true;                btnFirst.IsEnabled = true;            }            if (pIndex == MaxIndex)            {                btnNext.IsEnabled = false;                btnLast.IsEnabled = false;            }            else            {                btnNext.IsEnabled = true;                btnLast.IsEnabled = true;            }            tbkRecords.Text = string.Format("每頁{0}條/共{1}條", pageNum, allNum);            int first = (pIndex - 4) > 0 ? (pIndex - 4) : 1;            int last = (first + 9) > MaxIndex ? MaxIndex : (first + 9);            grid.Children.Clear();            for (int i = first; i <= last; i++)            {                ColumnDefinition cdf = new ColumnDefinition();                grid.ColumnDefinitions.Add(cdf);                TextBlock tbl = new TextBlock();                tbl.Text = i.ToString();                tbl.Style = FindResource("PageTextBlock3") as Style;                tbl.MouseLeftButtonUp += new MouseButtonEventHandler(tbl_MouseLeftButtonUp);                tbl.MouseLeftButtonDown += new MouseButtonEventHandler(tbl_MouseLeftButtonDown);                if (i == pIndex)                {                    tbl.IsEnabled = false;                }                Grid.SetColumn(tbl, grid.ColumnDefinitions.Count - 1);                Grid.SetRow(tbl, 0);                grid.Children.Add(tbl);            }        }        /// <summary>        /// 首頁        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btnFirst_Click(object sender, System.EventArgs e)        {            pIndex = 1;            RaisePageChanged();            ReadDataTable();        }        /// <summary>        /// 首頁        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btnFirst_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)        {            e.Handled = true;        }        /// <summary>        /// 上一頁        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btnPrev_Click(object sender, System.EventArgs e)        {            if (pIndex <= 1)            {                return;            }            pIndex--;            RaisePageChanged();            ReadDataTable();        }        /// <summary>        /// 上一頁        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btnPrev_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)        {            e.Handled = true;            RaisePageChanged();        }        /// <summary>        /// 下一頁        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btnNext_Click(object sender, System.EventArgs e)        {            if (pIndex >= MaxIndex)            {                return;            }            pIndex++;            RaisePageChanged();            ReadDataTable();                     }        /// <summary>        /// 下一頁        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btnNext_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)        {            e.Handled = true;        }        /// <summary>        /// 末頁        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btnLast_Click(object sender, System.EventArgs e)        {            pIndex = MaxIndex;            RaisePageChanged();            ReadDataTable();        }        /// <summary>        /// 末頁        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btnLast_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)        {            e.Handled = true;        }        /// <summary>        /// 設定最大頁        /// </summary>        private void SetMaxIndex()        {            //多少頁            int Pages = Count / pageNum;            if (Count != (Pages * pageNum))            {                if (Count < (Pages * pageNum))                {                    Pages--;                }                else                {                    Pages++;                }                MaxIndex = Pages;                allNum = _dt.Rows.Count;            }        }        /// <summary>        /// 跳轉到多少頁        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btnGo_Click(object sender, RoutedEventArgs e)        {            if (IsNumber(pageGo.Text))            {                int pageNum = int.Parse(pageGo.Text);                if (pageNum > 0 && pageNum <= MaxIndex)                {                    pIndex = pageNum;                    RaisePageChanged();                    ReadDataTable();                }                else if (pageNum > MaxIndex)                {                    pIndex = MaxIndex;                    RaisePageChanged();                    ReadDataTable();                }            }            pageGo.Text = "";        }        /// <summary>        /// 分頁數位點擊觸發事件        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void tbl_MouseLeftButtonUp(object sender, MouseButtonEventArgs e)        {            TextBlock tbl = sender as TextBlock;            if (tbl == null)            {                return;            }            int index = int.Parse(tbl.Text.ToString());            pIndex = index;            if (index > MaxIndex)            {                pIndex = MaxIndex;            }            if (index < 1)            {                pIndex = 1;            }            RaisePageChanged();            ReadDataTable();        }        void tbl_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)        {            e.Handled = true;        }        private static Regex RegNumber = new Regex("^[0-9]+$");        /// <summary>        /// 判斷是否為數字        /// </summary>        /// <param name="valString"></param>        /// <returns></returns>        public static bool IsNumber(string valString)        {            Match m = RegNumber.Match(valString);            return m.Success;        }        #region 欄位、屬性、委託        public delegate void PageChangedEventHandler(object sender, PageChangedEventArgs args);        private PageChangedEventArgs pageChangedEventArgs;        #endregion        /// <summary>        /// 引發分頁變更事件        /// </summary>        private void RaisePageChanged()        {            if (pageChangedEventArgs == null)            {                pageChangedEventArgs = new PageChangedEventArgs(PageChangedEvent, pageNum, pIndex);            }            else            {                pageChangedEventArgs.PageSize = pageNum;                pageChangedEventArgs.PageIndex = pIndex;            }            RaiseEvent(pageChangedEventArgs);                   }        public static readonly RoutedEvent PageChangedEvent = EventManager.RegisterRoutedEvent("PageChanged", RoutingStrategy.Bubble, typeof(PageChangedEventHandler), typeof(ucDataPage));        public class PageChangedEventArgs : RoutedEventArgs        {            public int PageSize { get; set; }            public int PageIndex { get; set; }            public PageChangedEventArgs(RoutedEvent routeEvent, int pageSize, int pageIndex)                : base(routeEvent)            {                PageSize = pageSize;                PageIndex = pageIndex;            }        }        public event PageChangedEventHandler PageChanged        {            add            {                AddHandler(PageChangedEvent, value);            }            remove            {                RemoveHandler(PageChangedEvent, value);            }        }    }}</span>

以上就是主要的計算邏輯，其中真分頁和假分頁的最大區別就是通過獲得當前頁的索引、每頁容量等內容來作為查詢條件來實現查詢功能。另外，一個區別就是需要通過委託來將分頁的功能引入到項目中，最後的幾個方法就是委託的實現。
接下來就是怎麼加入到項目中了，首先是將分頁的功能作為使用者控制項添加到項目中，前台代碼如下：

<span style="font-size:24px;"><pre name="code" class="html"> <my:ucDataPage Grid.Row="1" HorizontalAlignment="Left" Margin="10,0,0,0" x:Name="ucDataPage" PageChanged="dataPager_PageChanged"  VerticalAlignment="Bottom" Width="1114" /></span>

背景委託方法：

<span style="font-size:24px;"><pre name="code" class="csharp">        </span>

<span style="font-size:24px;">private void dataPager_PageChanged(object sender, ucDataPage.PageChangedEventArgs args)        {            #region 查詢資料            try            {                System.Data.DataTable dt = new System.Data.DataTable();                dt = Query(StrWhere, PageSize, ucDataPage.pIndex);                int PIndex = ucDataPage.pIndex;                //dt.DefaultView.Sort = "S_MONITORINGTIME DESC";          // 將查詢到的結果按倒序排列顯示                if (dt.Rows.Count == 0)                {                    MessageBox.Show("您查詢的時間段內沒有合格資訊，請重新選取查詢時間！", "提示");                    dataGrid1.ItemsSource = dt.DefaultView;                    btnQuery.IsEnabled = true;                    return;                }                else                {                    //風速小數位元設定                    for (int i = 0; i < dt.Rows.Count; i++)                    {                        DataRow row = dt.Rows[i];                        if (row["I_ALARMWINDSPEED"] != null || row["I_ALARMWINDSPEED"].ToString() != "")                        {                            row["I_ALARMWINDSPEED"] = Convert.ToDouble(row["I_ALARMWINDSPEED"]).ToString("F1");                        }                        if (Convert.IsDBNull(row["I_WINDSPEED1"]) == false)                        {                            row["I_WINDSPEED1"] = Convert.ToDouble(row["I_WINDSPEED1"]).ToString("F1");                        }                        if (Convert.IsDBNull(row["I_WINDSPEED2"]) == false)                        {                            row["I_WINDSPEED2"] = Convert.ToDouble(row["I_WINDSPEED2"]).ToString("F1");                        }                    }                    dataGrid1.ItemsSource = dt.DefaultView;                    ucDataPage.ShowPages(dataGrid1, dt, PageSize);                    btnQuery.IsEnabled = true;                    ucDataPage.pIndex = PIndex;                }                DT = dt;            }            catch (Exception ex)            {                MessageBox.Show("查詢風速資訊時出現問題，錯誤資訊為：" + ex.Message);                LogInfo.WriteLog(LogInfo.LogLevel.Error, ex.StackTrace + "<br>" + ex.Message + "<br>" + "引發該操作的位置是：" + this.GetType() + "btnQuery_Click()方法!");                return;            }            finally            {                btnQuery.IsEnabled = true;            }            #endregion        }</span>

總結，以上就是真分頁的具體實現方法了，整個過程下來也是比較容易的，我覺得比較複雜的也就算是那個委託的封裝了，實現了委託，也就是實現了真分頁……

<span style="font-family:Arial, Helvetica, sans-serif;font-size:24px;"><span style="white-space: normal;"></span></span>

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

WPF之分頁查詢