C#连接MySql数据库（二）--通过DataGridView修改数据库内容

通过DataSet连接数据库步骤：

1.**定义连接语句connStr:**如string connStr = “server=localhost;user id=root;password=123456;persistsecurityinfo=True;database=classicmodels”;

2.定义Mysql命令语句cmdStr,

如 string cmdStr = “select * from customers”;

3.定义MySqlConnection对象，
MySqlConnection conn = new MySqlConnection(connStr);

4.连接数据库conn.open();

5.定义一个MySqlDataAdapter其构造参数为MySql命令语句和MySqlConnection对象
如 MySqlDataAdapter adapter = new MySqlDataAdapter(cmdStr,conn)

6.将adapter对象中连接数据库的得出的Table，通过Fill()方法添加到DataSet对象中去。
adapter.Fill(ds,‘tableName’)//其中tablename是自定义的，与数据库中的无关

具体代码如下

 		MySqlDataAdapter adapter;
        DataSet ds;
         private void Form1_Load(object sender, EventArgs e)
        {
            string connStr = "server=localhost;user id=root;password=123456;persistsecurityinfo=True;database=classicmodels";
            string cmdStr = "select * from customers";
            string cmdStr2 = "select * from employees";
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                adapter = new MySqlDataAdapter(cmdStr, conn);
                ds = new DataSet();
                adapter.Fill(ds, "customer");
                adapter = new MySqlDataAdapter(cmdStr2, conn);
                adapter.Fill(ds, "employee");
            }
            catch(MySqlException exp)
            {
                MessageBox.Show(exp.Message);
            }
            finally
            {
                conn.Close();
            }
        }

通过try catch方法连接更加安全

如何将数据库数据在DataGridView控件中展示

在与数据库连接完成后，只需要在窗口中拖入一个DataGridView控件和一个Button按钮，在按钮的点击事件中将DataGridView的dataSource设置为前面定义的DataSet对象中的表格
dataGridView1.DataSource = ds.Tables[“customer”];
中括号的名字就是前面在Fill方法中写的

实现代码

        private void button1_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = ds.Tables["customer"];
            dataGridView1.Columns[0].HeaderText = "序号";
            dataGridView1.Columns[1].HeaderText = "名字";
            dataGridView1.Columns[0].Visible = false;

        }

最后效果

如何实现在DataGridView控件中对数据库进行修改

首先，要先了解命令生成器MySqlCommandBulider
因为每一次写MysqlCmd语句都很麻烦，就可以采用MySqlCommandBulider对象来自动获取这些命令。

DataSet对象有典型的四种功能，Insert,Delete,Update和Select.实际上我们是对DataSet对象的四个属性分别定义的MySQL语句的调用来完成的，如SelectCommand对应Select语句，DeleteCommand对应Delete语句

所以要对数据库进行操作，在Adapter对象定义完后，定义一个MySqlCommandBulider对象，其初始化参数为adapter。然后将adapter中增删改查的属性都用MySqlCommandBulider的增删改查的方法来初始化。

接下来就可以在DataGridView中直接对数据进行修改增加
当出现两个表的切换时，可以通过定义一个Cur变量来确定当前要修改的是哪一个表格。

源码：

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 DataGridFormTest
{
    public partial class Form1 : Form
    {
        MySqlDataAdapter customerAdapter;
        MySqlDataAdapter employeeAdapter;
        DataSet ds;
        string cur = "";
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string connStr = "server=localhost;user id=root;password=123456;persistsecurityinfo=True;database=classicmodels";
            string cmdStr = "select * from customers";
            string cmdStr2 = "select * from employees";
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                customerAdapter = new MySqlDataAdapter(cmdStr, conn);
                ds = new DataSet();
                customerAdapter.Fill(ds, "customer");
                MySqlCommandBuilder builder = new MySqlCommandBuilder(customerAdapter);
                customerAdapter.UpdateCommand = builder.GetUpdateCommand();
                employeeAdapter = new MySqlDataAdapter(cmdStr2, conn);
                employeeAdapter.Fill(ds, "employee");
                builder = new MySqlCommandBuilder(employeeAdapter);
                employeeAdapter.UpdateCommand = builder.GetUpdateCommand();
            }
            catch(MySqlException exp)
            {
                MessageBox.Show(exp.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = ds.Tables["customer"];
            dataGridView1.Columns[0].HeaderText = "序号";
            dataGridView1.Columns[1].HeaderText = "名字";
            dataGridView1.Columns[0].Visible = false;
            cur = "customer";

        }

        private void button3_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = ds.Tables["employee"];
            cur = "employee";
        }

        private void button2_Click(object sender, EventArgs e)
        {
            dataGridView1.EndEdit();
            switch (cur)
            {
                case "customer":
                    customerAdapter.Update(ds, "customer");
                    break;
                case "employee":
                    employeeAdapter.Update(ds, "employee");
                    break;

            }


        }
    }
}

效果：

在Workbench中可以看出确实修改成功了