本文章介绍如何把VS2022与MySQL连接

要求

1、请设计一个项目连接到自己的MySQL数据库，数据库包含至少三张表；
2、使用dataGridView控件显示表中的数据；
3、实现基本crud操作；

一、MySQL与VS2022的连接

打开vs2022的界面

浏览下载第一个

我这边因为已安装了，具体的安装过程可以去网上查询。

二、代码编写

1.界面设计

5个Button加一个DataGridView

2.引入库

代码如下（示例）：

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;

3.连接数据库

代码如下（示例）：

private MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
        private MySqlConnection connection;

private void button1_Click(object sender, EventArgs e)
        {
            builder.UserID = "root";
            builder.Password = "176164";
            builder.Server = "localhost";
            builder.Database = "new_schema";
            connection = new MySqlConnection(builder.ConnectionString);
            try
                {
                    //打开数据库连接
                    connection.Open();
                    MessageBox.Show("数据库已经连接了！");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
        }

3.查询

代码如下（示例）：

private void button2_Click(object sender, EventArgs e)
        {
            string sql = "select * from student";
            MySqlDataAdapter mda = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            mda.Fill(ds, "student");
            //显示数据
            dataGridView1.DataSource = ds.Tables["student"];
            connection.Close();
        }

4.增加

代码如下（示例）：

private void button3_Click(object sender, EventArgs e)
        {
            string sql = "insert into student set s_id ='99',s_name ='张三',s_birth='1989-07-01',s_sex='男'";
            MySqlDataAdapter mda = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            mda.Fill(ds, "student");
            connection.Close();
        }

5.删除

代码如下（示例）：

private void button4_Click(object sender, EventArgs e)
        {
            
            string sql = "delete from student where s_id=99";
            MySqlDataAdapter mda = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            mda.Fill(ds, "student");
            connection.Close();
        }

6.修改

代码如下（示例）：

private void button5_Click(object sender, EventArgs e)
        {
            string sql = "update student set s_birth = '2001-01-01' where s_name='张三'";
            MySqlDataAdapter mda = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            mda.Fill(ds, "student");
            connection.Close();
        }

7.完整代码

代码如下（示例）：

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;
namespace WinFormsApp10
{
    public partial class Form1 : Form
    {
        private MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
        private MySqlConnection connection;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            builder.UserID = "root";
            builder.Password = "176164";
            builder.Server = "localhost";
            builder.Database = "new_schema";
            connection = new MySqlConnection(builder.ConnectionString);
            try
                {
                    //打开数据库连接
                    connection.Open();
                    MessageBox.Show("数据库已经连接了！");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            string sql = "select * from student";
            MySqlDataAdapter mda = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            mda.Fill(ds, "student");
            //显示数据
            dataGridView1.DataSource = ds.Tables["student"];
            connection.Close();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            string sql = "update student set s_birth = '2001-01-01' where s_name='张三'";
            MySqlDataAdapter mda = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            mda.Fill(ds, "student");
            connection.Close();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string sql = "insert into student set s_id ='99',s_name ='张三',s_birth='1989-07-01',s_sex='男'";
            MySqlDataAdapter mda = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            mda.Fill(ds, "student");
            connection.Close();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            
            string sql = "delete from student where s_id=99";
            MySqlDataAdapter mda = new MySqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            mda.Fill(ds, "student");
            connection.Close();
        }
    }
}

三、操作及实验结果

因为我的数据库是已经创好了的，里面也有对应的表，操作过程是【连接数据库】->【查询】->【连接数据库】->【增加】->【连接数据库】->【删除】->【连接数据库】->【增加】->【连接数据库】->【修改】

四、总结

本次实验最主要的就是MySQL与VS2022的连接，只需要按照上述过程即可。这次实验，主要用到了DataGridView控件，也使用了基础的sql语句。本次实验，我直接固定了增删改的语句，使用按钮只能实现代码中sql语句的功能，并且在每次操作之后都会断开与数据库的连接，所以在每次操作之后，都需要重新连接数据库

五、源码的clone地址：

https://github.com/pan20174/WinFormsApp10