【笔记】C# ADO.NET With MySQL

使用ADO.NET连接MySQL并进行数据操作

引用 MySQL.Data

项目需要引用MySQL.Data库才能连接MySQL数据库

方法一

使用NuGet Package Manager搜索MySQL.Data并安装即可。

方法二

该库可以通过安装MySQL Connector/NET得到。下载安装后，在安装目录（默认安装在C:\Program Files (x86)\MySQL\MySQL Connector Net）下的Assemblies文件夹里可以找到库文件，在Visual Studio中引用对应.NET版本的MySQL.Data.dll文件即可。

最后在源码中引用对应的名称空间：

using MySql.Data.MySqlClient;

连接数据库

MySqlConnection，MySqlCommand，MySqlDataReader，DataSet，MySqlDataProvider是ADO.NET的核心对象。其中MySqlConnection用于连接数据库：

//连接MySQL
string connectionString = "Data source=localhost;port=3306;database=ss;userid=root;password=root";
MySqlConnection connection = new MySqlConnection(connectionString);
try { connection.Open(); }
catch (Exception ex) { Console.WriteLine(ex.ToString()); return; }
//获取MySQL版本
Console.WriteLine($"MySQL Version: {connection.ServerVersion}");
//关闭MysQL连接
connection.Close();

其中连接字符串中的字段意义如下：

Server , Host , Data Source , DataSource：数据库的网络地址
Port：连接端口
Database , Initial Catalog：数据库名称
UserID , User Id , Username , Uid , User name , User：登录用户名
Password , pwd：登录密码

其他可用字段请看：Connector/NET 8.0 Connection Options Reference

执行SQL脚本

通过MySqlCommand对象执行SQL脚本，该对象有三个执行脚本的方法，分别是ExecuteReader、ExecuteScalar、ExecuteNonQuery。

为MySqlCommand对象的CommandText属性指定SQL脚本内容，也可以通过其构造函数的第一个参数进行指定，第二个参数指定MySqlConnection对象，执行脚本之前，必须先通过Open函数连接数据库。

ExecuteScalar方法获取返回结果第一行第一列的数据，并忽视其他行与列，用于获取简单的数据。

MySqlCommand cmd = new MySqlCommand("SELECT version()", connection);
Console.WriteLine(cmd.ExecuteScalar().ToString());

ExecuteReader方法返回一个MySqlDataReader对象，该对象储存详细的脚本取回的数据，可以通过Read函数逐项读取数据。

cmd.CommandText = "SELECT prod_id, prod_name, prod_price FROM products ORDER BY prod_price;";
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    //Console.WriteLine("\t{0}\t{1}\t{2}", dr[0], dr[1], dr[2]);
    Console.WriteLine("\t{0}\t{1}\t{2}", dr.GetString(0), dr.GetString(1), dr.GetDouble(2));
}
dr.Close();

ExecuteNonQuery方法执行一个脚本，但不返回任何数据，用于插入/更新/删除数据。

cmd.CommandText = "INSERT INTO customers VALUES(NULL, 'White', 'A', 'B', 'C', '000000', 'China', NULL, NULL);";
cmd.ExecuteNonQuery();

获取解耦数据

使用MySqlDataReader时，MySQL数据库会一直保持连接直到你显式关闭它，如果只想在获取/更新数据时连接数据库，应该用解耦的方式执行SQL脚本。最关键的三个对象：DataSet、MySqlDataAdapter、MySqlCommandBuilder。

DataSet：不依赖于数据库的独立数据集合，用于在内存中储存数据。即使断开数据连接依然可用，可以储存多个数据表（DataTable）。

MySqlDataAdapter：数据库与DataSet之间的接口，负责对数据库连接进行高效管理，根据需要打开和关闭连接，有两个主要方法：Fill（将数据读取到DataSet）、Update（将DataSet的数据更新到数据库中）。

MySqlCommandBuilder：与MySqlDataAdapter搭配使用，负责从用户的SELECT脚本生成对应的CREATE/INSERT/DELETE脚本，这些脚本在执行Update方法时被执行。

下面是一个MySQL数据库数据绑定到WPF的DataGrid的例子：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using MySql.Data.MySqlClient;
using System.Data;

namespace WpfApp4
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {

        DataSet ds = new DataSet();
        MySqlConnection connection;
        MySqlDataAdapter da;
        MySqlCommandBuilder cmdbuilder;

        public MainWindow()
        {
            InitializeComponent();

            //连接MySQL
            string connectionString = "Data source=localhost;port=3306;database=ss;userid=root;password=root";
            connection = new MySqlConnection(connectionString);

            //读取解耦数据的准备工作
            string sql = "SELECT prod_id, prod_name, prod_price FROM products ORDER BY prod_price;";
            da = new MySqlDataAdapter(sql, connection);
            cmdbuilder = new MySqlCommandBuilder(da);
            //填充数据到数据集，参数一为储存数据的DataSet，参数二为储存的表名（用于有多张表时区分）
            da.Fill(ds, "products");

            //绑定到DataGrid
            datagrid.ItemsSource = ds.Tables["products"].DefaultView;
            //datagrid.ItemsSource = ds.Tables[0].DefaultView;
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            //更新到数据库
            da.Update(ds, "products");
        }
    }
}

注意其中的MySqlCommandBuilder必须创建，否则Update方法会报错。在使用MySqlDataAdapter之前，并不需要执行Open方法连接数据库，在进行Fill和Update方法时MySqlDataAdapter会自动进行连接，并在不必要的时候断开连接，如果你确实提前执行了Open方法，那么数据库会一直保持连接，直到你显式关闭。下面是效果图，点击按钮时更新数据到数据库。

使用参数

为了防止用户输入敏感或者恶意的脚本内容，开发者可以使用MySQL的参数功能，因为参数仅会被视为字段，而不会被识别为执行的命令。在SQL中输入值或者函数参数时使用以@开头的内容会被识别为参数，通过MySqlCommand.Parameters.AddWithValue方法指定参数值。

cmd.CommandText = "SELECT prod_id, prod_name, prod_price FROM products WHERE prod_price>@min ORDER BY prod_price;";
cmd.Parameters.AddWithValue("@min", "50");
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    //Console.WriteLine("\t{0}\t{1}\t{2}", dr[0], dr[1], dr[2]);
    Console.WriteLine("\t{0}\t{1}\t{2}", dr.GetString(0), dr.GetString(1), dr.GetDouble(2));
}
dr.Close();