一:实现功能
1、请设计一个项目连接到自己的MySQL数据库,数据库包含至少三张表;
2、使用dataGridView控件显示表中的数据;
3、实现基本crud操作;
二:C#连接mysql:
下载驱动:
1.mysql-for-visualstudio-1.2.9.msi Visual Studio 连接MySQL工具
2.mysql-connector-net-8.0.20.msi mysql 数据库.net开发驱动
1.mysql for visual studio(VS连接MySQL工具)是一款visual studio连mysql工具,它可以直接集成到Server Explorer中,为设置新连接和处理数据库对象提供无缝体验,但是想让VS访问到MySQL仅仅mysql for visual studio是不够的,还需要下载mysql connector net。
2.MySQL Connector/Net 是 MySQL 官方的 .NET 驱动程序,MySQL 官方的 .NET 客户端开发包。mysql 数据库最新版dotnet数据库连接驱动。
配置好后,单击视图,点击服务器资源管理器,右击数据连接,点击添加连接
Server name(localhost) 是服务器名称 user name是用户ID Passwords是用户密码
三:添加控件:
在这里的空间为dataGridView和六个button
然后为按钮编写相应的事件。
四:代码:
首先添加三个全局定义:
MySqlConnection conn; //连接数据库对象
MySqlDataAdapter mda; //适配器变量
DataSet ds; //临时数据集
连接数据库:
private void button1_Click(object sender, EventArgs e)
{
string M_str_sqlcon = "server=localhost;user id=root;password=wz1027**zm++;database=work2"; //创建数据库连接对象
conn = new MySqlConnection(M_str_sqlcon);
try
{
//打开数据库连接
conn.Open();
MessageBox.Show("数据库已经连接了!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
查询:(需要查询的表格“student”)
private void button2_Click(object sender, EventArgs e)
{
string sql = "select * from student";
mda = new MySqlDataAdapter(sql, conn);
ds = new DataSet();
mda.Fill(ds, "student");
//显示数据
dataGridView1.DataSource = ds.Tables["student"];
conn.Close();
}
修改:
private void button3_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)
{
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 button5_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 button6_Click(object sender, EventArgs e)
{
Application.Exit();
}
完整代码:
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;
namespace Cmysql
{
public partial class Form1 : Form
{
MySqlConnection conn; //连接数据库对象
MySqlDataAdapter mda; //适配器变量
DataSet ds; //临时数据集
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
string M_str_sqlcon = "server=localhost;user id=root;password=wz1027**zm++;database=work2"; //创建数据库连接对象
conn = new MySqlConnection(M_str_sqlcon);
try
{
//打开数据库连接
conn.Open();
MessageBox.Show("数据库已经连接了!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void button2_Click(object sender, EventArgs e)
{
string sql = "select * from student";
mda = new MySqlDataAdapter(sql, conn);
ds = new DataSet();
mda.Fill(ds, "student");
//显示数据
dataGridView1.DataSource = ds.Tables["student"];
conn.Close();
}
private void button3_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)
{
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 button5_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 button6_Click(object sender, EventArgs e)
{
Application.Exit();
}
}
}
五:运行结果:
连接数据库:
查询表student :