C# winform分頁查詢的實現示例
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
到此這篇關於C# winform分頁查詢的實現示例的文章就介紹到這瞭,更多相關C# winform分頁查詢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- 微信小程序實現循環嵌套數據選擇
- C#實現簡單點餐系統
- JavaScript分頁組件使用方法詳解
- Winform項目中TextBox控件DataBindings屬性
- 微信小程序實現購物車選擇規格顏色效果