.NET 生態連接 MySQL 歷經 MySql.Data (Oracle 官方) → MySqlConnector (社區高性能分支) 兩代。本文基於 **MySqlConnector 2.x** (推薦,.NET Standard 2.0+, 完全異步、連接池優化、修復大量 Bug),提供從基礎 CRUD 到企業級架構的完整指南。
<PackageReference Include="MySqlConnector" Version="2.3.5" />
dotnet add package MySqlConnector
"Server=10.0.1.10;Port=3306;Database=PhoneBook;Uid=app_user;Pwd=Str0ngP@ss!;" +
"SslMode=Required;CertificateStoreLocation=CurrentUser;CertificateThumbprint=...;" + -- 生產必開 SSL
"ConnectionReset=true;Pooling=true;MinPoolSize=5;MaxPoolSize=100;" +
"ConnectionLifeTime=300;DefaultCommandTimeout=30;CharacterSet=utf8mb4;" +
"AllowUserVariables=true;UseAffectedRows=true;"
public class PhoneRepository
{
private readonly string _connStr;
public PhoneRepository(string connStr) => _connStr = connStr;
private MySqlConnection CreateConnection() => new MySqlConnection(_connStr);
public async Task AddAsync(string name, string phone, CancellationToken ct = default)
{
const string sql = @"INSERT INTO PhoneBook (Name, Phone) VALUES (@Name, @Phone);
SELECT LAST_INSERT_ID();";
await using var conn = CreateConnection();
await conn.OpenAsync(ct);
await using var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Phone", phone);
return Convert.ToInt32(await cmd.ExecuteScalarAsync(ct));
}
public async Task> GetAllAsync(CancellationToken ct = default)
{
const string sql = "SELECT Id, Name, Phone FROM PhoneBook ORDER BY Id";
await using var conn = CreateConnection();
await conn.OpenAsync(ct);
await using var cmd = new MySqlCommand(sql, conn);
await using var reader = await cmd.ExecuteReaderAsync(ct);
var list = new List();
while (await reader.ReadAsync(ct))
{
list.Add(new Contact {
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Phone = reader.GetString(2)
});
}
return list;
}
}
public record Contact(int Id, string Name, string Phone);
MinPoolSize 預熱、MaxPoolSize 上限 (默認 100)。ConnectionLifeTime (秒) 控制連接回收重建,防止長連接攜帶舊狀態 (如會話變量、準備語句緩存)。using Dapper;
public async Task GetByIdAsync(int id)
{
const string sql = "SELECT Id, Name, Phone FROM PhoneBook WHERE Id = @Id";
await using var conn = CreateConnection();
return await conn.QuerySingleOrDefaultAsync(sql, new { Id = id });
}
public async Task BulkInsertAsync(IEnumerable contacts)
{
const string sql = "INSERT INTO PhoneBook (Name, Phone) VALUES (@Name, @Phone)";
await using var conn = CreateConnection();
return await conn.ExecuteAsync(sql, contacts); // 批量參數化,單次往返
}
<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="8.0.0" />
public class AppDbContext : DbContext
{
public DbSet PhoneBook => Set();
protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseMySql(_connStr, ServerVersion.AutoDetect(_connStr), b =>
b.CharSetBehavior(CharSetBehavior.NeverAppend) // 避免隱式字符集轉換
);
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity(e => {
e.ToTable("PhoneBook");
e.Property(p => p.Name).HasMaxLength(100).IsRequired();
e.Property(p => p.Phone).HasMaxLength(20).IsRequired();
e.HasIndex(p => p.Phone).IsUnique();
});
}
}
// 使用
await using var ctx = new AppDbContext();
var list = await ctx.PhoneBook.Where(p => p.Name.Contains("張")).ToListAsync();
await ctx.PhoneBook.AddAsync(new Contact(0, "李四", "13800001111"));
await ctx.SaveChangesAsync();
選對驅動、用好異步、管好連接池、擁抱 EF Core — .NET 連接 MySQL 可達生產級穩定與高性能。