【目标】实现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.学会解决问题,学会尝试,并快速搭建解决问题的环境。