（C#）MySql 学习笔记 limit 分页查询实现上下翻页
本帖最后由 Cool_Breeze 于 2021-6-24 15:16 编辑编译方法：
批处理命令
@echo off
csc.exe -lib:D:\GIN\c#\MsqlExample\ -r:mysql.data.dll /t:winexe mysqlFillDataGridView.cs&& mysqlFillDataGridView.exe
pause
源代码：using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using System.Drawing;
using System.Threading;
using MySql.Data;
using MySql.Data.MySqlClient;
class FileHomeForm : Form
{
static void Main()
{
Application.Run(new FileHomeForm());
}
DataGridView DataDgv;
Label TotalLbl;
Label TotalPageLbl;
Label CurrentPageNoLbl;
int CurrentPageNu;
int TotalPageNu;
bool Bind = true;
// 用户名和密码
string connectStr = "server=127.0.0.1;port=3306;database=userinfo;user=root;password=root";
public FileHomeForm()
{
this.Text = "数据库练习";
this.Size = new Size(600, 630);
this.StartPosition = FormStartPosition.CenterScreen;
TotalLbl = new Label();
TotalLbl.Text = "数量：";
TotalLbl.Size = new Size(100, 20);
TotalLbl.Location = new Point(430, 510);
this.Controls.Add(TotalLbl);
TotalPageLbl = new Label();
TotalPageLbl.Text = "页数：";
TotalPageLbl.Size = new Size(100, 20);
TotalPageLbl.Location = new Point(430, 540);
this.Controls.Add(TotalPageLbl);
CurrentPageNoLbl = new Label();
CurrentPageNoLbl.Text = "当页：";
CurrentPageNoLbl.Size = new Size(100, 20);
CurrentPageNoLbl.Location = new Point(430, 570);
this.Controls.Add(CurrentPageNoLbl);
DataDgv = new DataGridView();
DataDgv.Text = "测试";
DataDgv.Size = new Size(585, 500);
DataDgv.Location = new Point(0, 0);
DataDgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
DataDgv.DefaultCellStyle.WrapMode = DataGridViewTriState.False;
// 禁止用户输入行
DataDgv.AllowUserToAddRows = false;
DataDgv.ReadOnly = true;
DataDgv.Anchor = (AnchorStyles.Right | AnchorStyles.Bottom | AnchorStyles.Left | AnchorStyles.Top);
this.Controls.Add(DataDgv);
Button updateBtn = new Button();
updateBtn.Text = "查询数据库";
updateBtn.Size = new Size(100, 40);
updateBtn.Location = new Point(30, 510);
updateBtn.Click += (o, e) =>
{
MySqlConnection conn = new MySqlConnection(connectStr);
try
{
conn.Open();
string sqlCount = "select count(Size) from filesinfo";
MySqlCommand cmd = new MySqlCommand(sqlCount, conn);
object count = cmd.ExecuteScalar();
if (count != null)
{
CurrentPageNu = 1;
TotalLbl.Text = "数量：" + count.ToString();
TotalPageNu = (int)Math.Ceiling(Convert.ToInt32(count) / 100f);
TotalPageLbl.Text = "页数：" + TotalPageNu.ToString();
CurrentPageNoLbl.Text = "当页：" + "1";
}
string sql = "select * from filesinfo limit 100";
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(sql, conn); // 连接器
MySqlCommandBuilder dataCommand = new MySqlCommandBuilder(dataAdapter); // 生成命令
DataSet dataset = new DataSet(); // 数据集
dataAdapter.Fill(dataset, "filesinfo"); // 填充数据集
//恢复默认表格（兼容两者绑定方法）
DataDgv.DataSource = null;
DataDgv.Columns.Clear();
DataDgv.DataSource = dataset;
if (!Bind) Bind = true;
DataDgv.DataMember = "filesinfo";
}
catch (Exception ex){
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
};
this.Controls.Add(updateBtn);
Button upPageBtn = new Button();
upPageBtn.Text = "上一页";
upPageBtn.Size = new Size(100, 40);
upPageBtn.Location = new Point(180, 510);
upPageBtn.Click += (o, e) =>
{
if (CurrentPageNu == 1 || CurrentPageNu == 0)
{
return;
}
CurrentPageNu--;
CurrentPageNoLbl.Text = "当页：" + CurrentPageNu.ToString();
UpdatedDataDgvPage(string.Format("select * from filesinfo limit {0}, 100", (CurrentPageNu-1)*100));
};
this.Controls.Add(upPageBtn);
Button DownPageBtn = new Button();
DownPageBtn.Text = "下一页";
DownPageBtn.Size = new Size(100, 40);
DownPageBtn.Location = new Point(310, 510);
DownPageBtn.Click += (o, e) =>
{
if (CurrentPageNu == TotalPageNu || CurrentPageNu == 0)
{
return;
}
UpdatedDataDgvPage(string.Format("select * from filesinfo limit {0}, 100", CurrentPageNu*100));
CurrentPageNu++;
CurrentPageNoLbl.Text = "当页：" + CurrentPageNu.ToString();
};
this.Controls.Add(DownPageBtn);
}
private void UpdatedDataDgvPage(string sql)
{
// 绑定数据源方法
MySqlConnection conn = new MySqlConnection(connectStr);
DataSet dataset = new DataSet();
try
{
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(sql, conn);
MySqlCommandBuilder dataCommand = new MySqlCommandBuilder(dataAdapter);
dataAdapter.Fill(dataset, "filesinfo");
}
catch (Exception){}
finally
{
conn.Close();
}
// DataDgv.DataSource = new BindingSource();
// dataset.Clear();
DataDgv.DataSource = dataset;
DataDgv.DataMember = "filesinfo";
// 不绑定数据源 DataSet
// MessageBox.Show(sql);
// if (Bind)
// {
// DataDgv.DataSource = null;
// ChangeDataDgvStyle();
// }
// DataDgv.Rows.Clear();
// MySqlConnection conn = new MySqlConnection(connectStr);
// try
// {
// conn.Open(); //初始化一些数据库
// MySqlCommand cmd = new MySqlCommand(sql, conn);
// MySqlDataReader rdr = cmd.ExecuteReader();
// while (rdr.Read())
// {
// var values = new Object;
// rdr.GetValues(values);
// DataDgv.Rows.Add(values);
// }
// }
// catch (Exception e)
// {
// MessageBox.Show(e.Message);
// }
// finally
// {
// conn.Close();
// }
}
private void ChangeDataDgvStyle()
{
// 更改表格数据源后，修改表格信息
Bind = false;
DataDgv.ColumnCount = 4;
DataDgv.Columns.HeaderText = "FileName";
DataDgv.Columns.HeaderText = "ABSPath";
DataDgv.Columns.HeaderText = "LastWriteTime";
DataDgv.Columns.HeaderText = "Size";
}
}
数据库表：
mysql> desc filesinfo;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| FileName | varchar(256) | YES| | NULL | |
| ABSPath | varchar(256) | YES| | NULL | |
| LastWriteTime | datetime | YES| | NULL | |
| Size | bigint(20) | YES| | NULL | |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
数据库信息来源：
class FilesInfo
{
string targetDir;
public FilesInfo(string dir)
{
targetDir = dir;
}
public List<string[]> GetFileInfo()
{
var result = new List<string[]>();
var dir = new DirectoryInfo(targetDir);
foreach (var n in dir.EnumerateFiles("*", SearchOption.AllDirectories))
{
string[] each = new string;
each = n.Name;
each = Path.Combine(n.DirectoryName, n.Name);
each = n.LastWriteTime.ToString("yyyy-MM-dd HH:MM:ss");
each = n.Length.ToString();
result.Add(each);
}
return result;
}
} 谢谢分享,........
页:
[1]