【笔记】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);
            try { connection.Open(); }
            catch (Exception ex) { MessageBox.Show(ex.ToString()); return; }

            //读取解耦数据的准备工作
            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方法会报错。下面是效果图,点击按钮时更新数据到数据库。

使用参数

为了防止用户输入敏感或者恶意的脚本内容,开发者可以使用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();