1、功能需求
本实例将通过c# winform实现简单的分页功能，需要的基础知识有SQL语句，c#语言基础以及c# winform的一些简单知识。
2、界面设计
这是一个简单的分页查询的界面，可以输入任意字段进行查询，这四个字段在数据准备会提到，整体界面如图1所示。
图1
中间显示是一个DataGridView，编辑好列和id，SortMode选择Automatic，意思是所有列自动铺满DataGridView，如图2所示。
图2
3、数据准备
本实例涉及到删查改，因此要有数据表以及对数据表进行操作的代码。数据库表非常简单，如图3所示，分别有对应四个字段。
图3
later_back模型类对应数据库操作类代码如下：
| using MySql.Data.MySqlClient; | |
| using System; | |
| using System.Collections.Generic; | |
| using System.Data; | |
| using System.Linq; | |
| using System.Text; | |
| using System.Threading.Tasks; | |
| using System.Windows.Forms; | |
| using WindowsFormsApp1.Bean; | |
| namespace WindowsFormsApp1.SqlHelper | |
| { | |
| class LaterBackHelper | |
| { | |
| public LaterBackHelper(){} | |
| public void insert(LaterBack laterBack) | |
| { | |
| string sql = "insert into later_back(dormitory_id,student_no,time,reason) values('" + laterBack.Dormitory_id + "','" + laterBack.Student_no + "'," + "'" + laterBack.Time + "'," | |
| + "'" + laterBack.Reason + "')"; | |
| try | |
| { | |
| int iRet = SqlHelperBase.ExecuteSql(sql); | |
| if (iRet > 0) | |
| { | |
| MessageBox.Show("新增成功", "系统提示"); | |
| } | |
| else | |
| { | |
| MessageBox.Show("新增失败", "系统提示"); | |
| } | |
| } | |
| catch (Exception) | |
| { | |
| throw; | |
| } | |
| } | |
| public int update(LaterBack laterBack) | |
| { | |
| string sql = "update later_back set reason='" + laterBack.Reason + "' where student_no= '" + laterBack.Student_no + "' and" | |
| + " time = '" + laterBack.Time + "' and " + " dormitory_id= '" + laterBack.Dormitory_id + "'"; | |
| try | |
| { | |
| int iRet = SqlHelperBase.ExecuteSql(sql);//这里返回的是受影响的行数，为int值。可以根据返回的值进行判断是否插入成功。 | |
| if (iRet > 0) | |
| { | |
| MessageBox.Show("修改成功", "系统提示"); | |
| } | |
| else | |
| { | |
| MessageBox.Show("修改失败", "系统提示"); | |
| } | |
| return iRet; | |
| } | |
| catch (Exception) | |
| { | |
| throw; | |
| } | |
| } | |
| public int delete(LaterBack laterBack) | |
| { | |
| string sql = "delete from later_back where dormitory_id='" + laterBack.Dormitory_id + "' and" + " student_no= '" + laterBack.Student_no + "' and" | |
| + " time = '" + laterBack.Time + "' and" + " reason= '" + laterBack.Reason+"'"; | |
| try | |
| { | |
| int iRet = SqlHelperBase.ExecuteSql(sql);//这里返回的是受影响的行数，为int值。可以根据返回的值进行判断是否插入成功。 | |
| if (iRet > 0) | |
| { | |
| MessageBox.Show("删除成功", "系统提示"); | |
| } | |
| else | |
| { | |
| MessageBox.Show("删除失败", "系统提示"); | |
| } | |
| return iRet; | |
| } | |
| catch (Exception) | |
| { | |
| throw; | |
| } | |
| } | |
| public List<LaterBack> getAllLaterBacks() | |
| { | |
| List<LaterBack> laterBacks= new List<LaterBack>(); | |
| string sql = "select * from later_back"; | |
| MySqlDataReader mySqlDataReader = SqlHelperBase.ExecuteReader(sql); | |
| while (mySqlDataReader.Read()) | |
| { | |
| LaterBack laterBack = new LaterBack(int.Parse(mySqlDataReader[0].ToString()), int.Parse(mySqlDataReader[1].ToString()), mySqlDataReader[2].ToString(), | |
| mySqlDataReader[3].ToString()); | |
| laterBacks.Add(laterBack); | |
| } | |
| mySqlDataReader.Close(); | |
| return laterBacks; | |
| } | |
| public List<LaterBack> getAllLaterBacks(int student_no) | |
| { | |
| List<LaterBack> laterBacks= new List<LaterBack>(); | |
| string sql = "select * from later_back where student_no ='" + student_no + "'"; | |
| MySqlDataReader mySqlDataReader = SqlHelperBase.ExecuteReader(sql); | |
| while (mySqlDataReader.Read()) | |
| { | |
| LaterBack laterBack = new LaterBack(int.Parse(mySqlDataReader[0].ToString()), int.Parse(mySqlDataReader[1].ToString()), mySqlDataReader[2].ToString(), | |
| mySqlDataReader[3].ToString()); | |
| laterBacks.Add(laterBack); | |
| } | |
| mySqlDataReader.Close(); | |
| return laterBacks; | |
| } | |
| public DataSet getAllDataSet() | |
| { | |
| string sql = "select * from later_back"; | |
| return SqlHelperBase.GetDataSet(sql); | |
| } | |
| //模糊查询 | |
| public DataSet getDataSet(LaterBack laterBack) | |
| { | |
| string sql=""; | |
| if(laterBack.Student_no != -1) | |
| { | |
| if (laterBack.Dormitory_id != -1) sql = "select * from later_back where dormitory_id like '%" + laterBack.Dormitory_id | |
| + "%' and student_no like '%" + laterBack.Student_no + "%' and time like '%" + laterBack.Time | |
| + "%' and reason like '%" + laterBack.Reason + "%'"; | |
| else sql = "select * from later_back where student_no like '%" + laterBack.Student_no + "%' and time like '%" + laterBack.Time | |
| + "%' and reason like '%" + laterBack.Reason + "%'"; | |
| } | |
| else | |
| { | |
| if (laterBack.Dormitory_id != -1) sql = "select * from later_back where dormitory_id like '%" + laterBack.Dormitory_id | |
| + "%' and time like '%" + laterBack.Time+ "%' and reason like '%" + laterBack.Reason + "%'"; | |
| else sql = "select * from later_back where time like '%" + laterBack.Time | |
| + "%' and reason like '%" + laterBack.Reason + "%'"; | |
| } | |
| return SqlHelperBase.GetDataSet(sql); | |
| } | |
| } | |
| } |
4、源码实现
实现分页时，我的思想是，在每次查询时得到一个dataset1，然后在分页时根据一个from_index和一个end_index去得到当前页面要显示的dataset2，用dataset2来刷新当前要显示的数据，然后在dataset2里头支持行数据的修改与删除，可能从这么写不大合适，但是能完成分页的功能，仅供参考。源码如下：
| using MySql.Data.MySqlClient; | |
| using System; | |
| using System.Collections.Generic; | |
| using System.ComponentModel; | |
| using System.Data; | |
| using System.Drawing; | |
| using System.Linq; | |
| using System.Text; | |
| using System.Threading.Tasks; | |
| using System.Windows.Forms; | |
| using WindowsFormsApp1.Bean; | |
| using WindowsFormsApp1.CacheUtil; | |
| using WindowsFormsApp1.SqlHelper; | |
| namespace WindowsFormsApp1 | |
| { | |
| public partial class StudentLateBackPanelRightSelect : Form | |
| { | |
| private LaterBackHelper laterBackHelper; | |
| private string []page_counts = { "5","6","7","8","9","10","15","20"}; | |
| private int total_page, page_step, current_page_index, total_count; | |
| private DataSet dataSet; | |
| public StudentLateBackPanelRightSelect() | |
| { | |
| laterBackHelper = new LaterBackHelper(); | |
| dataSet = laterBackHelper.getAllDataSet(); | |
| total_count = dataSet.Tables[0].Rows.Count; | |
| page_step = 5; | |
| total_page = Convert.ToInt16(Math.Ceiling(Convert.ToDouble(total_count) / page_step)); | |
| InitializeComponent(); | |
| initView(GetViewDataSet(dataSet,1,5)); | |
| comboBox_page_count.Items.AddRange(page_counts); | |
| comboBox_page_count.SelectedIndex = 0; | |
| label_all_item_count.Text = total_count.ToString(); | |
| label_page_count.Text = total_page.ToString(); | |
| } | |
| //每次点击时刷新全局数据 | |
| private void initDataPage(int total_count,int page_step, int current_page_index) | |
| { | |
| this.total_count = total_count; | |
| this.page_step = page_step; | |
| this.total_page = Convert.ToInt16(Math.Ceiling(Convert.ToDouble(total_count) / page_step)); | |
| this.current_page_index = current_page_index; | |
| } | |
| //初始化界面并加载数据 | |
| private void initView(DataSet ds) | |
| { | |
| dataGridView.AutoGenerateColumns = false; | |
| dataGridView.DataSource = ds.Tables[0]; | |
| this.dataGridView.Columns["dor_id"].DataPropertyName = ds.Tables[0].Columns[0].ToString(); | |
| this.dataGridView.Columns["stu_id"].DataPropertyName = ds.Tables[0].Columns[1].ToString(); | |
| this.dataGridView.Columns["time"].DataPropertyName = ds.Tables[0].Columns[2].ToString(); | |
| this.dataGridView.Columns["reason"].DataPropertyName = ds.Tables[0].Columns[3].ToString(); | |
| } | |
| private void button_select_Click(object sender, EventArgs e) | |
| { | |
| string stu_id = textBox_stu_id.Text, dor_id = textBox_dor_id.Text, time = textBox_time.Text, reason = textBox_reason.Text; | |
| if(stu_id.Equals("")&& dor_id.Equals("") && time.Equals("") && reason.Equals("")) | |
| { | |
| return; | |
| } | |
| if (dor_id.Equals("")) dor_id = "-1"; | |
| if (stu_id.Equals("")) stu_id = "-1"; | |
| LaterBack laterBack = new LaterBack(int.Parse(dor_id), int.Parse(stu_id), time, reason); | |
| dataSet.Clear(); | |
| dataSet = laterBackHelper.getDataSet(laterBack); | |
| initDataPage(dataSet.Tables[0].Rows.Count, page_step, 1); | |
| initView(GetViewDataSet(dataSet, 1, page_step)); | |
| label_page_count.Text = total_page.ToString(); | |
| label_page_range.Text = "1-" + page_step.ToString(); | |
| label_all_item_count.Text = total_count.ToString(); | |
| } | |
| //只能输入数字 | |
| private void only_num_press(object sender, KeyPressEventArgs e) | |
| { | |
| if (!(Char.IsNumber(e.KeyChar)) && e.KeyChar != (char)8) | |
| { | |
| e.Handled = true; | |
| } | |
| } | |
| //对行数据进行删除或修改操作 | |
| private void dataGridView_CellClick(object sender, DataGridViewCellEventArgs e) | |
| { | |
| string action = dataGridView.Columns[e.ColumnIndex].Name;//操作类型 | |
| var cells = dataGridView.Rows[e.RowIndex].Cells; | |
| LaterBack laterBack = new LaterBack(int.Parse(cells[0].Value.ToString()), int.Parse(cells[1].Value.ToString()), cells[2].Value.ToString(), cells[3].Value.ToString()); | |
| switch (action) | |
| { | |
| case "update": | |
| //获取相应列的数据ID,弹出加载了该ID数据详细信息的Form,用以修改 | |
| StudentLateBackPanelRightUpdate studentLateBackPanelRightUpdate = new StudentLateBackPanelRightUpdate(laterBack); | |
| studentLateBackPanelRightUpdate.Show(); | |
| break; | |
| case "delete": | |
| if (MessageBox.Show("确定删除这行数据吗?", "删除提示", MessageBoxButtons.OKCancel) == DialogResult.OK) | |
| { | |
| //获取相应列的数据ID,删除此数据记录 | |
| int result = laterBackHelper.delete(laterBack); | |
| if(result > 0) | |
| { | |
| //dataSet.Clear(); | |
| dataSet = laterBackHelper.getAllDataSet(); | |
| initDataPage(dataSet.Tables[0].Rows.Count,page_step,1); | |
| initView(GetViewDataSet(dataSet,1,page_step)); | |
| label_page_count.Text = total_page.ToString(); | |
| label_page_range.Text = "1-" + page_step.ToString(); | |
| label_all_item_count.Text = total_count.ToString(); | |
| } | |
| } | |
| break; | |
| default: | |
| break; | |
| } | |
| } | |
| //获取要显示的数据源 | |
| public DataSet GetViewDataSet(DataSet a_ds,int from_index,int end_index) | |
| { | |
| //首先先声明一个DataSet对象和一个DataTable对象 | |
| DataSet l_ds = new DataSet(); | |
| DataTable l_dt = new DataTable(); | |
| //构建DataTable对象的列值 | |
| l_dt.Columns.Add("dor_id");//这些列名就是返回的DataSet的列名，可以随意添加 | |
| l_dt.Columns.Add("stu_id"); | |
| l_dt.Columns.Add("time"); | |
| l_dt.Columns.Add("reason"); | |
| //遍历传进来的DataSet的值，并对DataTable进行赋值操作 | |
| for (int i = from_index - 1; i < end_index ; i++) | |
| { | |
| DataRow dr = l_dt.NewRow();//首先新增一行，然后对其进行赋值 | |
| dr["dor_id"] = a_ds.Tables[0].Rows[i][0].ToString().Trim(); | |
| dr["stu_id"] = a_ds.Tables[0].Rows[i][1].ToString().Trim(); | |
| dr["time"] = a_ds.Tables[0].Rows[i][2].ToString().Trim(); | |
| dr["reason"] = a_ds.Tables[0].Rows[i][3].ToString().Trim(); | |
| l_dt.Rows.Add(dr);//这里一定要add进去 | |
| } | |
| l_ds.Tables.Add(l_dt);//这里也不能忘记 | |
| return l_ds; | |
| } | |
| //combobox更改触发事件 | |
| private void combobox_selected_listen(object sender, EventArgs e) | |
| { | |
| page_step = int.Parse(comboBox_page_count.SelectedItem.ToString()); | |
| textBox_page_count.Text = 1.ToString(); | |
| label_page_range.Text = "1-" + page_step.ToString(); | |
| total_page = Convert.ToInt16( Math.Ceiling(Convert.ToDouble(total_count) / page_step)); | |
| label_page_count.Text = total_page.ToString(); | |
| initView(GetViewDataSet(dataSet, 1, page_step)); | |
| } | |
| //输入页数变化 | |
| private void page_count_change(object sender, EventArgs e) | |
| { | |
| if (textBox_page_count.Text.Equals("")) return; | |
| if (int.Parse(textBox_page_count.Text) < 1) | |
| { | |
| MessageBox.Show("输入页数不能小于1", "系统提示"); | |
| return; | |
| } | |
| if (int.Parse(textBox_page_count.Text) > total_page) | |
| { | |
| MessageBox.Show("输入页数超过总页数", "系统提示"); | |
| return; | |
| } | |
| current_page_index = int.Parse(textBox_page_count.Text); | |
| var view_range = (current_page_index - 1)* page_step ; | |
| if (view_range + page_step < total_count) { | |
| initView(GetViewDataSet(dataSet, view_range + 1, view_range + page_step)); | |
| label_page_range.Text = (view_range + 1).ToString() + "-" + (view_range + page_step).ToString(); | |
| } | |
| else { | |
| initView(GetViewDataSet(dataSet, view_range + 1, total_count)); | |
| label_page_range.Text = (view_range + 1).ToString() + "-" + total_count.ToString(); | |
| } | |
| } | |
| private void button_first_page_Click(object sender, EventArgs e) | |
| { | |
| current_page_index = 1; | |
| var view_range = (current_page_index - 1) * page_step; | |
| initView(GetViewDataSet(dataSet, view_range + 1, view_range + page_step)); | |
| label_page_range.Text = (view_range + 1).ToString() + "-" + page_step.ToString(); | |
| textBox_page_count.Text = current_page_index.ToString(); | |
| } | |
| private void button_previous_page_Click(object sender, EventArgs e) | |
| { | |
| if (current_page_index == 1) return; | |
| current_page_index -= 1; | |
| var view_range = (current_page_index - 1) * page_step; | |
| initView(GetViewDataSet(dataSet, view_range + 1, view_range + page_step)); | |
| label_page_range.Text = (view_range + 1).ToString() + "-" + (view_range + page_step).ToString(); | |
| textBox_page_count.Text = current_page_index.ToString(); | |
| } | |
| private void button_next_page_Click(object sender, EventArgs e) | |
| { | |
| if (current_page_index == total_page) return; | |
| current_page_index += 1; | |
| var view_range = (current_page_index - 1) * page_step; | |
| if (view_range + page_step < total_count) | |
| { | |
| initView(GetViewDataSet(dataSet, view_range + 1, view_range + page_step)); | |
| label_page_range.Text = (view_range + 1).ToString() + "-" + (view_range + page_step).ToString(); | |
| } | |
| else | |
| { | |
| initView(GetViewDataSet(dataSet, view_range + 1, total_count)); | |
| label_page_range.Text = (view_range + 1).ToString() + "-" + total_count.ToString(); | |
| } | |
| textBox_page_count.Text = current_page_index.ToString(); | |
| } | |
| private void button_last_page_Click(object sender, EventArgs e) | |
| { | |
| current_page_index = total_page; | |
| var view_range = (current_page_index - 1) * page_step; | |
| initView(GetViewDataSet(dataSet, view_range + 1, total_count)); | |
| label_page_range.Text = (view_range + 1).ToString() + "-" + total_count.ToString(); | |
| textBox_page_count.Text = current_page_index.ToString(); | |
| } | |
| } | |
| } |
5、结果
程序运行结果如图4和图5所示，更改每页条数，或者输入页数时都能跳转到相应页面，所有功能都正常，至此，简单的分页功能就实现，如果有任何问题，欢迎给我留言。
图4
图5