一、功能说明
1、能够连接到自己的MySQL数据库,数据库包含至少三张表;
2、使用dataGridView控件显示表中的数据;
3、实现基本crud操作;
二、准备工作
在工具中找到NuGet管理器,添加MySQL.Data这个包
并加上using MySql.Data.MySqlClient; using MySql.Data; using System.Data;
三、界面设计
在Form中右侧加入5个Button控件,并进行功能命名,然后放入dataGridView控件,如下图:
四、核心代码
连接数据库
private void button4_Click(object sender, EventArgs e)
{
string connStr = "server = localhost; user = root; database = school1; port = 3306; password = 123456";
//创建连接数据库的对象
conn = new MySqlConnection(connStr);
try
{
conn.Open();
MessageBox.Show("连接成功");
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
查询
MySqlConnection conn; //连接数据库对象
MySqlDataAdapter mda; //适配器变量
DataSet ds; //临时数据集
private void button1_Click(object sender, EventArgs e)
{
string sql = "select * from course";
mda = new MySqlDataAdapter(sql, conn);
ds = new DataSet();
mda.Fill(ds, "course");
dataGridView1.DataSource = ds.Tables["course"];
conn.Close();
}
增加
private void button2_Click(object sender, EventArgs e)
{
if (mda == null || ds == null)
{
MessageBox.Show("请先导入数据");
return;
}
try
{
string msg = "确实要添加此条数据吗?";
if (1 == (int)MessageBox.Show(msg, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation))
{
MySqlCommandBuilder builder = new MySqlCommandBuilder(mda);
mda.Update(ds, "user");
MessageBox.Show("添加成功", "提示");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "错误信息");
}
}
删除
private void button3_Click(object sender, EventArgs e)
{
int index = dataGridView1.CurrentCell.RowIndex;
int id = (int)dataGridView1.Rows[index].Cells[0].Value;
string sql = "delete from user where id=" + id + "";
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
int i = cmd.ExecuteNonQuery();
if (i < 0)
{
conn.Close();
MessageBox.Show("删除失败");
return;
}
conn.Close();
}
修改
private void button5_Click(object sender, EventArgs e)
{
if (mda == null || ds == null)
{
MessageBox.Show("请先导入数据");
return;
}
try
{
string msg = "确实要修改吗?";
if (1 == (int)MessageBox.Show(msg, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation))
{
MySqlCommandBuilder builder = new MySqlCommandBuilder(mda); //命令生成器。
//适配器会自动更新用户在表上的操作到数据库中
mda.Update(ds, "course");
MessageBox.Show("修改成功", "提示");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "错误信息");
}
}
退出:
private void button6_Click(object sender, EventArgs e)
{
Application.Exit();
}
全部代码:
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 MySql.Data.MySqlClient;
using MySql.Data;
namespace 连接
{
public partial class Form1 : Form
{
MySqlConnection conn; //连接数据库对象
MySqlDataAdapter mda; //适配器变量
DataSet ds; //临时数据集
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "select * from course";
mda = new MySqlDataAdapter(sql, conn);
ds = new DataSet();
mda.Fill(ds, "course");
//显示数据
dataGridView1.DataSource = ds.Tables["course"];
conn.Close();
}
private void button2_Click(object sender, EventArgs e)
{
if (mda == null || ds == null)
{
MessageBox.Show("请先导入数据");
return;
}
try
{
string msg = "确实要添加此条数据吗?";
if (1 == (int)MessageBox.Show(msg, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation))
{
MySqlCommandBuilder builder = new MySqlCommandBuilder(mda);
mda.Update(ds, "user");
MessageBox.Show("添加成功", "提示");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "错误信息");
}
}
private void button4_Click(object sender, EventArgs e)
{
string connStr = "server = localhost; user = root; database = school1; port = 3306; password = 123456";
//创建连接数据库的对象
conn = new MySqlConnection(connStr);
try
{
conn.Open();
MessageBox.Show("连接成功");
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
private void button3_Click(object sender, EventArgs e)
{
int index = dataGridView1.CurrentCell.RowIndex;
int id = (int)dataGridView1.Rows[index].Cells[0].Value;
string sql = "delete from user where id=" + id + "";
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
int i = cmd.ExecuteNonQuery();
if (i < 0)
{
conn.Close();
MessageBox.Show("删除失败");
return;
}
conn.Close();
}
private void button5_Click(object sender, EventArgs e)
{
if (mda == null || ds == null)
{
MessageBox.Show("请先导入数据");
return;
}
try
{
string msg = "确实要修改吗?";
if (1 == (int)MessageBox.Show(msg, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation))
{
MySqlCommandBuilder builder = new MySqlCommandBuilder(mda); //命令生成器。
//适配器会自动更新用户在表上的操作到数据库中
mda.Update(ds, "course");
MessageBox.Show("修改成功", "提示");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "错误信息");
}
}
private void button6_Click(object sender, EventArgs e)
{
Application.Exit();
}
}
}
五、结果截图
三张表
操作后的图(删除了第8行,并修改了第7行)
六、实验小结
这次实验连接时,我使用的vs2022版本无法与MySQL连接,因此放弃在资源服务管理器上操作,而是在NuGet中下载MySQL.Data包,会省去许多步骤。接着要熟练运用各个功能所对应的函数,并且在实验前,要确认好数据库和表的名称,多加练习就可以熟练掌握