15、ADO.NET 最佳实践：打造高效数据访问架构

ADO.NET 最佳实践：打造高效数据访问架构

1. 明确系统需求

在软件开发中，我们常常面临着客户需求不明确以及项目范围不断扩大的问题。客户可能对软件了解甚少，却期望我们解决他们的业务问题。而数据访问作为软件的基础，其架构需要既稳固又灵活。

例如，在处理数据完整性时，客户可能提出简单的要求，但我们不能盲目听从，而应选择更合适的解决方案。比如在实现事务时，避免将 UI 交互作为事务的一部分，而是采用多个事务并在每个 UI 交互之间进行完整性检查。这样可以确保系统不依赖于特定用户的独占锁和随意操作，为其他用户提供服务。

2. 为正确的工作选择合适的工具

2.1 数据访问工具的选择

在使用 ADO.NET 进行数据访问时，需要根据具体需求选择合适的工具。例如，当需要与 SQL Server 数据库交互，并且有一个在应用程序生命周期内不发生变化的国家名称查找功能时，对于完全托管的应用程序，应优先选择 SqlClient 而非 OleDb。同时，将国家名称查找数据存储在内存缓存对象中，避免每次请求都从数据库中检索，以提高性能。

2.2 Data Reader 与 DataSet/Data Adapter 的选择

2.2.1 内存消耗

Data Reader 每次只加载一行数据到内存中，因此内存消耗较少。而 DataSet 是专门为存储大量数据的内存缓存设计的，内存消耗相对较大。如果内存紧张，应考虑使用 Data Reader；如果内存不是首要考虑因素，DataSet 的增强功能可能更适合需求。但需要注意的是，如果长时间打开 Data Reader，可能会对连接池性能产生负面影响。

2.2.2 遍历方向

如果只是简单地以 HTML 形式显示结果集中的所有记录，Data Reader 提供的只读/向前的访问方式可以快速读取和遍历数据，具有性能优势。但如果需要对内存缓存进行写操作、随机访问任意行或进行耗时的处理，可能需要考虑其他方案。

2.2.3 多结果集

Data Reader 和 DataSet 都支持多结果集。Data Reader 通过 NextResult 方法访问额外的结果集，且每次只保留一行信息。而 DataSet 通过表来支持多结果集。

2.2.4 连接池

DataSet 是数据库独立的，在处理数据时可以保持连接关闭，有利于连接池的使用。而 Data Reader 在处理数据时连接保持打开，在高并发场景下可能会导致性能下降。

2.3 DataSet 与强类型 DataSet

普通 DataSet 虽然试图模拟内存中的关系数据结构，但在表示实际数据类型方面存在不足，需要在每个点进行检查和条件判断。强类型 DataSet 虽然解决了数据类型的问题，但需要不断更新结构以反映底层表结构，涉及代码生成和重新编译，维护和部署可能会成为难题。在这种情况下，可能需要考虑使用自定义业务对象。

2.4 DataSet 与业务对象

2.4.1 业务对象的优势

• 沟通便利 ：业务对象更符合客户的业务逻辑，便于与客户进行沟通。
• 数据调试 ：易于捕获、隔离和调试异常数据。
• 行为封装 ：可以将数据的行为封装在业务对象中，减少架构各层的逻辑。
• 跨平台支持 ：可以通过 XML 序列化将业务对象暴露给非 .NET 世界。
• 第三方工具支持 ：可以利用第三方工具实现业务对象与数据库的转换。
• 接口和继承 ：可以通过接口和继承实现业务对象的统一行为和实现。
• 开发调试 ：在 Visual Studio 2005 中可以重用 UI 逻辑创建可视化工具，便于开发和调试。
• 测试驱动开发 ：业务对象易于重新创建，便于编写测试用例。
• 数据通知 ：数据可以通知变化或问题。
• 聚合关系 ：可以定义业务对象的聚合关系。
• 数据模板 ：可以实现数据模板。

2.4.2 业务对象的劣势

• 开发成本 ：需要编写业务对象及其持久化逻辑。
• 版本管理 ：随着应用需求的增长，需要管理更多的业务对象及其版本。
• 动态 SQL ：生成的 SQL 查询是动态的，查询计划可能不会被缓存。
• 代码编写 ：对于新实体需要编写新的对象。
• 并发管理 ：并发管理相对困难。
• 数据绑定 ：需要实现 ITypedList 和 IBindingList 以启用数据绑定。
• 框架依赖 ：可能会错过 Microsoft 未来版本的增强功能。

2.5 T-SQL、SQLCLR 与扩展存储过程（XP）

比较项	T-SQL	SQLCLR	扩展存储过程（XP）
操作类型	适合声明性、基于集合的操作	适合递归调用、数学运算、字符串操作等	除了在调用它的同一数据库上进行数据访问外，性能较好
执行方式	解释执行	编译执行	-
功能特点	有丰富的数据中心函数库	具有更丰富的功能和更好的语言表达能力	-
事务处理	难以脱离当前事务和启动另一个数据库的事务	可以轻松实现	-
安全性	-	执行的 T-SQL 作为动态 SQL，不遵循链式安全访问和编译时语法检查	无法进行细粒度的访问权限限制
性能	优化程度高	可能有较大的内存分配和更好的并发性能，但有从本地代码到托管代码的转换开销	-

对于大量的过程逻辑，应将其分离到用 SQLCLR 编写的单独数据库对象中；对于基于集合的操作，T-SQL 是更好的选择。但最终的性能判断还需要通过比较测试来确定。

2.6 事务的选择

为了保护数据的完整性，需要使用事务将多个操作绑定为一个原子单元。事务有多种类型，管理开销越大，性能越低。常见的事务类型及特点如下：
1. 隐式事务 ：自动与任何单个 SQL 语句关联，确保该语句执行期间的数据完整性。
2. BEGIN TRANSACTION 和 COMMIT/ROLLBACK 语句块 ：需要较高的管理开销和锁定更多资源，可能存在嵌套事务。
3. DbTransaction 对象包裹 DbCommand 对象 ：具体实现取决于 .NET 数据提供程序，需要注意嵌套事务和事务计数。
4. System.Transactions 自动登记 ：在单数据库场景下效果较好，但在某些情况下可能会升级为 MSDTC 管理，成本较高。
5. SQLCLR 或 ADO.NET 代码登记事务 ：默认由 MSDTC 管理，成本较高。
6. 分布式或松散耦合系统的事务 ：需要自己创建回滚/失败机制，不能直接使用 MSDTC 等。

在选择事务类型时，应选择满足需求的最低级别的事务架构，避免使用过于复杂的事务。同时，优先选择使用 API（即 DbTransaction 对象）来包装事务，避免 SQL 和 API 中的事务相互提交或回滚。

3. 避免明显的错误

3.1 实现数据层

在大多数情况下，实现数据层是一个好主意。数据层可以是一组类、Web 服务、应用程序服务器或任何用于数据访问的代码。实现数据层的好处包括：
- 隔离框架的变化，确保整个架构不受影响。
- 确保每个人都遵循最佳实践。
- 确保每个人使用相同的连接字符串，便于连接池管理。
- 方便进行性能测量和问题排查。

3.2 关闭连接

忘记关闭数据库连接是一个严重的问题，可能会导致应用程序在高负载下出现性能问题。由于 DbConnection 对象占用的是宝贵的网络连接资源，垃圾回收器不会自动处理，因此需要手动关闭连接。建议尽可能晚地打开连接，并尽早关闭连接。

3.3 网络延迟

在大型应用程序中，网络延迟是性能的敌人。应尽量减少获取和打开连接、执行命令以及通过网络返回结果的操作次数。可以通过预取数据、使用 DataSet 作为内存缓存等方式来减少网络往返次数，提高性能。

3.4 复杂的分层 DataSet

虽然可以获取更多的数据，但不建议将整个数据库加载到 DataSet 中。原因如下：
- 较大的 DataSet 会消耗更多的内存。
- 保存分层数据时需要考虑数据关系，可能会导致死锁。
- 运行 GetChanges 和 Merge 操作的时间会随着 DataSet 中行数、表数和关系数的增加而呈指数级增长。
- 循环引用会导致 GetChanges、Merge 和 XmlSerialization 逻辑效率低下。

一般来说，一个大的单表、两个中等大小的表或三个小表是 DataSet 的一个较好的参考大小，但可以根据实际情况进行调整。

3.5 数据缓存

每个应用程序都可以从数据缓存中受益。例如，ASP.NET 应用程序可以使用 HttpContext.Cache 对象缓存数据，减少对数据库的访问次数。同时，可以使用 SqlDependency 等对象在底层数据发生变化时使缓存失效，或者设置过期策略。对于 Windows 应用程序和服务，可以利用底层的 Win32 函数构建类似的缓存 API。

总结

在使用 ADO.NET 进行数据访问时，需要根据具体需求和场景选择合适的工具和技术，遵循最佳实践，避免常见的错误。通过合理的架构设计和性能优化，可以提高应用程序的性能和稳定性，为用户提供更好的体验。

下面是一个简单的 mermaid 流程图，展示了选择 Data Reader 还是 DataSet 的决策过程：

graph TD;
    A[需要访问数据] --> B{内存是否紧张?};
    B -- 是 --> C[考虑使用 Data Reader];
    B -- 否 --> D{是否需要写操作或随机访问?};
    D -- 否 --> E{是否有大量耗时处理?};
    E -- 否 --> F[Data Reader 可能有性能优势];
    E -- 是 --> G[考虑其他方案];
    D -- 是 --> H[考虑使用 DataSet];

通过以上的分析和建议，希望能帮助你在使用 ADO.NET 进行数据访问时做出更明智的决策。

4. 关键技术点详细分析

4.1 数据访问工具选择的深入考量

在选择数据访问工具时，除了前面提到的 SqlClient 和 OleDb 的比较，还需要考虑其他因素。例如，当应用程序需要与多种不同类型的数据库进行交互时，OleDb 可能提供更广泛的兼容性，但性能上会有所损失。而 SqlClient 则专门针对 SQL Server 进行了优化，提供了更高效的性能和更丰富的功能。

操作步骤：
1. 确定应用程序需要访问的数据库类型。
2. 如果只与 SQL Server 交互，优先考虑 SqlClient。
3. 如果需要兼容多种数据库，可考虑 OleDb，但要注意性能问题。
4. 对于需要频繁访问且数据不经常变化的内容，如国家名称查找，将其存储在内存缓存对象中。具体实现可以使用 .NET 中的 Cache 类，示例代码如下：

// 检查缓存中是否存在国家名称查找数据
if (HttpContext.Current.Cache["CountryLookup"] == null)
{
    // 从数据库中获取数据
    DataTable countryTable = GetCountryLookupDataFromDatabase();
    // 将数据存入缓存
    HttpContext.Current.Cache.Insert("CountryLookup", countryTable);
}
// 从缓存中获取数据
DataTable cachedCountryTable = (DataTable)HttpContext.Current.Cache["CountryLookup"];

4.2 Data Reader 与 DataSet 的性能优化

4.2.1 Data Reader 的性能优化

Data Reader 适合快速读取大量数据，为了进一步优化其性能，可以采取以下措施：
- 尽量减少不必要的字段读取，只选择需要的列。
- 避免在读取数据时进行复杂的计算和处理，将这些操作放在数据读取完成后进行。
- 及时关闭 Data Reader 和数据库连接，释放资源。

操作步骤：

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand("SELECT Column1, Column2 FROM TableName", connection);
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // 只处理需要的列
            string column1Value = reader.GetString(0);
            int column2Value = reader.GetInt32(1);
            // 进行简单的数据处理
        }
    }
    // 连接会在 using 块结束时自动关闭
}

4.2.2 DataSet 的性能优化

DataSet 适合需要对数据进行多次操作和处理的场景，优化其性能可以从以下方面入手：
- 控制 DataSet 的大小，避免加载过多的数据。
- 合理使用 DataAdapter 的 Fill 方法，只填充需要的数据。
- 对于需要更新的数据，使用 DataAdapter 的 Update 方法时，尽量批量更新，减少与数据库的交互次数。

操作步骤：

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM TableName", connection);
    DataSet dataSet = new DataSet();
    // 只填充需要的数据
    adapter.Fill(dataSet, "TableName");
    // 对 DataSet 中的数据进行操作
    DataTable table = dataSet.Tables["TableName"];
    foreach (DataRow row in table.Rows)
    {
        row["Column1"] = "New Value";
    }
    // 批量更新数据
    SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);
    adapter.Update(dataSet, "TableName");
}

4.3 业务对象的设计与实现

业务对象的设计需要遵循一定的原则，以充分发挥其优势。以下是设计和实现业务对象的步骤：
1. 确定业务实体 ：根据业务需求，确定需要抽象的业务实体，如保险业务中的政策、保费、支付计划等。
2. 定义属性和方法 ：为业务对象定义属性来表示实体的状态，定义方法来实现实体的行为。
3. 实现数据访问逻辑 ：可以使用数据访问层来实现业务对象与数据库之间的数据交互，避免业务对象直接与数据库交互。
4. 考虑继承和接口 ：通过继承和接口实现业务对象的统一行为和实现，提高代码的可维护性和可扩展性。

示例代码：

// 定义业务对象
public class Policy
{
    public int PolicyId { get; set; }
    public string PolicyName { get; set; }
    public decimal Premium { get; set; }

    public void CalculatePremium()
    {
        // 实现保费计算逻辑
    }
}

// 数据访问层
public class PolicyDataAccess
{
    public Policy GetPolicyById(int policyId)
    {
        // 从数据库中获取政策数据
        return new Policy();
    }

    public void SavePolicy(Policy policy)
    {
        // 将政策数据保存到数据库
    }
}

4.4 事务管理的最佳实践

事务管理是保证数据完整性的关键，以下是事务管理的最佳实践：
- 选择合适的事务类型 ：根据业务需求，选择满足需求的最低级别的事务架构，避免使用过于复杂的事务。
- 避免嵌套事务 ：嵌套事务会增加管理开销和复杂性，尽量避免使用。
- 使用 try-catch 块 ：在事务处理过程中，使用 try-catch 块捕获异常，并在异常发生时进行回滚操作。

操作步骤：

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlTransaction transaction = connection.BeginTransaction();
    try
    {
        SqlCommand command1 = new SqlCommand("UPDATE Table1 SET Column1 = 'Value1'", connection, transaction);
        command1.ExecuteNonQuery();

        SqlCommand command2 = new SqlCommand("INSERT INTO Table2 (Column2) VALUES ('Value2')", connection, transaction);
        command2.ExecuteNonQuery();

        // 提交事务
        transaction.Commit();
    }
    catch (Exception ex)
    {
        // 回滚事务
        transaction.Rollback();
    }
}

5. 总结与展望

在数据访问领域，选择合适的工具和技术对于应用程序的性能和稳定性至关重要。通过对数据访问工具、Data Reader 与 DataSet、业务对象、事务管理等方面的深入分析，我们了解了它们的优缺点和适用场景。

在实际应用中，需要根据具体需求和场景进行综合考虑，遵循最佳实践，避免常见的错误。同时，不断关注技术的发展和变化，及时调整和优化数据访问架构，以适应不断变化的业务需求。

以下是一个 mermaid 流程图，展示了事务选择的决策过程：

graph TD;
    A[需要事务处理] --> B{是否为单个 SQL 语句?};
    B -- 是 --> C[隐式事务];
    B -- 否 --> D{是否需要自定义事务块?};
    D -- 是 --> E[BEGIN TRANSACTION 和 COMMIT/ROLLBACK 语句块];
    D -- 否 --> F{是否使用 .NET 数据提供程序?};
    F -- 是 --> G[DbTransaction 对象包裹 DbCommand 对象];
    F -- 否 --> H{是否为单数据库场景?};
    H -- 是 --> I[System.Transactions 自动登记];
    H -- 否 --> J{是否为分布式或松散耦合系统?};
    J -- 是 --> K[自己创建回滚/失败机制];
    J -- 否 --> L[SQLCLR 或 ADO.NET 代码登记事务];

通过以上的内容，希望能帮助你在数据访问领域做出更明智的决策，提高应用程序的性能和稳定性。