構建可移植高性能的 SQL Server 數據訪問層：從通用接口到專用實現

核心矛盾：可移植性 vs. 性能

許多開發者追求「一套代碼跑遍所有數據庫」，但現實是：不同 RDBMS 的 SQL 方言、優化器特性、鎖機制、並發控制差異巨大。僅使用通用接口（如 IDbConnection、IDbCommand）並編寫通用 SQL，必然導致：

• 無法利用數據庫特有優化（如 SQL Server 的表值參數、Oracle 的 Hint、PostgreSQL 的 CTE）
• 查詢計劃復用率低，參數嗅探問題難以處理
• 並發衝突頻發，鎖升級不可控

分層架構：正確的抽象層級

關鍵在於在業務邏輯層與數據庫物理實現之間，建立「領域驅動的數據訪問契約」，而非單純的 ADO.NET 接口封裝。

第一層：提供程序工廠（Provider Factory）

封裝連接、命令、適配器的創建，屏蔽具體類型：

public enum DatabaseType { SqlServer, Oracle, PostgreSQL, MySQL }

public static class DbProviderFactory
{
    public static IDbConnection CreateConnection(string connStr, DatabaseType type)
    {
        return type switch
        {
            DatabaseType.SqlServer => new SqlConnection(connStr),
            DatabaseType.Oracle => new OracleConnection(connStr),
            DatabaseType.PostgreSQL => new NpgsqlConnection(connStr),
            DatabaseType.MySQL => new MySqlConnection(connStr),
            _ => throw new ArgumentException("Unsupported database type")
        };
    }

    public static IDbCommand CreateCommand(string cmdText, IDbConnection conn, DatabaseType type)
    {
        var cmd = conn.CreateCommand();
        cmd.CommandText = cmdText;
        return cmd; // 具體參數創建由上層調用 DbParameter 集合統一處理
    }

    public static DbDataAdapter CreateAdapter(IDbCommand cmd, DatabaseType type)
    {
        return type switch
        {
            DatabaseType.SqlServer => new SqlDataAdapter((SqlCommand)cmd),
            DatabaseType.Oracle => new OracleDataAdapter((OracleCommand)cmd),
            DatabaseType.PostgreSQL => new NpgsqlDataAdapter((NpgsqlCommand)cmd),
            DatabaseType.MySQL => new MySqlDataAdapter((MySqlCommand)cmd),
            _ => throw new ArgumentException("Unsupported database type")
        };
    }
}

第二層：數據訪問接口契約（Repository Interface）

定義領域操作，而非 SQL 操作：

public interface ICustomerRepository
{
    Customer GetById(int id);
    IEnumerable GetByCountry(string countryCode);
    int Insert(Customer customer);
    bool Update(Customer customer);
    void Delete(int id);
}

第三層：具體數據庫實現

每個數據庫一個實現項目，充分利用該數據庫特性：

// SQL Server 實現 - 利用存儲過程、表值參數、MERGE 語句
public class SqlCustomerRepository : ICustomerRepository
{
    private readonly string _connStr;
    public SqlCustomerRepository(string connStr) => _connStr = connStr;

    public Customer GetById(int id)
    {
        using var conn = new SqlConnection(_connStr);
        using var cmd = new SqlCommand("usp_GetCustomerById", conn) { CommandType = CommandType.StoredProcedure };
        cmd.Parameters.AddWithValue("@Id", id);
        conn.Open();
        using var reader = cmd.ExecuteReader();
        return reader.Read() ? MapCustomer(reader) : null;
    }

    public int Insert(Customer customer)
    {
        using var conn = new SqlConnection(_connStr);
        using var cmd = new SqlCommand(@"
            INSERT INTO Customers (Name, CountryCode, CreatedAt)
            OUTPUT INSERTED.Id
            VALUES (@Name, @CountryCode, SYSUTCDATETIME())", conn);
        cmd.Parameters.AddWithValue("@Name", customer.Name);
        cmd.Parameters.AddWithValue("@CountryCode", customer.CountryCode);
        conn.Open();
        return (int)cmd.ExecuteScalar();
    }
    // ... 其他方法利用 SQL Server 特性優化
}

// Oracle 實現 - 利用序列、PL/SQL 包、游標變量
public class OracleCustomerRepository : ICustomerRepository { /* ... */ }

第四層：運行時組裝（依賴注入 / 配置驅動）

// 配置文件
{
  "DatabaseType": "SqlServer",
  "ConnectionString": "Server=...;Database=...;",
  "RepositoryAssembly": "MyApp.Data.SqlServer",
  "RepositoryType": "MyApp.Data.SqlServer.SqlCustomerRepository"
}

// 運行時工廠
public static class RepositoryFactory
{
    public static ICustomerRepository CreateCustomerRepository(IConfiguration config)
    {
        var asmName = config["RepositoryAssembly"];
        var typeName = config["RepositoryType"];
        var connStr = config.GetConnectionString("Default");
        
        var asm = Assembly.Load(asmName);
        var type = asm.GetType(typeName);
        return (ICustomerRepository)Activator.CreateInstance(type, connStr);
    }
}

業務層完全解耦

public class CustomerService
{
    private readonly ICustomerRepository _repo;
    public CustomerService(ICustomerRepository repo) => _repo = repo;

    public CustomerDto GetCustomer(int id) => _repo.GetById(id).ToDto();
    public int CreateCustomer(CreateCustomerDto dto) => _repo.Insert(dto.ToEntity());
}

關鍵設計原則總結

現代替代：Micro-ORM 與 EF Core

對於新項目，建議考慮：

• Dapper：輕量、高性能、手寫 SQL 但支持多數據庫
• EF Core：LINQ 查詢、遷移、多提供程序，性能持續優化
• SqlSugar / FreeSql：國產 ORM，功能豐富、支持多數據庫

但理解上述分層思想，有助於在 ORM 無法滿足性能或特殊需求時，正確地編寫底層數據訪問代碼。