如何做個好用的數據庫訪問類

jopen 10年前發布 | 73K 次閱讀數據庫訪問類 .NET開發

數據庫訪問組件是應用系統開發中的基礎組件，在用過SQLHelper、EnterpriseLibrary、NHibernate、EntityFramework之后，我開始思考什么樣的數據庫訪問組件適合互聯網應用開發。

我需要什么樣的數據庫訪問類？

它必須具備

自動釋放數據庫連接及相關資源
這是最重要的要求，數據庫資源沒有及時回收會給系統帶來很大影響，往往就是這種低級錯誤造成系統癱瘓，與其要求程序員編寫高質量的代碼來避免這個錯誤，不如通過基礎組件徹底解決這個問題。
支持多個數據庫
多個數據庫指同類型的多個數據源，一般中型系統的數據庫分布在多臺服務器上，系統需要訪問多個數據源。
支持多種類型數據庫
其實一個系統需要訪問多種類型數據庫的情況不常見，反而會出現A系統使用SQL Server、B系統使用MySQL的情況，這時我不希望重寫整個數據庫訪問組件，重寫的成本很高，還會涉及DAL層的改造。
事務處理
即使現在很多應用場景講究最終一致性，但對于交易、庫存類的應用，數據庫事務仍然是簡單可靠的首選方案，
代碼必須極致簡單
它不需要是個成熟穩定的組件，而是一個簡單可用的起始代碼，實現常見的基本功能，并可以比較方便的擴展，我需要的是代碼、不是類庫。

它不需要

不支持存儲過程調用
存儲過程不適用與互聯網應用，DB很難做分布式，只能做分片，而分布式應用程序則相對易于實現，此外存儲過程不受源代碼管理，不符合軟件開發流程規范。
不實現ORMapping
類體現業務模型，不是數據存儲模型，業務模型決定數據庫結構，而不是受其影響，數據庫只是一種數據存儲方式。一些ORMapping框架通過配置維護類、表的映射，處理這種通用的映射配置非常復雜，需要考慮關聯表數據延遲加載、數據庫會話生命周期管理，進一步引出動態代理、會話上下文綁定、緩存等需求，遠超出了數據庫訪問組件的職責，所以我決定不做通用的映射功能，把從業務模型到數據模型轉換的職責交給DAL層。
不對數據庫的特有語法做統一處理
每種類型的數據庫都有自己特有的數據類型和函數定義，如SQL Server有top、MySQL有limit，實現的功能一樣但語法不同，如果在語法層面將這些差異屏蔽，那么勢必引入一種新的DSL（如NHibernate的HQL），而這種新的DSL會增加系統復雜度、并帶來新的學習成本，所以我不做這個功能，使用原生的SQL語句。

接下來，我會逐步實現上面提到的功能，并解釋代碼實現的技巧和權衡，實際上我已經完成了這個組件的開發，并起了個狗血的名字——SqlHelper2，代碼發布在這里，如果您有興趣，可以checkout下來看看，代碼不到300行，非常簡單。

talk is cheap, show me the code

既然需求確定了，下面就是實現它。

Feature1：支持多種類型數據庫

首先解釋一下為什么Feature1不是做最重要的功能“自動釋放數據庫連接及相關資源”。其實支持多種類型的數據庫并不需要寫更多的代碼，而是要針對接口編程，把這個功能提前實現是為了后續工作建立一個良好的代碼基礎，所以我從它入手。

以最常用的SQL Server數據庫為例，通過連接串創建數據庫連接對象的代碼一般是這樣：

var connectionString = "Data Source=localhost;Initial Catalog=sample;User ID=sa;Password=?";
var connection = new SqlConnection(connectionString);
connection.Open();

如果只用SQL Server數據庫，這么做沒問題，但如果用到Oracle數據庫，就要用OracleConnection類、PostgreSQL數據庫用NpgsqlConnection類……SqlCommand、SqlDataReader、SqlParameter、SqlTransaction等類也是同樣的情況，如果使用這些類來編寫代碼，就是在面向具體實現編程。

其實ADO.NET在System.Data.Common命名空間中已經提供了一組抽象基類DbConnection、DbCommand、DbDataReader、DbParameter、DbTransaction，可以利用這些類編寫與具體數據庫類型無關的代碼，面向接口編程，達到改配置不改代碼即可訪問不同類型的數據庫。

然而這些抽象類不能直接使用new實例化，而需要通過工廠方法創建，可以使用DbProviderFactories.GetFactory(providerName)方法得到數據庫驅動提供程序的實現，然后調用工廠方法得到它們的實例：

var providerFactory = DbProviderFactories.GetFactory(providerName);
var connection = providerFactory.CreateConnection();
var command = connection.CreateCommand();
var parameter = command.CreateParameter();
var dr = command.ExecuteReader();

在實際調用時，providerName可能是SQL Server數據庫的System.Data.SqlClient、也可能是MySql數據庫的MySql.Data.MySQLClient，只需要將providerName配置為正確的值，就可以訪問特定類型的數據庫。

所以，實現“支持多種類型數據庫”的關鍵在于面向接口編程：

利用DbProviderFactories類創建具體類型的數據庫驅動提供程序；
使用System.Data.Common命名空間下的抽象類編寫數據庫訪問代碼。

檢查一下你的代碼中是否還在用System.Data.SqlClient等具體實現程序命名空間中的類，將它們改為可復用的優雅代碼吧！

Feature2：自動釋放數據庫連接及相關資源

自動釋放包括兩方面的含義：

無論sql語句執行過程中是否出現異常，資源用完之后立即釋放；

不需要調用者發出釋放資源的信號。

做到這兩方面，就可以保證數據庫連接及相關資源不受調用代碼的影響，能夠及時、正確的釋放。

無論sql語句執行過程中是否出現異常，資源用完之后立即釋放

關閉數據庫連接一般在finally代碼塊中調用Close()方法：

var connection = providerFactory.CreateConnection();
try {
    var command = connection.CreateCommand();
    ...
}
finally {
    connection.Close();
}

更好的方法是用using語句，它能實現同樣的功能，不用try-finally，也不需要調用Close方法，代碼更簡潔，對于DbCommand、DbDataReader對象可以采用同樣資源釋放方式，事實上所有實現IDisposable接口的類，都可以采用using關鍵字釋放資源：

using (var connection = providerFactory.CreateConnection()) {
    connection.Open();
    using (var command = connection.CreateCommand()) {
        command.CommandType = CommandType.Text;
        command.CommandText = "select * from Book";
        using (var reader = command.ExecuteReader()) {
            while(reader.Read()){
                Consume(reader);
            }
        }
    }
}

上面的代碼正常運行時，會依次創建connection、command、reader對象，然后依次釋放它們；無論哪行代碼出現異常，已經創建的資源都會被釋放。

不需要調用者發出釋放資源的信號

通過using語句，我實現了“無論sql語句執行過程中是否出現異常，資源用完之后立即釋放”的功能，下面考慮“不需要調用者發出釋放資源的信號”的需求。

對于查詢數據的場景，數據庫訪問類負責打開數據庫連接、執行SQL語句、創建DataReader和釋放資源，調用者只需要從DataReader中消費數據，這是理想的職責分離。然而數據庫訪問類如何得知調用者已經完成消費呢？一種方法是用模板方法模式，這要求調用者必須繼承某個基類，侵入性太大；第二種方法是使用Action<T>委托，消費DataReader的代碼通過Action<T>委托實例傳給數據庫訪問類，數據庫訪問類先建立連接，然后調用委托方法，最后進行資源清理：

public class Database {
    private readonly DbProviderFactory _ProviderFactory;
    private readonly string _ConnectionString;

    public Database(string connectionString, string providerName) {
        _ConnectionString = connectionString;
        _ProviderFactory = DbProviderFactories.GetFactory(providerName);
    }

    public void ExecuteReader(string sql, Action<DbDataReader> action) {
        // 建立連接
        using (var connection = _ProviderFactory.CreateConnection()) {
            connection.ConnectionString = _ConnectionString;
            connection.Open();
            // 建立命令對象
            using (var command = connection.CreateCommand()) {
                command.CommandType = CommandType.Text;
                command.CommandText = sql;
                // 執行查詢語句，返回DataReader
                using (var dr = command.ExecuteReader()) {
                    // 調用偽托方法
                    action.Invoke(dr);
                }   // dispose dr
            }   // dispose command
        }   // dispose connection
    }
}

調用方直接讀取DataReader，無需考慮其它操作，假設要讀取Book表中的所有記錄，并將其填充到Book領域對象：

public IList<Book> GetAllBooks() {
    // 創建數據庫訪問類
    var connectionString = "Data Source=localhost;Initial Catalog=mall;User ID=sa;Password=*";
    var providerName = "System.Data.SqlClient";
    var db = new Database(connectionString, providerName);

    var books = new List<Book>();
    db.ExecuteReader("select * from Book", dr => {
        // 讀取Book表中的所有記錄并將其填充到Book領域對象
        while (dr.Read()) {
            var book = new Book {Id = (int) dr["Id"], Name = (string) dr["Name"]};
            books.Add(book);
        }
    });

    return books;
}

現在，我們已經實現了數據庫資源的自動釋放，而調用代碼只需消費數據，而不必處理其它事情，遵循了單一職責SRP原則。

Feature3：支持多個數據庫

幾乎所有項目的配置都保存在配置文件中，對于.net系統，數據庫的信息一般保存在App.config或者Web.config文件的connectionStrings配置節中：

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="product" connectionString="Data Source=localhost;Initial Catalog=product-read;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
    <add name="order" connectionString="Data Source=192.168.1.100;Initial Catalog=order-read;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

所以可以讓數據庫訪問類從配置文件中讀取數據庫連接配置，實現這個功能非常簡單，在構造方法中使用ConfigurationManager類讀取配置文件中的連接串配置節，構造方法的參數為連接串的配置名：

public Database(string connectionStringName) {
    var connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionStringName];
    var connectionString = connectionStringSettings.ConnectionString;
    _ConnectionString = connectionString;
    _ProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
}

這樣改造后，調用者可以通過創建多個Database對象訪問多個數據源，比如下面獲取所有圖書銷量的方法，就用到了product和order兩個數據源：

public IDictionary<Book, int> GetAllBookSales() {
    var sales = new Dictionary<Book, int>();
    new Database("product").ExecuteReader("select * from Book", dr => {
        while (dr.Read()) {
            var book = new Book {Id = (int) dr["Id"], Name = (string) dr["Name"]};
            var amount = GetBookSales(book.Id);
            sales.Add(book, amount);
        }
    });

    return sales;
}

private int GetBookSales(int bookId) {
    var sum = 0;
    new Database("order").ExecuteReader(
        string.Format("select sum(Amount) from OrderDetail where BookId = {0}", bookId),
        dr => {
            if (dr.Read() && dr[0] != DBNull.Value)
                sum = (int) dr[0];
        });
    return sum;
}

此外還可以做一個小改進：如果系統只需要訪問一個數據庫，那么只要把這個連接串配置名設置為“*”（或者其它你喜歡的名字）：

<add name="*" connectionString="Data Source=localhost;Initial Catalog=sample;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>

Database類的構造方法使用“缺省參數”，這樣在實例化Database類時，就不必指定配置名了：

public class Database {
    public Database(string connectionStringName = "*") {
        ...
    }
}

至此，我已經實現了Feature1（支持多種類型數據庫）、Feature2（自動釋放數據庫連接及相關資源）和Feature3（支持多個數據庫）的全部功能。下面是目前數據庫訪問類的完整代碼，只有一個類Database、一個構造方法和一個執行Reader的方法：

public class Database {
    private readonly DbProviderFactory _ProviderFactory;
    private readonly string _ConnectionString;

    public Database(string connectionStringName = "*") {
        var connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionStringName];
        var connectionString = connectionStringSettings.ConnectionString;
        _ConnectionString = connectionString;
        _ProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
    }

    public void ExecuteReader(string sql, Action<DbDataReader> action) {
        using (var connection = _ProviderFactory.CreateConnection()) {
            connection.ConnectionString = _ConnectionString;
            connection.Open();
            using (var command = connection.CreateCommand()) {
                command.CommandType = CommandType.Text;
                command.CommandText = sql;
                using (var dr = command.ExecuteReader()) {
                    action.Invoke(dr);
                }
            }
        }
    }
}

接下來是我會完善數據庫訪問類，使其具備設置查詢參數和更新數據功能，同時秉承KISS、DRY、SRP思想不斷重構代碼。

Feature4：支持查詢參數

查詢參數是數據庫編程的基本功能，實現起來并不困難，在這里我將重點放在“使用更少的代碼、以更靈活的方式設置查詢參數”。

最初的想法：使用具體類的實例設置查詢參數

最初的想法是通過IEnumerable<DbParameter>類型的對象設置查詢參數：

public class Database {
    public void ExecuteReader(string sql, IEnumerable<DbParameter> parameters, Action<DbDataReader> action) {
        ...
        if (parameters != null) {
            foreach (var p in parameters)
                command.Parameters.Add(p);
        }
        ...
    }
    ...
}

但這樣調用代碼只能通過實例化DbParameter的派生類給參數賦值，違背了面向接口編程原則，同時也破壞了“支持多種類型數據庫”功能：

private int GetBookSales(int bookId) {
    var sum = 0;
    var parameters = new[] {new SqlParameter("@BookId", bookId)};   // bad smell
    new Database("order").ExecuteReader(string.Format("select sum(Amount) from OrderDetail where BookId = @BookId"), parameters, dr => {
        if (dr.Read() && dr[0] != DBNull.Value)
            sum = (int) dr[0];
    });
    return sum;
}

改進1：使用Action 委托設置查詢參數

遵循面向接口編程，可以通過command.CreateParameter方法創建查詢參數，如果這句代碼由調用者編寫，就需要數據庫訪問類將command對象暴露出來，破壞了封裝，所以我想到增加一個Action<DbCommand>委托類型的參數，用它來設置查詢參數：

public class Database {
    public void ExecuteReader(string sql, Action<DbCommand> setParametersAction, Action<DbDataReader> action) {
        ...
        if (setParametersAction != null)
            setParametersAction.Invoke(command);
        ...
    }
    ...
}

這樣做雖然沒有在數據庫訪問類層面將DbCommand對象暴露出來，但在方法級別仍然將其暴露，而且調用者的代碼太冗長了：

private int GetBookSales(int bookId) {
    var sum = 0;
    new Database("order").ExecuteReader(string.Format("select sum(Amount) from OrderDetail where BookId = @BookId"),
        command => {
            var p = command.CreateParameter();
            p.ParameterName = "@BookId";
            p.Value = bookId;
            command.Parameters.Add(p);
        },
        dr => {
            if (dr.Read() && dr[0] != DBNull.Value)
                sum = (int) dr[0];
        });
        return sum;
    }
}

改進2：使用匿名類設置查詢參數

從調用者的角度思考，設置查詢參數無非是提供參數名和參數值，那么平時常用的類庫是如何做的呢？我想到了jQuery的$.post方法：

$.post("test.php", { name: "John", time: "2pm" });

簡潔清晰的beauty code，在c#中可以使用匿名類實現同樣的功能，這樣調用者的代碼變成：

private int GetBookSales(int bookId) {
    var sum = 0;
    new Database("order").ExecuteReader(string.Format("select sum(Amount) from OrderDetail where BookId = @BookId"),
        new {BookId = bookId},
        dr => {
            if (dr.Read() && dr[0] != DBNull.Value)
                sum = (int) dr[0];
        });
        return sum;
    }
}

而且，如果沒有在匿名類型中指定成員名稱，編譯器會為匿名類型成員指定與用于初始化這些成員的屬性相同的名稱，那么設置參數的代碼可以進一步簡化，需要特別注意sql參數是大小寫敏感的，所以要求sql語句中的參數名和匿名類成員名一樣：

private int GetBookSales(int bookId) {
    ...
    "select sum(Amount) from OrderDetail where BookId = @bookId",
    new {bookId},   // 省略成員名稱
    ...
}

權衡：雖然這個技巧簡化了代碼，但它使重命名重構操作變得危險，如果在修改上面方法的bookId參數名時，忘記同時修改sql語句中的參數名，就會導致程序出錯，所以是否使用這個技巧需要權衡。我的建議是除非每個開發人員都非常熟悉它，否則任何時候都不要省略成員名，并且一旦確定了采用（或不用）該技巧，那么整個項目代碼要保持一致，這樣有利于習慣的形成。

接下來的問題是解決將匿名類解析為查詢參數，可以使用反射的方法實現：

public void ExecuteReader(string sql, object parameters, Action<DbDataReader> action) {
    ...
    if (parameters != null) {
        var t = parameters.GetType();
        foreach (var pi in t.GetProperties()) {
            var p = command.CreateParameter();
            p.ParameterName = pi.Name;
            p.Value = pi.GetValue(parameters, null);
            command.Parameters.Add(p);
        }
    }
    ...
}

有人可能想說反射慢，但比起sql語句的執行用時，反射只占其中很小的一部分，在這里我不想過早優化。

現在我已經實現了Feature4“支持查詢參數”的功能，全部代碼如下：

public class Database {
    private readonly DbProviderFactory _ProviderFactory;
    private readonly string _ConnectionString;

    public Database(string connectionStringName = "*") {
        var connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionStringName];
        var connectionString = connectionStringSettings.ConnectionString;
        _ConnectionString = connectionString;
        _ProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
    }

    public void ExecuteReader(string sql, object parameters, Action<DbDataReader> action) {
        using (var connection = _ProviderFactory.CreateConnection()) {
            connection.ConnectionString = _ConnectionString;
            connection.Open();
            using (var command = connection.CreateCommand()) {
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                if (parameters != null) {
                    var t = parameters.GetType();
                    foreach (var pi in t.GetProperties()) {
                        var p = command.CreateParameter();
                        p.ParameterName = "@" + pi.Name;
                        p.Value = pi.GetValue(parameters, null);
                        command.Parameters.Add(p);
                    }
                }

                using (var dr = command.ExecuteReader()) {
                    action.Invoke(dr);
                }
            }
        }
    }
}

接下來實現插入、更新、刪除數據功能。

Feature5：插入、更新、刪除數據

插入、更新、刪除數據全可以用一個ExecuteNonQuery方法實現，有了目前的代碼基礎，可以很容易的實現它：

public class Database {
    private readonly DbProviderFactory _ProviderFactory;
    private readonly string _ConnectionString;

    public Database(string connectionStringName = "*") {
        var connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionStringName];
        var connectionString = connectionStringSettings.ConnectionString;
        _ConnectionString = connectionString;
        _ProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
    }

    public void ExecuteReader(string sql, object parameters, Action<DbDataReader> action) {
        using (var connection = _ProviderFactory.CreateConnection()) {
            connection.ConnectionString = _ConnectionString;
            connection.Open();
            using (var command = connection.CreateCommand()) {
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                if (parameters != null) {
                    var t = parameters.GetType();
                    foreach (var pi in t.GetProperties()) {
                        var p = command.CreateParameter();
                        p.ParameterName = "@" + pi.Name;
                        p.Value = pi.GetValue(parameters, null);
                        command.Parameters.Add(p);
                    }
                }

                using (var dr = command.ExecuteReader()) {
                    action.Invoke(dr);
                }
            }
        }
    }

    public int ExecuteNonQuery(string sql, object parameters) {
        using (var connection = _ProviderFactory.CreateConnection()) {
            connection.ConnectionString = _ConnectionString;
            connection.Open();
            using (var command = connection.CreateCommand()) {
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                if (parameters != null) {
                    var t = parameters.GetType();
                    foreach (var pi in t.GetProperties()) {
                        var p = command.CreateParameter();
                        p.ParameterName = "@" + pi.Name;
                        p.Value = pi.GetValue(parameters, null);
                        command.Parameters.Add(p);
                    }
                }

                return command.ExecuteNonQuery();
            }
        }
    }
}

ExecuteReader和ExecuteNonQuery中出現了很多重復代碼，DRY原則提醒我，現在需要重構了。

重構1：抽取建立連接方法

兩個方法的一開始都是創建連接對象并打開它，這可以抽取為一個公用方法：

public class Database {
    private DbConnection CreateConnection() {
        var connection = _ProviderFactory.CreateConnection();
        connection.ConnectionString = _ConnectionString;
        connection.Open();
        return connection;
    }

    public void ExecuteReader(string sql, object parameters, Action<DbDataReader> action) {
        using (var connection = CreateConnection()) {
            ...
        }
    }

    public int ExecuteNonQuery(string sql, object parameters) {
        using (var connection = CreateConnection()) {
            ...
        }
    }

    ...
}

重構2：抽取創建命令對象方法

兩個方法的另一部分重復代碼是創建command對象并對其屬性賦值，也把它抽取為公用方法：

public class Database {
    private DbCommand CreateCommand(DbConnection connection, string sql, object parameters) {
        var command = connection.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = sql;

        if (parameters != null) {
            var t = parameters.GetType();
            foreach (var pi in t.GetProperties()) {
                var p = command.CreateParameter();
                p.ParameterName = "@" + pi.Name;
                p.Value = pi.GetValue(parameters, null);
                command.Parameters.Add(p);
            }
        }

        return command;
    }

    public void ExecuteReader(string sql, object parameters, Action<DbDataReader> action) {
        using (var connection = CreateConnection()) {
            using (var command = CreateCommand(connection, sql, parameters)) {
                ...
            }
        }
    }

    public int ExecuteNonQuery(string sql, object parameters) {
        using (var connection = CreateConnection()) {
            using (var command = CreateCommand(connection, sql, parameters)) {
                ...
            }
        }
    }
}

重構3：分離設置查詢參數代碼

CreateCommand方法不僅創建了command對象，還設置了sql語句和查詢參數，SRP原則提醒我這個方法實現了多個職責，應該將其分離。在這里可以將設置參數的代碼抽取到一個新的SetParameters方法中，不過為了保證代碼的可讀性，我打算使用擴展方法實現它。

首先確定我們要擴展的是DbCommand類，所以增加一個DbCommandExtensions靜態類，在這個類的SetParameters方法中完成查詢參數的設置：

public static class DbCommandExtensions {
    public static void SetParameters(this DbCommand cmd, object parameters) {
        cmd.Parameters.Clear();

        if (parameters == null)
            return;

        var t = parameters.GetType();
        var parameterInfos = t.GetProperties();
        foreach (var pi in parameterInfos) {
            var p = cmd.CreateParameter();
            p.ParameterName = pi.Name;
            p.Value = pi.GetValue(parameters, null) ?? DBNull.Value;
            cmd.Parameters.Add(p);
        }
    }
}

更進一步，foreach循環中的代碼是完成增加查詢參數的功能，可以再將它抽取到AddParameter方法中：

public static class DbCommandExtensions {
    public static void SetParameters(this DbCommand cmd, object parameters) {
        cmd.Parameters.Clear();

        if (parameters == null)
            return;

        var t = parameters.GetType();
        var parameterInfos = t.GetProperties();
        foreach (var pi in parameterInfos) {
            AddParameter(cmd, pi.Name, pi.GetValue(parameters, null));
        }
    }

    private static void AddParameter(DbCommand cmd, string name, object value) {
        var p = cmd.CreateParameter();
        p.ParameterName = name;
        p.Value = value ?? DBNull.Value;
        cmd.Parameters.Add(p);
    }
}

然后修改CreateCommand方法的代碼：

private DbCommand CreateCommand(DbConnection connection, string sql, object parameters) {
    var command = connection.CreateCommand();
    command.CommandType = CommandType.Text;
    command.CommandText = sql;
    command.SetParameters(parameters);  // 調用擴展方法
    return command;
}

這樣CreateCommand方法的可讀性更好了。

Feature6：重構查詢方法

如果使用目前的代碼編寫一個查詢所有圖書的方法，代碼如下：

public IList<Book> GetAllBooks() {
    var books = new List<Book>();
    new Database("product").ExecuteReader("select * from Book", null, dr => {
        while (dr.Read()) {
            var book = new Book { Id = (int)dr["Id"], Name = (string)dr["Name"] };
            books.Add(book);
        }
    });

    return books;
}

這段代碼有些變扭，讀取的數據是在委托方法體中填充到業務對象的，而不是Database.ExecuteReader()方法返回的，代碼不夠清晰，而友好的查詢方法用起來應該是這樣：

public IList<Book> GetAllBooks() {
    var books = new Database("product").ExecuteReader("select * from Book", null, dr => {
        var book = new Book {Id = (int) dr["Id"], Name = (string) dr["Name"]};
        return book;
    });

    return books;
}

對于調用代碼
消費DataReader的委托方法不再負責維護讀取器的前進，而僅僅消費當前DataReader指向的記錄，并將消費結果返回，一般情況下，消費結果是數據填充后的業務對象；
對于數據庫查詢方法
負責DataReader狀態的維護，并返回消費DataReader委托方法返回值的集合。

這樣做仍然是從SRP原則出發，調用代碼只負責消費數據和確定返回集合的元素類型，查詢方法負責維護讀取器狀態和返回查詢結果，職責分明。

下面開始重構代碼吧。

重構1：由ExecuteReader方法維護DataReader的前進

只需要把while(dr.Read())語句從調用代碼移動到ExecuteReader方法中即可：

public void ExecuteReader(string sql, object parameters, Action<DbDataReader> action) {
    using (var connection = CreateConnection()) {
        using (var command = CreateCommand(connection, sql, parameters)) {
            using (var dr = command.ExecuteReader()) {
                while(dr.Read())
                    action.Invoke(dr);
            }
        }
    }
}

調用代碼改為：

var books = new List<Book>();
new Database("product").ExecuteReader("select * from Book", null, dr => {
    var book = new Book {Id = (int) dr["Id"], Name = (string) dr["Name"]};
    books.Add(book);
});

重構2：調用代碼返回消費結果，ExecuteReader方法返回查詢結果集合

調用代碼改為：

var books = new Database("product").ExecuteReader("select * from Book", null, dr => {
    var book = new Book {Id = (int) dr["Id"], Name = (string) dr["Name"]};
    return book;
});

而ExecuteReader方法有下面幾點改動：

委托方法需要具備返回值，所以第三個參數類型改為Func<DbDataReader, T>，泛型參數T表示消費返回結果類型，它一般是業務對象的類型；
方法返回值變為集合類型IList<T>，因為使用了泛型參數，所以整個方法變為泛型方法；
方法中定義了查詢結果變量result，在遍歷sql查詢結果集的過程中填充，最后作為返回值。

public IList<T> ExecuteReader<T>(string sql, object parameters, Func<DbDataReader, T> action) {
    var result = new List<T>();
    using (var connection = CreateConnection()) {
        using (var command = CreateCommand(connection, sql, parameters)) {
            using (var dr = command.ExecuteReader()) {
                while (dr.Read()) {
                    var item = action.Invoke(dr);
                    result.Add(item);
                }
            }
        }
    }
    return result;
}

重構3：更進一步，使用`IEnumerable<T>`接口延遲加載數據

現在ExecuteReader方法的返回值類型是IList<T>，對于所有返回集合類的方法，我都會考慮使用IEnumerable<T>接口作為返回值的類型，因為IEnumerable接口和yield語句可使方法具備延遲計算功能，這也就是為什么多個Linq方法可以積攢到一起執行的原因。

采用yield語句重寫ExecuteReader方法：

public IEnumerable<T> ExecuteReader<T>(string sql, object parameters, Func<DbDataReader, T> action) {
    using (var connection = CreateConnection()) {
        using (var command = CreateCommand(connection, sql, parameters)) {
            using (var dr = command.ExecuteReader()) {
                while (dr.Read()) {
                    yield return action.Invoke(dr);
                }
            }
        }
    }
}

經過這樣的改造后，ExecuteReader方法具備了延遲計算功能：在使用Where()、OrderBy()、GroupBy()、Concat()等方法時，并不會立即查詢數據庫，只有在需要得到結果的時候才會真正執行，這對數據查詢場景非常有用，不僅僅是lazy-loading，更重要的是，它返回迭代器，而不是集合對象，只有迭代器當前指向的對象才需要內存，而不是把整個查詢結果都加載到內存中。

延遲加載的陷阱

上面所說的“需要得到結果的時候”是指：

使用foreach遍歷IEnumeralbe<T>對象；
調用IEnumerable<T>對象的Count()、First()、Max()、Average()、All()、Any()、ToArray()、ToList()、ToDictionary()等擴展方法；

所以下面的代碼會查詢多次數據庫：

var books = new Database("product").ExecuteReader(...);
// 第一次查詢數據庫
foreach (var book in books)
    Console.WriteLine(book.Name);
// 再次查詢數據庫
var totalBooks = books.Count();
// 第三次查詢數據庫
var firstBook = books.OrderBy(book => book.Name).FirstOrDefault();

當調用關系復雜時，IEnumerable<T>對象會作為方法的參數和返回值在多個方法中傳遞，這時更容易出現“重復執行”的問題，一種解決方法是在一開始獲得IEnumerable<T>結果時，就是用ToList()方法強制執行，這樣返回的對象類型為List<T>，無論后續如何調用都不會產生重復計算的問題，但這么做也失去了數據延遲加載的優點，所以這又是實際使用中需要權衡的地方。

var books = new Database("product").ExecuteReader(...).ToList();    // 強制執行
// 第一次查詢數據庫
foreach (var book in books)
    Console.WriteLine(book.Name);
// 不會再次查詢數據庫
var totalBooks = books.Count();
var firstBook = books.OrderBy(book => book.Name).FirstOrDefault();

我的想法是讓ExecuteReader方法的返回值為IEnumerable<T>類型，并具備延遲加載功能，具體是否使用，交給調用者決定。

另一種用到數據延遲加載的場景

有時我們會將數據庫作為消息隊列使用，在消費端，利用延遲加載的特性實現就非常合適，即可以保證在內存中只加載隊列中的一條數據，還可以靈活控制處理流程，根據條件判斷是否要提前結束數據的遍歷。

比如下面的代碼是從Message表中獲取待處理的消息，如果出現3次錯誤則通過拋出異常提前結束結果集的遍歷：

private IEnumerable<Message> FindTodoMessages() {
    return new Database("product").ExecuteReader("select * from Message where Status = @todo", new {todo = "todo"}, Message.GetByDataReader);
}

public void ProcessMessages() {
    var messages = FindTodoMessages();

    int errors = 0;
    foreach (var message in messages) {
        try {
            DispatchMessage(message);
        }
        catch {
            if (++errors >= 3) 
                throw new AppDomainUnloadedException("too many errors, abort.");
        }
    }
}

FindTodoMessages方法中有個值得注意的地方，從DataReader讀取數據的代碼被抽取到了Message類的GetByDataReader方法中，這同樣基于SRP原則考慮，從DataReader讀取數據是Message類的職責，可以將它實現為一個簡單工廠方法：

public class Message {
    public int Id { get; set; }
    public string Status { get; set; }

    public static Message GetByDataReader(DbDataReader dr) {
        return new Message {
            Id = (int)dr["Id"],
            Status = (string)dr["Status"]
        };
    }
}

這樣，領域類負責從DataReader中創建一個領域對象，數據訪問層的方法負責執行sql，職責又一次分離了。

現在，我完成了重構，重構后的查詢方法具有明確意義的返回值，在方法內部維護了DataReader讀取器的狀態，并具備延遲查詢功能，為調用者提供了靈活易用的方法。不要小看這一步步的重構，正是它們讓你的代碼更漂亮，堅持長期審視、重構代碼，提高你的思考能力和編碼水平，無他，惟手熟爾。

Feature7：事務處理

1. 最初的想法有bug

有了現在的代碼基礎，我認為實現事務處理功能非常簡單，事務處理代碼通過委托方法指定，如果沒有異常提交事務，否則回滾：

public void ExecuteTransaction(Action action) {
    using (var connection = CreateConnection()) {
        using (var transaction = connection.BeginTransaction()) {
            try {
                using (var cmd = connection.CreateCommand()) {
                    cmd.Transaction = transaction;
                    action.Invoke();
                }
                transaction.Commit();
            }
            catch {
                transaction.Rollback();
                throw;
            }
        }
    }
}

一個插入訂單和訂單明細的事務代碼為：

public void CreateOrder() {
    var db = new Database("order");
    db.ExecuteTransaction(() => {
        var orderId = db.ExecuteReader(@"insert into [Order](Status, TotalPrice) values(@Status, @TotalPrice); select SCOPE_IDENTITY()",
            new { @Status = "new", TotalPrice = 89.3 }, dr => Convert.ToInt32(dr[0]))
            .FirstOrDefault();

        db.ExecuteNonQuery("insert into OrderDetail(OrderId, BookId, Amount) values(@OrderId, @BookId, @Amount)",
            new {orderId, BookId = 1, Amount = 2});
    });
}

但這段代碼并不具備事務功能，仔細查看代碼后發現，插入訂單和訂單明細的操作仍然使用沒有事務關聯的db對象，并且ExecuteTransaction方法中調用委托方法時，也沒有使用綁定事務的command對象。

2. 修正bug，但不好用

那么我嘗試將專門為事務創建的DbCommand對象傳遞給委托方法：

public void ExecuteTransaction(Action<DbCommand> action) {
    using (var connection = CreateConnection()) {
        using (var transaction = connection.BeginTransaction()) {
            try {
                using (var cmd = connection.CreateCommand()) {
                    cmd.Transaction = transaction;
                    action.Invoke(cmd);
                }
                transaction.Commit();
            }
            catch {
                transaction.Rollback();
                throw;
            }
        }
    }
}

然而在嘗試使用使用ExecuteTransaction方法編寫事務處理代碼時，由于委托方法的參數是DbCommand類型，雖然通過它可以設置sql語句、設置參數、執行和查詢，并能正確的處理事務，但卻無法利用我已經編寫好的Database.ExecuteReader和Database.ExecuteNonQuery方法，這兩個方法用起來比ADO.NET的DbCommand類更加方便，我希望在事務代碼中仍然能使用它們。

3. 改進易用性，但代碼有bad smell

既然要用自己編寫的API，所以我把ExecuteTransaction方法的參數改為Action<Database>：

public void ExecuteTransaction(Action<Database> action) {
    using (var connection = CreateConnection()) {
        using (var transaction = connection.BeginTransaction()) {
            try {
                using (var cmd = connection.CreateCommand()) {
                    cmd.Transaction = transaction;
                    action.Invoke(?);   // how?
                }
                transaction.Commit();
            }
            catch {
                transaction.Rollback();
                throw;
            }
        }
    }
}

不過我遇到了困難：如何調用事務委托方法？action.Invoke(?)的參數要求：

參數是一個Database類型的對象；
設法把專門為事務創建的command對象（第5行代碼）傳遞給它；
在這個Database對象內部使用傳入的command進行數據操作。

既然這樣，我想可以為Database增加一個新的構造方法Database(DbCommand command)，并將通過構造方法注入的DbCommand作為數據成員，在方法ExecuteReader和ExecuteNonQuery中判斷：如果成員變量_Command不為null，則使用它來操作數據，否則建立新連接和新的command對象：

public class Database {
    private readonly DbProviderFactory _ProviderFactory;
    private readonly string _ConnectionString;
    private readonly DbCommand _Command;

    public Database(string connectionStringName = "*") {
        var connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionStringName];
        var connectionString = connectionStringSettings.ConnectionString;
        _ConnectionString = connectionString;
        _ProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
    }

    public Database(DbCommand command) {
        _Command = command;
    }

    private DbConnection CreateConnection() {
        var connection = _ProviderFactory.CreateConnection();
        connection.ConnectionString = _ConnectionString;
        connection.Open();
        return connection;
    }

    private DbCommand CreateCommand(DbConnection connection, string sql, object parameters) {
        var command = _Command ?? connection.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = sql;
        command.SetParameters(parameters);
        return command;
    }

    public IEnumerable<T> ExecuteReader<T>(string sql, object parameters, Func<DbDataReader, T> action) {
        if (_Command != null) {
            var command = CreateCommand(null, sql, parameters);
            using (var dr = command.ExecuteReader()) {
                while (dr.Read()) {
                    yield return action.Invoke(dr);
                }
            }
        }
        else {
            using (var connection = CreateConnection()) {
                using (var command = CreateCommand(connection, sql, parameters)) {
                    using (var dr = command.ExecuteReader()) {
                        while (dr.Read()) {
                            yield return action.Invoke(dr);
                        }
                    }
                }
            }
        }
    }

    public int ExecuteNonQuery(string sql, object parameters) {
        if (_Command != null) {
            var command = CreateCommand(null, sql, parameters);
            return command.ExecuteNonQuery();
        }

        using (var connection = CreateConnection()) {
            using (var command = CreateCommand(connection, sql, parameters)) {
                return command.ExecuteNonQuery();
            }
        }
    }

    public void ExecuteTransaction(Action<Database> action) {
        using (var connection = CreateConnection()) {
            using (var transaction = connection.BeginTransaction()) {
                try {
                    using (var cmd = connection.CreateCommand()) {
                        cmd.Transaction = transaction;

                        action.Invoke(new Database(cmd));
                    }

                    transaction.Commit();
                }
                catch {
                    transaction.Rollback();
                    throw;
                }
            }
        }
    }
}

使用這個版本的Database類編寫事務處理代碼時，在委托方法中需要使用委托方法的參數值tx操作數據，而不能用執行事務的db對象：

public void CreateOrder() {
    var db = new Database("order");
    db.ExecuteTransaction((tx) => { // 使用tx對象操作數據
        var orderId = tx.ExecuteReader(@"insert into [Order](Status, TotalPrice) values(@Status, @TotalPrice); select SCOPE_IDENTITY()",
            new { @Status = "new", TotalPrice = 89.3 }, dr => Convert.ToInt32(dr[0]))
            .FirstOrDefault();

        tx.ExecuteNonQuery("insert into OrderDetail(OrderId, BookId, Amount) values(@OrderId, @BookId, @Amount)",
            new {orderId, BookId = 1, Amount = 2});
    });
}

如此，事務處理功能就實現了，但Database類中的if-else判斷是bad smell代碼，到重構的時候了。

4. 重構：使用繼承替換if-else判斷

這次重構的規模有些大，不是方法級別的重構，而是在類級別進行。對于代碼中的if-else判斷，有個重構“套路”——使用繼承關系改寫。目前Database類其實兼任兩種角色，一種是每次都新建連接、新建命令對象，然后再進行數據庫訪問，另一種是在事務作用域中進行數據庫操作，這兩種角色也是導致代碼中出現if-else分支的原因，那么現在我將把Database類按照這兩種角色進行分解。

首先建立一個接口IDatabase，把目前Database類中的所有public方法在這個接口中定義，ExecuteTransaction參數的泛型參數類型從Database改為IDatabase。

public interface IDatabase {
    IEnumerable<T> ExecuteReader<T>(string sql, object parameters, Func<DbDataReader, T> action);
    int ExecuteNonQuery(string sql, object parameters);
    void ExecuteTransaction(Action<IDatabase> action);
}

然后實現在事務作用域中進行數據庫操作的Database類——DatabaseInTx：

public class DatabaseInTx : IDatabase {
    private readonly DbCommand _Command;

    public DatabaseInTx(DbCommand command) {    // 要點1
        _Command = command;
    }

    private void PrepareCommand(string sql, object parameters) {    // 要點2
        _Command.CommandType = CommandType.Text;
        _Command.CommandText = sql;
        _Command.SetParameters(parameters);
    }
    public IEnumerable<T> ExecuteReader<T>(string sql, object parameters, Func<DbDataReader, T> action) {
        PrepareCommand(sql, parameters);
        using (var dr = _Command.ExecuteReader()) {
            while (dr.Read())
                yield return action.Invoke(dr);
        }
    }

    public int ExecuteNonQuery(string sql, object parameters) {
        PrepareCommand(sql, parameters);
        return _Command.ExecuteNonQuery();
    }

    public void ExecuteTransaction(Action<IDatabase> action) {
        if (action != null)
            action.Invoke(this);    // 要點1
    }
}

實現要點是：

ExecuteTransaction方法中將this作為調用事務委托方法的參數，這樣委托方法中使用的IDatabase對象就是當前的DatabaseInTx實例，而它使用的是構造方法中注入的DbCommand對象操作數據庫，并且在注入前，這個DbCommand對象已經和事務綁定；
因為DbCommand對象通過構造方法注入，原來的Database.CreateCommand()方法就不需要創建對象了，只需要設置sql語句和查詢參數，所以方法名我改為更貼切的PrepareCommand。

建立新類DatabaseInTx后，現在重構Databaes類，剔除事務作用域中的數據庫操作，只保留新建連接對象、新建命令對象、執行操作的職責：

public class Database : IDatabase {
    private readonly DbProviderFactory _ProviderFactory;
    private readonly string _ConnectionString;

    public Database(string connectionStringName = "*") {
        var connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionStringName];
        var connectionString = connectionStringSettings.ConnectionString;
        _ConnectionString = connectionString;
        _ProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
    }

    private DbConnection CreateConnection() {
        var connection = _ProviderFactory.CreateConnection();
        connection.ConnectionString = _ConnectionString;
        connection.Open();
        return connection;
    }

    public IEnumerable<T> ExecuteReader<T>(string sql, object parameters, Func<DbDataReader, T> action) {
        using (var connection = CreateConnection()) {
            using (var cmd = connection.CreateCommand()) {
                var db = new DatabaseInTx(cmd); // 要點1
                foreach (var item in db.ExecuteReader(sql, parameters, action))
                    yield return item;  // 要點2
            }
        }
    }

    public int ExecuteNonQuery(string sql, object parameters) {
        using (var connection = CreateConnection()) {
            using (var cmd = connection.CreateCommand()) {
                var db = new DatabaseInTx(cmd); // 要點1
                return db.ExecuteNonQuery(sql, parameters);
            }
        }
    }

    public void ExecuteTransaction(Action<IDatabase> action) {
        using (var connection = CreateConnection()) {
            using (var transaction = connection.BeginTransaction()) {
                try {
                    using (var cmd = connection.CreateCommand()) {
                        cmd.Transaction = transaction;
                        var db = new DatabaseInTx(cmd); // 要點3
                        db.ExecuteTransaction(action);
                    }
                    transaction.Commit();
                }
                catch {
                    transaction.Rollback();
                    throw;
                }
            }
        }
    }
}

改動要點有：

原來的ExecuteReader、ExecuteNonQuery方法中，我會通過connection對象的工廠方法創建command對象，然后設置command的sql語句和參數，最后執行。而改動后方法中通過調用DatabaseInTx類中的對應方法完成設置sql語句、參數以及執行操作，所以在這兩個方法中，復用了DatabaseInTx類中的代碼，并且CreateCommand方法也不需要保留了；
ExecuteReader中使用yield return返回查詢結果，而不能直接調用return db.ExecuteReader(sql, parameters, action)，這是因為DatabaseInTx.ExecuteReader()方法具備延遲執行特性，當調用Database.ExecuteReader()時，會依次執行打開數據庫連接、創建command對象，實例化DatabaseInTx對象db，調用db.ExecuteReader方法，因為db.ExecuteReader方法是延遲執行的，所以此時不會執行數據庫操作，代碼繼續運行釋放command對象和連接對象，等到真正遍歷查詢查詢結果時，db.ExecuteReader方法才開始執行，但此時command對象和connection對象已經釋放，會拋出異常“connection已經關閉”。所以必須使用yield return語句使Database.ExecuteReader()方法也具備延遲執行特性；
在ExecuteTransaction方法中同樣創建了DatabaseInTx對象，并將已經和事務關聯的command對象注入（回憶DatabaseInTx類的實現要點1），然后調用DatabaseInTx對象的ExecuteTransaction方法，將委托方法傳入。

現在我完成了對Database類的重構，將它按照職責分離出一個新類DatabaseInTx，并新建了一個定義數據庫訪問行為的IDatabase接口，整個重構過程分成了幾個較小的步驟，每個步驟中都是實現功能、找到不足、思考改進方案的閉環，所有公開類和方法簽名都沒有發生變化，這意味著已有的生產和測試代碼不必修改。

好了，目前我已經完成了數據庫訪問類的全部預期功能，如開始所說，這只是起始代碼，如果想要實際使用，還需要理解它、改進它。下面談談我為什么要寫這篇文章。

這篇文章的目的

你也許發現這篇文章有些不同，它記錄了嘗試、思考和權衡的創作過程，而不僅僅是最終的系統介紹，如果你在閱讀的同時一邊寫代碼，你會發現它是慢慢生長出來的。

這個數據庫訪問類是我4年前寫出來的，陸續也在幾個產品中使用，到目前沒有發現明顯的錯誤和性能問題，之后我不斷對它進行修補，但整體的技術方案并沒有變化，也沒有做出更好的改進。這是我寫此文的初衷，我想敘述自己的設計思路和重構方法，不僅讓別人知其然、更能知其所以然，因為做到后者才會有自己的獨立見解，而這正是我愿意聆聽和思考的。

最后，在項目中我也許會大干快上，甚至靠蠻力解決問題，但對于技術修養，我認為應精雕細琢、去浮華存本真，希望有朝一日能百煉成鋼、水滴石穿。

也許這就是術、道之別。

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1408675180100.html

數據庫訪問類 .NET開發

如何做個好用的數據庫訪問類

我需要什么樣的數據庫訪問類？

它必須具備

它不需要

talk is cheap, show me the code

Feature1：支持多種類型數據庫

Feature2：自動釋放數據庫連接及相關資源

無論sql語句執行過程中是否出現異常，資源用完之后立即釋放

不需要調用者發出釋放資源的信號

Feature3：支持多個數據庫

Feature4：支持查詢參數

最初的想法：使用具體類的實例設置查詢參數

改進1：使用Action 委托設置查詢參數

改進2：使用匿名類設置查詢參數

Feature5：插入、更新、刪除數據

重構1：抽取建立連接方法

重構2：抽取創建命令對象方法

重構3：分離設置查詢參數代碼

Feature6：重構查詢方法

重構1：由ExecuteReader方法維護DataReader的前進

重構2：調用代碼返回消費結果，ExecuteReader方法返回查詢結果集合

重構3：更進一步，使用`IEnumerable<T>`接口延遲加載數據

延遲加載的陷阱

另一種用到數據延遲加載的場景

Feature7：事務處理

1. 最初的想法有bug

2. 修正bug，但不好用

3. 改進易用性，但代碼有bad smell

4. 重構：使用繼承替換if-else判斷

這篇文章的目的

相關經驗

相關資訊

相關文檔

目錄

如何做個好用的數據庫訪問類

我需要什么樣的數據庫訪問類？

它必須具備

它不需要

talk is cheap, show me the code

Feature1：支持多種類型數據庫

Feature2：自動釋放數據庫連接及相關資源

無論sql語句執行過程中是否出現異常，資源用完之后立即釋放

不需要調用者發出釋放資源的信號

Feature3：支持多個數據庫

Feature4：支持查詢參數

最初的想法：使用具體類的實例設置查詢參數

改進1：使用Action 委托設置查詢參數

改進2：使用匿名類設置查詢參數

Feature5：插入、更新、刪除數據

重構1：抽取建立連接方法

重構2：抽取創建命令對象方法

重構3：分離設置查詢參數代碼

Feature6：重構查詢方法

重構1：由ExecuteReader方法維護DataReader的前進

重構2：調用代碼返回消費結果，ExecuteReader方法返回查詢結果集合

重構3：更進一步，使用IEnumerable<T>接口延遲加載數據

延遲加載的陷阱

另一種用到數據延遲加載的場景

Feature7：事務處理

1. 最初的想法有bug

2. 修正bug，但不好用

3. 改進易用性，但代碼有bad smell

4. 重構：使用繼承替換if-else判斷

這篇文章的目的

相關經驗

相關資訊

相關文檔

目錄

重構3：更進一步，使用`IEnumerable<T>`接口延遲加載數據