DataGridView分页——分页浏览和分页读取两种实现方法

【目标】实现DataGridView分页

【结果】基本实现：

1.实现分页浏览，这种方法将数据库数据全部读到内存，只是分页显示。

2.实现分页读取：写存储过程，获得以当前页码和页面大小为参数，读取需要的数据并显示。

【条件】了解BindingSource,BindingNavigator,DataGridView控件，会SQL语句写存储过程。

【方法】

1.实现分页浏览：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Drawing;
  6 using System.Linq;
  7 using System.Text;
  8 using System.Windows.Forms;
  9 using System.Data.SqlClient;
 10 using DAL;
 11 
 12 namespace UI
 13 {
 14     public partial class PagingTest : Form
 15     {
 16         int rowsPerPage = 0;  //每页显示行数
 17         int currentPage = 0;    //当前页号
 18         int rowCount = 0;      //当前记录行
 19         bool defaultRowsPerPage=true;    //是否页面大小为默认
 20 
 21         int sumCount = 0;
 22         int pageCount = 0;
 23         DataTable dt = new DataTable();
 24         SQLHelper sqlhelper = null;
 25         public PagingTest()
 26         {
 27             InitializeComponent();
 28             sqlhelper = new SQLHelper();
 29         }
 30 
 31 
 32         private void PagingTest_Load(object sender, EventArgs e)
 33         {
 34             SqlConnection con = new SqlConnection("Data Source=(local);database=PetrolExplorAndDevelopInfoSys;Integrated Security=True");
 35             SqlDataAdapter sda = new SqlDataAdapter("select * from tbTestWellLogInfo",con);
 36             sda.Fill(dt);
 37             InitDataSet();
 38             
 39         }
 40 
 41         private void InitDataSet()
 42         {
 43             if (!defaultRowsPerPage)
 44             {
 45                 rowsPerPage = Convert.ToInt32(textBox1.Text);
 46             }
 47             else rowsPerPage = 2;   //设置页面行数
 48             sumCount = dt.Rows.Count;   //总行数
 49             pageCount=(sumCount/rowsPerPage);   //计算出总页数
 50             if ((sumCount % rowsPerPage) > 0) pageCount++;
 51 
 52             currentPage = 1;        //当前页数从1开始
 53             rowCount = 0;
 54             LoadData();
 55         }
 56 
 57         private void LoadData()
 58         {
 59             int Start = 0; //当前页面开始记录行
 60             int End=0;    //当前页面结束记录行
 61             DataTable tableClone=dt.Clone();
 62             //设置按钮的可用性
 63             if(currentPage<=1)
 64             {
 65                 toolStripLabel1.Enabled = false;
 66                 toolStripLabel2.Enabled = false;
 67             }
 68             else
 69             {
 70                 toolStripLabel1.Enabled = true;
 71                 toolStripLabel2.Enabled = true;
 72             }
 73             if(currentPage>=pageCount)
 74             {
 75                 toolStripLabel3.Enabled = false;
 76                 toolStripLabel4.Enabled = false;
 77             }
 78             else
 79             {
 80                 toolStripLabel3.Enabled = true;
 81                 toolStripLabel4.Enabled = true;
 82             }
 83             if(currentPage==pageCount)End=sumCount;
 84             else End=rowsPerPage*currentPage;
 85             Start=rowCount;
 86             txtCurrentPage.Text= currentPage.ToString() ;
 87             lblPageCount.Text = "/" + pageCount.ToString();
 88 
 89 
 90             //从元数据源复制记录行
 91             for (int i = Start; i < End; i++)
 92             {
 93                 tableClone.ImportRow(dt.Rows[i]);
 94                 rowCount++;
 95             }
 96             bindingSource1.DataSource = tableClone;
 97             bindingNavigator1.BindingSource = bindingSource1;
 98             dataGridView1.DataSource = bindingSource1;
 99         }
100 
101         private void bindingNavigator1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
102         {
103             if (e.ClickedItem.Text == "上一页")
104             {
105                 if (currentPage <= 1)
106                 {
107                     return;
108                 }
109                 else
110                 {
111                     currentPage--;
112                     rowCount = rowsPerPage * (currentPage - 1);
113                 }
114                 LoadData();
115             }
116             if (e.ClickedItem.Text == "下一页")
117             {
118                 
119                 if (currentPage>=pageCount)
120                 {
121                     return;
122                 }
123                 else
124                 {
125                     currentPage++;
126                     rowCount=rowsPerPage*(currentPage-1);
127                 }
128                 LoadData();
129             }
130             if (e.ClickedItem.Text == "第一页")
131             {
132                 currentPage = 1;
133                 rowCount = 0;
134                 LoadData();
135             }
136             if (e.ClickedItem.Text == "最后一页")
137             {
138                 currentPage = pageCount;
139                 rowCount=rowsPerPage*(currentPage-1);
140                 LoadData();
141             }
142         }
143 
144         private void txtCurrentPage_KeyDown(object sender, KeyEventArgs e)
145         {
146             if (e.KeyValue == 13)
147             {
148                 currentPage = Convert.ToInt32(txtCurrentPage.Text);
149                 rowCount = rowsPerPage * (currentPage-1);
150                 LoadData();
151             }
152         }
153 
154 
155         private void btnSure_Click(object sender, EventArgs e)
156         {
157             if (Convert.ToInt32(textBox1.Text) > 0 && Convert.ToInt32(textBox1.Text) <= sumCount)
158             {
159                 defaultRowsPerPage = false;
160             }
161             else
162             {
163                 MessageBox.Show("请输入正确数值！");
164             }
165             //PagingTest_Load(sender,e);
166             InitDataSet();
167         }
168 
169     }
170 }

2.实现分页读取：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Drawing;
  6 using System.Linq;
  7 using System.Text;
  8 using System.Windows.Forms;
  9 using System.Data.SqlClient;
 10 using DAL;
 11 
 12 namespace UI
 13 {
 14     public partial class PagingTest2 : Form
 15     {
 16         int rowsPerPage = 0;  //每页显示行数
 17         int currentPage = 0;    //当前页号
 18         bool defaultRowsPerPage = true;
 19 
 20         int sumCount = 0;
 21         int pageCount = 0;
 22         DataTable dt = new DataTable();
 23         SQLHelper sqlhelper = null;
 24         public PagingTest2()
 25         {
 26             InitializeComponent();
 27             sqlhelper = new SQLHelper();
 28 
 29         }
 30 
 31         private void InitDataSet()
 32         {
 33             if (!defaultRowsPerPage)
 34             {
 35                 rowsPerPage = Convert.ToInt32(textBox1.Text);
 36             }
 37             else rowsPerPage = 2;   //设置页面行数
 38             pageCount = (sumCount / rowsPerPage);   //计算出总页数
 39             if ((sumCount % rowsPerPage) > 0) pageCount++;
 40             currentPage = 1;        //当前页数从1开始
 41             LoadData();
 42         }
 43 
 44         private void LoadData()
 45         {
 46             DataTable tempTable = new DataTable();
 47             //设置按钮的可用性
 48             if (currentPage <= 1)
 49             {
 50                 toolStripLabel1.Enabled = false;
 51                 toolStripLabel2.Enabled = false;
 52             }
 53             else
 54             {
 55                 toolStripLabel1.Enabled = true;
 56                 toolStripLabel2.Enabled = true;
 57             }
 58             if (currentPage >= pageCount)
 59             {
 60                 toolStripLabel3.Enabled = false;
 61                 toolStripLabel4.Enabled = false;
 62             }
 63             else
 64             {
 65                 toolStripLabel3.Enabled = true;
 66                 toolStripLabel4.Enabled = true;
 67             }
 68 
 69             txtCurrentPage.Text = currentPage.ToString();
 70             lblPageCount.Text = "/" + pageCount.ToString();
 71 
 72             SqlParameter[] para = new SqlParameter[]{
 73                     new SqlParameter("@pageSize",rowsPerPage),
 74                     new SqlParameter("@currentPage",currentPage)
 75                 };
 76             tempTable = sqlhelper.ExecuteQuery("proc_Paging",para,CommandType.StoredProcedure);
 77             bindingSource1.DataSource = tempTable;
 78             bindingNavigator1.BindingSource = bindingSource1;
 79             dataGridView1.DataSource = bindingSource1;
 80         }
 81 
 82 
 83         private void PagingTest2_Load(object sender, EventArgs e)
 84         {
 85             //求总行数
 86             DataTable tempDt = sqlhelper.ExecuteQuery("select * from tbTestWellLogInfo", CommandType.Text);
 87             sumCount = tempDt.Rows.Count;
 88             InitDataSet();
 89         }
 90 
 91         private void bindingNavigator1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
 92         {
 93             if (e.ClickedItem.Text == "上一页")
 94             {
 95                 if (currentPage <= 1)
 96                 {
 97                     return;
 98                 }
 99                 else
100                 {
101                     currentPage--;
102                 }
103                 LoadData();
104             }
105             if (e.ClickedItem.Text == "下一页")
106             {
107 
108                 if (currentPage >= pageCount)
109                 {
110                     return;
111                 }
112                 else
113                 {
114                     currentPage++;
115                 }
116                 LoadData();
117             }
118             if (e.ClickedItem.Text == "第一页")
119             {
120                 currentPage = 1;
121                 LoadData();
122             }
123             if (e.ClickedItem.Text == "最后一页")
124             {
125                 currentPage = pageCount;
126                 LoadData();
127             }
128         }
129 
130         private void btnSure_Click(object sender, EventArgs e)
131         {
132             defaultRowsPerPage = false;
133             InitDataSet();
134         }
135 
136         private void txtCurrentPage_KeyDown(object sender, KeyEventArgs e)
137         {
138             if (e.KeyValue == 13)
139             {
140                 currentPage = Convert.ToInt32(txtCurrentPage.Text);
141                 LoadData();
142             }
143         }
144 
145     }
146 }

附分页存储过程：proc_Paging

ALTER PROCEDURE [dbo].[proc_Paging]
 @pageSize int,
 @currentPage int
AS
BEGIN
 SET NOCOUNT ON;
    if(@currentPage=1)select top (@pageSize) * from dbo.tbTestWellLogInfo
    else begin
    select top (@pageSize) * from dbo.tbTestWellLogInfo
    where num>
    (select MAX(num) from
    (select top((@currentPage-1)*@pageSize) num from dbo.tbTestWellLogInfo order by num)as T
    )
    order by num
    end
END

附图（表格上有数据显示）：

【可以改善】
输入数值的验证，如输入页面大小时不能输入：0.5，-1等，当然还有其他暂未发现之处。

【总结】

1.SQl语句需要加强，特别是对原理的理解，对时耗的把握，SQL语句也很强大。

2.学会解决问题，学会尝试，并快速搭建解决问题的环境。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/denghuachengle/p/3479194.html