C# winform分頁(yè)查詢的實(shí)現(xiàn)示例
更新時(shí)間：2021年04月26日 10:38:19 作者：一枝韓獨(dú)秀
這篇文章主要介紹了C# winform分頁(yè)查詢的實(shí)現(xiàn)示例，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
1、功能需求
本實(shí)例將通過c# winform實(shí)現(xiàn)簡(jiǎn)單的分頁(yè)功能，需要的基礎(chǔ)知識(shí)有SQL語(yǔ)句，c#語(yǔ)言基礎(chǔ)以及c# winform的一些簡(jiǎn)單知識(shí)。
2、界面設(shè)計(jì)
這是一個(gè)簡(jiǎn)單的分頁(yè)查詢的界面，可以輸入任意字段進(jìn)行查詢，這四個(gè)字段在數(shù)據(jù)準(zhǔn)備會(huì)提到，整體界面如圖1所示。
圖1
中間顯示是一個(gè)DataGridView，編輯好列和id，SortMode選擇Automatic，意思是所有列自動(dòng)鋪滿DataGridView，如圖2所示。
圖2
3、數(shù)據(jù)準(zhǔn)備
本實(shí)例涉及到刪查改，因此要有數(shù)據(jù)表以及對(duì)數(shù)據(jù)表進(jìn)行操作的代碼。數(shù)據(jù)庫(kù)表非常簡(jiǎn)單，如圖3所示，分別有對(duì)應(yīng)四個(gè)字段。
圖3
later_back模型類對(duì)應(yīng)數(shù)據(jù)庫(kù)操作類代碼如下：
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("新增成功", "系統(tǒng)提示");
}
else
{
MessageBox.Show("新增失敗", "系統(tǒng)提示");
}
}
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);//這里返回的是受影響的行數(shù)，為int值。可以根據(jù)返回的值進(jìn)行判斷是否插入成功。
if (iRet > 0)
{
MessageBox.Show("修改成功", "系統(tǒng)提示");
}
else
{
MessageBox.Show("修改失敗", "系統(tǒng)提示");
}
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);//這里返回的是受影響的行數(shù)，為int值?？梢愿鶕?jù)返回的值進(jìn)行判斷是否插入成功。
if (iRet > 0)
{
MessageBox.Show("刪除成功", "系統(tǒng)提示");
}
else
{
MessageBox.Show("刪除失敗", "系統(tǒng)提示");
}
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、源碼實(shí)現(xiàn)
實(shí)現(xiàn)分頁(yè)時(shí)，我的思想是，在每次查詢時(shí)得到一個(gè)dataset1，然后在分頁(yè)時(shí)根據(jù)一個(gè)from_index和一個(gè)end_index去得到當(dāng)前頁(yè)面要顯示的dataset2，用dataset2來刷新當(dāng)前要顯示的數(shù)據(jù)，然后在dataset2里頭支持行數(shù)據(jù)的修改與刪除，可能從這么寫不大合適，但是能完成分頁(yè)的功能，僅供參考。源碼如下：
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();
}
//每次點(diǎn)擊時(shí)刷新全局?jǐn)?shù)據(jù)
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;
}
//初始化界面并加載數(shù)據(jù)
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();
}
//只能輸入數(shù)字
private void only_num_press(object sender, KeyPressEventArgs e)
{
if (!(Char.IsNumber(e.KeyChar)) && e.KeyChar != (char)8)
{
e.Handled = true;
}
}
//對(duì)行數(shù)據(jù)進(jìn)行刪除或修改操作
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":
//獲取相應(yīng)列的數(shù)據(jù)ID,彈出加載了該ID數(shù)據(jù)詳細(xì)信息的Form,用以修改
StudentLateBackPanelRightUpdate studentLateBackPanelRightUpdate = new StudentLateBackPanelRightUpdate(laterBack);
studentLateBackPanelRightUpdate.Show();
break;
case "delete":
if (MessageBox.Show("確定刪除這行數(shù)據(jù)嗎?", "刪除提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
//獲取相應(yīng)列的數(shù)據(jù)ID,刪除此數(shù)據(jù)記錄
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;
}
}
//獲取要顯示的數(shù)據(jù)源
public DataSet GetViewDataSet(DataSet a_ds,int from_index,int end_index)
{
//首先先聲明一個(gè)DataSet對(duì)象和一個(gè)DataTable對(duì)象
DataSet l_ds = new DataSet();
DataTable l_dt = new DataTable();
//構(gòu)建DataTable對(duì)象的列值
l_dt.Columns.Add("dor_id");//這些列名就是返回的DataSet的列名，可以隨意添加
l_dt.Columns.Add("stu_id");
l_dt.Columns.Add("time");
l_dt.Columns.Add("reason");
//遍歷傳進(jìn)來的DataSet的值，并對(duì)DataTable進(jìn)行賦值操作
for (int i = from_index - 1; i < end_index ; i++)
{
DataRow dr = l_dt.NewRow();//首先新增一行，然后對(duì)其進(jìn)行賦值
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進(jìn)去
}
l_ds.Tables.Add(l_dt);//這里也不能忘記
return l_ds;
}
//combobox更改觸發(fā)事件
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));
}
//輸入頁(yè)數(shù)變化
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("輸入頁(yè)數(shù)不能小于1", "系統(tǒng)提示");
return;
}
if (int.Parse(textBox_page_count.Text) > total_page)
{
MessageBox.Show("輸入頁(yè)數(shù)超過總頁(yè)數(shù)", "系統(tǒng)提示");
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、結(jié)果
程序運(yùn)行結(jié)果如圖4和圖5所示，更改每頁(yè)條數(shù)，或者輸入頁(yè)數(shù)時(shí)都能跳轉(zhuǎn)到相應(yīng)頁(yè)面，所有功能都正常，至此，簡(jiǎn)單的分頁(yè)功能就實(shí)現(xiàn)，如果有任何問題，歡迎給我留言。
圖4
圖5
到此這篇關(guān)于C# winform分頁(yè)查詢的實(shí)現(xiàn)示例的文章就介紹到這了,更多相關(guān)C# winform分頁(yè)查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
相關(guān)文章
c# 抓取Web網(wǎng)頁(yè)數(shù)據(jù)分析
通過程序自動(dòng)的讀取其它網(wǎng)站網(wǎng)頁(yè)顯示的信息，類似于爬蟲程序。比方說我們有一個(gè)系統(tǒng)，要提取BaiDu網(wǎng)站上歌曲搜索排名。分析系統(tǒng)在根據(jù)得到的數(shù)據(jù)進(jìn)行數(shù)據(jù)分析。為業(yè)務(wù)提供參考數(shù)據(jù)。2008-11-11
這篇文章主要為大家詳細(xì)介紹了Unity實(shí)現(xiàn)枚舉類型中文顯示，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-02-02
c#項(xiàng)目實(shí)現(xiàn)發(fā)布到服務(wù)器全過程
這篇文章主要介紹了c#項(xiàng)目實(shí)現(xiàn)發(fā)布到服務(wù)器全過程，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-04-04
這篇文章主要為大家詳細(xì)介紹了C#?TrackBar拖動(dòng)條改變滑塊顏色，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-02-02
這篇文章主要介紹了C# 中的委托的相關(guān)資料，幫助初學(xué)者更好的理解和使用c#，感興趣的朋友可以了解下2020-11-11
這篇文章介紹了C#關(guān)鍵字之重寫override，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-04-04
這篇文章介紹了C#中List<T>存放元素的工作機(jī)制，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-08-08
這篇文章主要介紹了C# ToolStrip浮動(dòng)工具欄的制作,可以上/下/左/右?？?，代碼在下面2013-12-12
這篇文章主要介紹了C# 監(jiān)控 Windows 文件夾的方法，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-03-03
c#動(dòng)態(tài)執(zhí)行腳本的3種方式詳解
本文主要介紹了c#動(dòng)態(tài)執(zhí)行腳本的3種方式詳解，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-04-04