DataGridView分页与数据库分页查询

     最近一个项目中，需要从数据库查询大量数据（上万条）并在Winform里面用Datagridview展示，故而查找了相关资料并进行研究。

     在.NET中有两种思路实现这种分页式datagridview: 一种是通过纯客户端进行数据分页筛取展示来实现; 另一种是通过结合数据库分页查询来实现.

1. 客户端分页筛选式DataGridView

    在Web程序中，Asp.NET提供了Web的分页式DataGridView控件，显然web数据传输限制了通信数据量，html和js限制了如winform中的许多高级特性，这恐怕也是其不得不提供分页的原因。然而在Form程序中并没有这种实现，相反却提供了许多让人爱不释手的特性，可唯独缺乏了这种分页机制。

    为什么要分页。可能有人会觉得数据量增大时，通信的代价远大于较短的窗体构建过程，然而事实于此相反，当每行记录字段较为复杂时（比如包括图片，bool，字符串）时，datagridview构建的窗体cell将包含图片、复选框、单选框、文本框，笔者实测上千条记录就能导致3-5s以上的窗体构建过程，这段时间窗体主线程将die在这了。退一步讲，就算记录字段较为简单，都是文本集合，然而上万条记录仍将导致数秒中的响应时间。可见大数量数据构建分页的必要性。

    [1]中展示了一个简洁易行的datagridview分页方案。其效果如下图所示，其思路大概如下：通过对DataTable的行Rows以设定的每页大小进行筛选来构成新的某页数据的DataTable，并把其绑定到Bindingsource上来控制BindingNavigate上面的导航条和DataGridView的展现。其实现了上页，下页，首页，尾页的功能，当然也可以实现跳转到某页的功能（这并不复杂）。最后为了代码的移植性和易用性，可以把两个控件合一起做成一个控件。具体结果及代码张贴如下：

 1 // 1、定义几个所需的公有成员：

      int pageSize = 0;     //每页显示行数
      int nMax = 0;         //总记录数
      int pageCount = 0;    //页数＝总记录数/每页显示行数
      int pageCurrent = 0;   //当前页号
      int nCurrent = 0;      //当前记录行
      DataSet ds = new DataSet();
      DataTable dtInfo = new DataTable();

//2、在窗体载入事件中，从数据源读取记录到DataTable中：

      string strConn = "SERVER=127.0.0.1;DATABASE=NORTHWIND;UID=SA;PWD=ULTRATEL";   //数据库连接字符串
      SqlConnection conn = new SqlConnection(strConn);
      conn.Open();
      string strSql = "SELECT * FROM CUSTOMERS";
      SqlDataAdapter sda = new SqlDataAdapter(strSql,conn);
      sda.Fill(ds,"ds");
      conn.Close();
      dtInfo = ds.Tables[0];
      InitDataSet();
  
//3、用当前页面数据填充DataGridView

      private void InitDataSet()
      {
          pageSize = 20;      //设置页面行数
          nMax = dtInfo.Rows.Count;
          pageCount=(nMax/pageSize);    //计算出总页数
          if ((nMax % pageSize) > 0) pageCount++;
          pageCurrent = 1;    //当前页数从1开始
          nCurrent = 0;       //当前记录数从0开始
          LoadData();
       }

      private void LoadData()
      {
          int nStartPos = 0;   //当前页面开始记录行
          int nEndPos = 0;     //当前页面结束记录行
          DataTable dtTemp = dtInfo.Clone();   //克隆DataTable结构框架

          if (pageCurrent == pageCount)
          {
              nEndPos = nMax;
          }
          else
          {
               nEndPos = pageSize * pageCurrent;
          }

          nStartPos = nCurrent;
          lblPageCount.Text = pageCount.ToString();
          txtCurrentPage.Text = Convert.ToString(pageCurrent);
  

          //从元数据源复制记录行
          for (int i = nStartPos; i < nEndPos; i++)
          {
              dtTemp.ImportRow(dtInfo.Rows[i]);
              nCurrent++;
          }
          bdsInfo.DataSource = dtTemp;
          bdnInfo.BindingSource = bdsInfo;
          dgvInfo.DataSource = bdsInfo;
     }

    //   4、菜单响应事件：
 
    private void bdnInfo_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
    {
         if (e.ClickedItem.Text == "关闭")
          {
             this.Close();
          }
         if (e.ClickedItem.Text == "上一页")
          {
              pageCurrent--;
              if (pageCurrent <= 0)
              {
                 MessageBox.Show("已经是第一页，请点击“下一页”查看！");
                 return;
              }
              else
              {
                 nCurrent = pageSize * (pageCurrent - 1);
              }
              LoadData();
           }
          if (e.ClickedItem.Text == "下一页")
          {
              pageCurrent++;
              if (pageCurrent > pageCount)
              {
                  MessageBox.Show("已经是最后一页，请点击“上一页”查看！");
                  return;
               }
               else
              { 
                  nCurrent=pageSize*(pageCurrent-1);
              }
              LoadData();
           }
     }

2. 数据库分页查询式Datagridview

        解决了大量数据客户端Datagridview显示后，大大缩小了反应的时间。然而随着数据量的增大，到上百万条，通信数据量将大大提高，造成的数据读取延时较为明显。当然这还是要通过分页解决，毕竟用户根本不需要那么多数据同时展现，他也看不过来，那么仅有限的数据量是有必要的，太大的数据量相信大多数人会通过专门的查找选项（毕竟有用的数据有限）。这时，显然仍通过客户端筛取的方法是不可行的，数据库分页查询势在必行，需要将上下页及跳转的导航结合数据库的查询。

2.1      首先来看数据库的分页查询

        (1)  [1]文中介绍了几种适用的SQL Server分页查询的方法，思路主要有两种。一种是通过降序和升序结合来查询某段的数据；另一种是通过ROW_NUMER()函数来生成排序号，通过该排序号进行筛选数据。这两种思想分别体现在文中第4种和第5种方案。正如作者推荐，第5种方案更可取，当数据字段没有明显排序规律，或者想进一步操作时，合适和排序号row_number机制更佳。针对该函数，可参考[4]中SQL2005四个排名函数（row_number、rank、dense_rank和ntile）的比较。需要注意的是，正如[4]中所言，row_number依赖于over子句的排序，当存在排序字段相同的记录时，记录的顺序不定，所以多次分页查询，相同排序字段的记录顺序不定。

         (2) [2]文中介绍了Winform中datagridview大数量查询分页显示，微软的解决办法。代码张贴如下：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

//Winform datagridview 大数量查询分页显示 微软的解决办法
namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        // WinForm上的控件
        Button prevBtn = new Button();
        Button nextBtn = new Button();
        Button firstBtn = new Button();
        Button lastBtn = new Button();
        static DataGrid myGrid = new DataGrid();
        static Label pageLbl = new Label();
        // 分页的变量
        static int pageSize = 4; // 每页显示多少
        static int leftpageSiz; // 分页余数
        static int totalPages = 0; // 总共页数
        static int currentPage = 0; // 当前页数.
        static string firstVisibleCustomer = ""; // First customer on page to determine location for move previous.
        static string lastVisibleCustomer = ""; // Last customer on page to determine location for move next.
        // DataSet to bind to DataGrid.
        static DataTable custTable;
        // Initialize connection to database and DataAdapter.
        static SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
        static SqlDataAdapter custDA = new SqlDataAdapter("", nwindConn);
        static SqlCommand selCmd = custDA.SelectCommand;

        public Form1()
        {
            InitializeComponent();

            // Initialize controls and add to form.
            this.ClientSize = new Size(360, 274);
            this.Text = "NorthWind Data";
            myGrid.Size = new System.Drawing.Size(729, 240);
            myGrid.Dock = System.Windows.Forms.DockStyle.Top;
            myGrid.AllowSorting = true;
            myGrid.CaptionText = "NorthWind Customers";
            myGrid.ReadOnly = true;
            myGrid.AllowNavigation = false;
            myGrid.PreferredColumnWidth = 150;

            firstBtn.Text = "First";
            firstBtn.Size = new Size(48, 24);
            firstBtn.Location = new Point(22, 240);
            firstBtn.Click += new EventHandler(First_OnClick);

            prevBtn.Text = "Prev";
            prevBtn.Size = new Size(48, 24);
            prevBtn.Location = new Point(92, 240);
            prevBtn.Click += new EventHandler(Prev_OnClick);

            nextBtn.Text = "Next";
            nextBtn.Size = new Size(48, 24);
            nextBtn.Location = new Point(160, 240);
            nextBtn.Click += new EventHandler(Next_OnClick);

            lastBtn.Text = "Last";
            lastBtn.Size = new Size(48, 24);
            lastBtn.Location = new Point(230, 240);
            lastBtn.Click += new EventHandler(Last_OnClick);


            pageLbl.Text = "没有记录";
            pageLbl.Size = new Size(130, 16);
            pageLbl.Location = new Point(300, 244);
            this.Controls.Add(myGrid);
            this.Controls.Add(prevBtn);
            this.Controls.Add(firstBtn);
            this.Controls.Add(nextBtn);
            this.Controls.Add(lastBtn);
            this.Controls.Add(pageLbl);

            // 获取第一页数据
            GetData("Default");
            DataView custDV = new DataView(custTable, "", "ID", DataViewRowState.CurrentRows);
            myGrid.SetDataBinding(custDV, "");

        }
        public static void First_OnClick(object sender, EventArgs args)
        {
            GetData("First");
        }
        public static void Prev_OnClick(object sender, EventArgs args)
        {
            GetData("Previous");
        }
        public static void Next_OnClick(object sender, EventArgs args)
        {
            GetData("Next");
        }
        public static void Last_OnClick(object sender, EventArgs args)
        {
            GetData("Last");
        }
        private void Form1_Load(object sender, EventArgs e)
        {

        }
        public static void GetData(string direction)
        {
            // Create SQL statement to return a page of records.
            selCmd.Parameters.Clear();
            switch (direction)
            {
                case "First":
                    selCmd.CommandText = "SELECT TOP " + pageSize + " * FROM Customers ";
                    break;

                case "Next":
                    selCmd.CommandText = "SELECT TOP " + pageSize + " * FROM Customers " +
                    "WHERE ID > @ID ORDER BY ID";
                    selCmd.Parameters.Add("@ID", SqlDbType.VarChar, 5).Value = lastVisibleCustomer;
                    break;

                case "Previous":
                    selCmd.CommandText = "SELECT TOP " + pageSize + " * FROM Customers " +
                    "WHERE ID < @ID ORDER BY ID DESC";
                    selCmd.Parameters.Add("@ID", SqlDbType.VarChar, 5).Value = firstVisibleCustomer;
                    break;

                case "Last":
                    selCmd.CommandText = "SELECT TOP " + leftpageSiz + " * FROM Customers ORDER BY ID DESC";
                    break;

                default:
                    selCmd.CommandText = "SELECT TOP " + pageSize + " * FROM Customers ORDER BY ID";

                    // Determine total pages. 
                    SqlCommand totCMD = new SqlCommand("SELECT Count(*) FROM Customers", nwindConn);
                    nwindConn.Open();
                    int totalRecords = (int)totCMD.ExecuteScalar();
                    nwindConn.Close();
                    totalPages = (int)Math.Ceiling((double)totalRecords / pageSize);
                    if ((totalRecords % pageSize) == 0)
                    {
                        leftpageSiz = pageSize;
                    }
                    else
                    {
                        leftpageSiz = totalRecords % pageSize;
                    }
                    break;
            }
            // Fill a temporary table with query results. 
            DataTable tmpTable = new DataTable("Customers");
            int recordsAffected = custDA.Fill(tmpTable);
            // If table does not exist, create it. 
            if (custTable == null)
                custTable = tmpTable.Clone();
            // Refresh table if at least one record returned. 
            if (recordsAffected > 0)
            {
                switch (direction)
                {
                    case "First":
                        currentPage = 1;
                        break;
                    case "Next":
                        currentPage++;
                        break;
                    case "Previous":
                        currentPage--;
                        break;
                    case "Last":
                        currentPage = totalPages;
                        break;
                    default:
                        currentPage = 1;
                        break;
                }
                pageLbl.Text = "Page " + currentPage + " of " + totalPages;
                // Clear rows and add new results. 
                custTable.Rows.Clear();
                foreach (DataRow myRow in tmpTable.Rows)
                    custTable.ImportRow(myRow);
                // Preserve first and last primary key values. 
                DataRow[] ordRows = custTable.Select("", "ID ASC");
                firstVisibleCustomer = ordRows[0][0].ToString();
                lastVisibleCustomer = ordRows[custTable.Rows.Count - 1][0].ToString();
            }
        } 

    }
}

        显而易见，这种方法效率极高，因为只对一个字段进行排序筛选。但同时，这也存在极大的缺点：

• 只能依据一个排序字段，如果有多个字段需要排序则无能为力
• 无法提供跳转的功能，因为仅靠通过上次起始ID来进行上页和下页的查询，但无法指定查询哪一页。

        (3) 我喜欢的两种: 正如(1)所言, [2]中的4方案和5方案是极佳选择

2.2 DataGridView的实现

     由前文可知，2.1中（2）的方法提供了一种简单高效的分页查询方法， 但其缺点也显而易见，不适宜更高级的查询需求。同时将显示指定SQL查询字符串，无法构造通用的DataGridView控件，使得控件的可移植性受限。下文将介绍一种可行的方案（未完待续）。

参考文献:

[1]  DataGridView分页功能的实现, http://www.cnblogs.com/kevin-top/archive/2010/01/05/1639448.html

[2]  高效的SQLSERVER分页查询， http://www.jb51.net/article/35213.htm

[3] Winform datagridview 大数量查询分页显示 微软的解决办法，http://bbs.csdn.net/topics/320194542

[4]SQL2005四个排名函数（row_number、rank、dense_rank和ntile）的比较,   ,http://www.cnblogs.com/xhyang110/archive/2009/10/27/1590448.html