.NET Framework 針對 SQL Server (System.Data.SqlClient)、OLE DB (System.Data.OleDb) 與 Oracle 提供不同的提供者類別。若業務邏輯直接耦合具體類別，未來切換資料庫將牽一髮動全身。本文示範如何以 Factory Method 統一建立介面、以 Singleton 管理連線池，打造「資料庫無關」的資料存取層（DAL）。
使用 abstract class 而非 interface，是為了後續在基類實作 Instance 靜態工廠方法與連線池邏輯：
public abstract class DBOperator
{
public abstract IDbConnection Connection { get; }
public abstract void Open();
public abstract void Close();
public abstract void BeginTrans();
public abstract void CommitTrans();
public abstract void RollbackTrans();
public abstract void ExecuteSql(string sql, string[] parameters, object[] values);
public abstract DataSet ExecuteSqlForDataSet(string queryString);
// --- 靜態工廠方法（自管理 Factory） ---
public static DBOperator Instance(string connectionString)
{
if (connectionString.IndexOf("provider=", StringComparison.OrdinalIgnoreCase) < 0)
return new SqlDBOperator(connectionString);
else
return new OleDBOperator(connectionString);
}
}internal class SqlDBOperator : DBOperator
{
private SqlConnection _conn;
private SqlTransaction _trans;
private bool _inTransaction;
public SqlDBOperator(string connStr) => _conn = new SqlConnection(connStr);
public override IDbConnection Connection => _conn;
public override void Open()
{
if (_conn.State != ConnectionState.Open) _conn.Open();
}
public override void Close()
{
if (_conn.State == ConnectionState.Open) _conn.Close();
}
public override void BeginTrans()
{
_trans = _conn.BeginTransaction();
_inTransaction = true;
}
public override void CommitTrans()
{
_trans?.Commit();
_inTransaction = false;
}
public override void RollbackTrans()
{
_trans?.Rollback();
_inTransaction = false;
}
public override void ExecuteSql(string sql, string[] parameters, object[] values)
{
using (var cmd = _conn.CreateCommand())
{
cmd.CommandText = sql;
if (_inTransaction) cmd.Transaction = _trans;
if (parameters != null)
{
if (parameters.Length != values.Length)
throw new ArgumentException("參數名稱與值數量不符");
for (int i = 0; i < parameters.Length; i++)
cmd.Parameters.AddWithValue(parameters[i], values[i]);
}
cmd.ExecuteNonQuery();
}
}
public override DataSet ExecuteSqlForDataSet(string queryString)
{
using (var cmd = _conn.CreateCommand())
using (var adapter = new SqlDataAdapter((SqlCommand)cmd))
{
cmd.CommandText = queryString;
if (_inTransaction) cmd.Transaction = _trans;
var ds = new DataSet();
adapter.Fill(ds);
return ds;
}
}
}internal class OleDBOperator : DBOperator
{
private OleDbConnection _conn;
private OleDbTransaction _trans;
private bool _inTransaction;
public OleDBOperator(string connStr) => _conn = new OleDbConnection(connStr);
public override IDbConnection Connection => _conn;
// Open/Close/Transaction 方法同 SqlDBOperator，略
public override void ExecuteSql(string sql, string[] parameters, object[] values)
{
using (var cmd = _conn.CreateCommand())
{
cmd.CommandText = ConvertParameters(sql); // @param → ?
if (_inTransaction) cmd.Transaction = _trans;
if (parameters != null)
{
if (parameters.Length != values.Length)
throw new ArgumentException("參數名稱與值數量不符");
for (int i = 0; i < parameters.Length; i++)
cmd.Parameters.AddWithValue(parameters[i], values[i]);
}
cmd.ExecuteNonQuery();
}
}
private string ConvertParameters(string sql)
{
// 簡易轉換：@Name → ? (實務建議用 Regex 完整解析)
return sql.Replace("@", "?");
}
// ExecuteSqlForDataSet 同理，略
}若偏好顯式工廠類別而非基類靜態方法：
public static class DBOperatorFactory
{
public static DBOperator Create(string connectionString)
{
return connectionString.Contains("provider=", StringComparison.OrdinalIgnoreCase)
? new OleDBOperator(connectionString)
: new SqlDBOperator(connectionString);
}
}用戶端呼叫：
var db = DBOperatorFactory.Create(connStr);
db.Open();
db.ExecuteSql("INSERT INTO ...", new[]{"@p1"}, new object[]{val});
db.Close();在 DBOperator 基類加入靜態陣列與索引，實作簡易 Round-Robin 池：
public abstract class DBOperator
{
// ... 介面定義 ...
private static DBOperator[] _pool;
private static int _poolSize;
private static int _nextIndex = -1;
private static readonly object _lock = new object();
static DBOperator()
{
// 從設定檔讀取池大小
_poolSize = int.Parse(ConfigurationManager.AppSettings["PoolCount"] ?? "0");
if (_poolSize > 0) _pool = new DBOperator[_poolSize];
}
public static DBOperator GetPooledInstance(string connectionString)
{
if (_poolSize == 0) return CreateNew(connectionString);
lock (_lock)
{
_nextIndex = (_nextIndex + 1) % _poolSize;
if (_pool[_nextIndex] == null)
_pool[_nextIndex] = CreateNew(connectionString);
return _pool[_nextIndex];
}
}
private static DBOperator CreateNew(string connStr)
{
return connStr.Contains("provider=", StringComparison.OrdinalIgnoreCase)
? new OleDBOperator(connStr)
: new SqlDBOperator(connStr);
}
}注意： 連線池需搭配 Open/Close 語意調整（Close 不真正關閉實體連線），實務上建議直接使用 ADO.NET 內建 Connection Pooling (Pooling=true; Min Pool Size=5; Max Pool Size=100)，僅在極特殊需求下自行實作。
var db = DBOperator.Instance(connStr);
db.Open();
db.BeginTrans();
try
{
db.ExecuteSql(sql1, params1, values1);
db.ExecuteSql(sql2, params2, values2);
db.CommitTrans();
}
catch
{
db.RollbackTrans();
throw;
}
finally
{
db.Close();
}結合 Factory Method 隔離提供者差異、Singleton 管理連線實例，即可建構出彈性、可維護的通用 DAL。核心原則：業務邏輯只依賴 DBOperator 抽象介面，具體實作細節完全封裝於衍生類別與工廠內部。專案實務中，建議優先使用 ADO.NET 內建連線池，並在必要時才引入自訂池邏輯。
