前置準備
- NuGet 安裝:
Install-Package MySql.Data (或下載 MySQL Connector/NET 安裝)。
- 專案加入參考
MySql.Data.dll。
封裝類別:MySqlHelper
using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Configuration;
namespace DAL
{
public static class MySqlHelper
{
private static readonly string _connStr =
ConfigurationManager.ConnectionStrings["MySqlConn"].ConnectionString;
// 或直接字串:"server=localhost;uid=root;pwd=123;database=test;pooling=true;"
private static MySqlConnection GetConnection()
{
var conn = new MySqlConnection(_connStr);
if (conn.State != ConnectionState.Open) conn.Open();
return conn;
}
// 單一值查詢
public static object ExecuteScalar(string sql, params MySqlParameter[] parameters)
{
using (var conn = GetConnection())
using (var cmd = new MySqlCommand(sql, conn))
{
if (parameters != null) cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
// 執行非查詢 (INSERT/UPDATE/DELETE)
public static int ExecuteNonQuery(string sql, params MySqlParameter[] parameters)
{
using (var conn = GetConnection())
using (var cmd = new MySqlCommand(sql, conn))
{
if (parameters != null) cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();
}
}
// 查詢 DataTable
public static DataTable GetDataTable(string sql, params MySqlParameter[] parameters)
{
using (var conn = GetConnection())
using (var cmd = new MySqlCommand(sql, conn))
using (var da = new MySqlDataAdapter(cmd))
{
if (parameters != null) cmd.Parameters.AddRange(parameters);
var dt = new DataTable();
da.Fill(dt);
return dt;
}
}
// 查詢 DataReader (需自行管理連線關閉)
public static MySqlDataReader ExecuteReader(string sql, params MySqlParameter[] parameters)
{
var conn = GetConnection();
var cmd = new MySqlCommand(sql, conn);
if (parameters != null) cmd.Parameters.AddRange(parameters);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// 交易支援
public static void ExecuteTransaction(Action action)
{
using (var conn = GetConnection())
using (var tran = conn.BeginTransaction())
{
try
{
action(conn, tran);
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
}
}
使用範例
// 查詢
var dt = MySqlHelper.GetDataTable("SELECT * FROM users WHERE status=@status",
new MySqlParameter("@status", 1));
// 新增
int rows = MySqlHelper.ExecuteNonQuery(
"INSERT INTO users (name, email) VALUES (@name, @email)",
new MySqlParameter("@name", "張三"),
new MySqlParameter("@email", "zhang@example.com"));
// 單一值
int count = Convert.ToInt32(MySqlHelper.ExecuteScalar("SELECT COUNT(*) FROM users"));
// 交易
MySqlHelper.ExecuteTransaction((conn, tran) => {
var cmd = new MySqlCommand("UPDATE accounts SET balance=balance-@amt WHERE id=@id", conn, tran);
cmd.Parameters.AddWithValue("@amt", 100);
cmd.Parameters.AddWithValue("@id", 1);
cmd.ExecuteNonQuery();
// ... 第二筆更新
});
連接字串設定
<connectionStrings>
<add name="MySqlConn"
connectionString="server=localhost;port=3306;uid=root;pwd=123;database=mydb;charset=utf8mb4;pooling=true;min pool size=1;max pool size=100;"
providerName="MySql.Data.MySqlClient" />
</connectionStrings>
最佳實務補充
- 連線池:預設開啟,
pooling=true,依負載調整 min/max pool size。
- 參數化查詢:所有方法支援
MySqlParameter[],防止 SQL Injection。
- 非同步:.NET 4.5+ 可擴充
ExecuteScalarAsync、GetDataTableAsync 等非同步版本。
- Dapper:輕量 ORM,搭配此 Helper 的
GetConnection() 使用:conn.Query<User>("SELECT * FROM users")。