如何做个好用的数据库访问类
数据库访问组件是应用系统开发中的基础组件，在用过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年前写出来的，陆续也在几个产品中使用，到目前没有发现明显的错误和性能问题，之后我不断对它进行修补，但整体的技术方案并没有变化，也没有做出更好的改进。这是我写此文的初衷，我想叙述自己的设计思路和重构方法，不仅让别人知其然、更能知其所以然，因为做到后者才会有自己的独立见解，而这正是我愿意聆听和思考的。
最后，在项目中我也许会大干快上，甚至靠蛮力解决问题，但对于技术修养，我认为应精雕细琢、去浮华存本真，希望有朝一日能百炼成钢、水滴石穿。
也许这就是术、道之别。