.NET 連接 MySQL 完整指南：驅動安裝、連線字串與 CRUD 實戰

前置準備

1. 下載 **MySQL Connector/NET** (官方 ADO.NET 驅動)：
   https://dev.mysql.com/downloads/connector/net/
   選擇對應 .NET Framework 版本 (如 8.0.x 支援 .NET 4.6.2+ / .NET Core / .NET 5+)。
2. 安裝後，於專案 **加入參考** → `MySql.Data.dll` (通常在 `C:\Program Files (x86)\MySQL\MySQL Connector Net \Assemblies\`)。
3. 引用命名空間：using MySql.Data.MySqlClient;

連線字串關鍵參數

Server=localhost;Database=school;Uid=root;Pwd=123;CharSet=utf8;SslMode=None;

資料庫操作封裝類別

public static class MySqlHelper
{
    private static readonly string ConnStr =
        "Server=localhost;Database=school;Uid=root;Pwd=123;CharSet=utf8;SslMode=None;Pooling=true;";

    public static MySqlConnection GetConnection()
    {
        var conn = new MySqlConnection(ConnStr);
        conn.Open();
        return conn;
    }

    // 查詢單一值
    public static T ExecuteScalar(string sql, params MySqlParameter[] parameters)
    {
        using var conn = GetConnection();
        using var cmd = new MySqlCommand(sql, conn) { Parameters = { } };
        cmd.Parameters.AddRange(parameters);
        return (T)cmd.ExecuteScalar();
    }

    // 查詢列表 (DataReader 高效能)
    public static List Query(string sql, Func map, params MySqlParameter[] parameters)
    {
        var list = new List();
        using var conn = GetConnection();
        using var cmd = new MySqlCommand(sql, conn);
        cmd.Parameters.AddRange(parameters);
        using var reader = cmd.ExecuteReader();
        while (reader.Read()) list.Add(map(reader));
        return list;
    }

    // 執行非查詢
    public static int ExecuteNonQuery(string sql, params MySqlParameter[] parameters)
    {
        using var conn = GetConnection();
        using var cmd = new MySqlCommand(sql, conn);
        cmd.Parameters.AddRange(parameters);
        return cmd.ExecuteNonQuery();
    }
}

實戰範例：學生資料 CRUD

資料表結構

CREATE TABLE `student` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `age` INT,
  `email` VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

實體對應

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public string Email { get; set; }
}

Repository 實作

public class StudentRepository
{
    public List GetAll() =>
        MySqlHelper.Query("SELECT * FROM student", r => new Student
        {
            Id = r.GetInt32("id"),
            Name = r.GetString("name"),
            Age = r.GetInt32("age"),
            Email = r.IsDBNull(r.GetOrdinal("email")) ? null : r.GetString("email")
        });

    public Student GetById(int id) =>
        MySqlHelper.Query("SELECT * FROM student WHERE id=@id",
            r => new Student { Id = r.GetInt32("id"), Name = r.GetString("name"), Age = r.GetInt32("age"), Email = r.GetString("email") },
            new MySqlParameter("@id", id)).FirstOrDefault();

    public int Insert(Student s) =>
        MySqlHelper.ExecuteNonQuery("INSERT INTO student(name,age,email) VALUES(@name,@age,@email)",
            new MySqlParameter("@name", s.Name), new MySqlParameter("@age", s.Age), new MySqlParameter("@email", s.Email));

    public int Update(Student s) =>
        MySqlHelper.ExecuteNonQuery("UPDATE student SET name=@name,age=@age,email=@email WHERE id=@id",
            new MySqlParameter("@name", s.Name), new MySqlParameter("@age", s.Age), new MySqlParameter("@email", s.Email), new MySqlParameter("@id", s.Id));

    public int Delete(int id) =>
        MySqlHelper.ExecuteNonQuery("DELETE FROM student WHERE id=@id", new MySqlParameter("@id", id));
}

Console 測試程式

class Program
{
    static void Main()
    {
        var repo = new StudentRepository();

        // Insert
        repo.Insert(new Student { Name = "張三", Age = 20, Email = "zhang@example.com" });
        Console.WriteLine("Inserted.");

        // Query All
        foreach (var s in repo.GetAll())
            Console.WriteLine($"{s.Id}\t{s.Name}\t{s.Age}\t{s.Email}");

        // Update
        var stu = repo.GetById(1);
        stu.Age = 21;
        repo.Update(stu);
        Console.WriteLine("Updated.");

        // Delete
        repo.Delete(1);
        Console.WriteLine("Deleted.");

        Console.ReadKey();
    }
}

常見錯誤與解決

進階建議

• **參數化查詢** 防 SQL Injection (範例皆使用 MySqlParameter)。
• **連線池** 預設開啟，高併發下效能最佳。
• **Dapper / Entity Framework Core** 可進一步簡化映射代碼。
• **Async/Await** 版本 (ExecuteReaderAsync 等) 適用於 ASP.NET Core 高併發場景。