前言
最近在开发一款软件,C#连接MySQL数据库,在软件中使用了dataGridView控件,然后在dataGridView控件下面添加一个分页。最开始的时候,我找了一些C#分页的控件,也尝试引入项目,但是或多或少都有一些遗憾。于是自己尝试写了一下这个分页。
过程中踩了不少坑,现在写出来分项一下。
一、软件界面
分页由两个Button、一个TextBox和一个Label 组成。
二、核心代码
1. 全局变量 和 加载函数
int pagenum;//页码
int zts;//总条数
public void pageLode()
{
pagenum = 1;
txt页码.Text = pagenum.ToString();
selectData();
lab总页.Text = (zts / 15 + 1).ToString();
btn上一页.Enabled = false;
btn下一页.Enabled = true;
}
pageLode()函数需要放入窗体Load()方法中。
private void Manager_Load(object sender, EventArgs e)
{
pageLode();
}
2. 计算页码
public void selectData()
{
string conn = myDatabase.Common;
MySqlConnection mycon = new MySqlConnection(conn);
try
{
if (mycon.State == System.Data.ConnectionState.Closed)
{
mycon.Open();
}
string sql_count = "SELECT COUNT(uid) as tally FROM code_custom";
MySqlCommand cmd_count = new MySqlCommand(sql_count, mycon);
MySqlDataReader sread = cmd_count.ExecuteReader();
sread.Read();
zts = Convert.ToInt32(sread["tally"]);
}
catch (MySqlException ex)
{
MessageBox.Show("不能连接服务器,本地验证失败!" + ex.Message);
}
finally
{
mycon.Close();
}
}
该处使用的url网络请求的数据。
3. 上一页
private void Btn上一页_Click(object sender, EventArgs e)
{
btn下一页.Enabled = true;
pagenum = pagenum - 1;
txt页码.Text = pagenum.ToString();
if (pagenum <= 1)
{
txt页码.Text = "1";
btn上一页.Enabled = false;
btn重新载入.Focus();
}
selectPage(pagenum);
}
4. 下一页
private void Btn下一页_Click(object sender, EventArgs e)
{
btn上一页.Enabled = true;
pagenum = pagenum + 1;
txt页码.Text = pagenum.ToString();
if (pagenum == Convert.ToInt32(lab总页.Text))
{
txt页码.Text = lab总页.Text;
btn下一页.Enabled = false;
btn重新载入.Focus();
}
selectPage(pagenum);
}
5. 分页读取数据
public void selectPage(int p)
{
string fenye = (15 * (p - 1)).ToString();
string conn = myDatabase.Common;
MySqlConnection mycon = new MySqlConnection(conn);
try
{
if (mycon.State == System.Data.ConnectionState.Closed)
{
mycon.Open();
}
dataGridView1.Rows.Clear();
string sql_uid = "SELECT * FROM code_custom WHERE uid NOT IN (SELECT uid FROM (SELECT uid FROM code_custom ORDER BY uid desc LIMIT " + fenye + " ) as cd ) ORDER BY uid desc LIMIT 15";
MySqlCommand cmd_uid = new MySqlCommand(sql_uid, mycon);
MySqlDataReader reader = cmd_uid.ExecuteReader();
while (reader.Read())
{
int index = this.dataGridView1.Rows.Add();
this.dataGridView1.Rows[index].Cells[1].Value = reader.GetString("uid");
this.dataGridView1.Rows[index].Cells[2].Value = reader.GetString("ucode");
···
···
···
···
}
}
catch (MySqlException ex)
{
MessageBox.Show("不能连接服务器,本地验证失败!" + ex.Message);
}
finally
{
mycon.Close();
}
}
踩坑的部分
这部分代码最耗时的部分就是写SQL语句。之前我是参考MSSQL写的SQL语句,后来发现MySQL很多地方与MSSQL不一样。
比如MSSQL中有这样的语法:
SELECT TOP 15 * FROM table_name;
但是在MySQL中不存在 “TOP 15” 这样的语法,于是在MySQL中需要这样写:
SELECT * FROM table_name LIMIT 15;
其次,MySQL语法中,不容许在子查询中使用LIMIT,比如:
SELECT * FROM table_name WHERE uid NOT IN
(SELECT uid FROM code_custom ORDER BY uid desc LIMIT 30) ORDER BY uid desc LIMIT 15;
这样的语句在MySQL中就是错误的,于是就会出现这样的提示。
经过优化后,需要给子查询加上一个别名。于是正确的代码如下:
SELECT * FROM code_custom WHERE uid NOT IN
(SELECT uid FROM
(SELECT uid FROM code_custom ORDER BY uid desc LIMIT " + fenye + " ) as cd )
ORDER BY uid desc LIMIT 15"
总起起来,还是自己对SQL语句掌握的不是非常熟练。