将弹性数据库客户端库与 Dapper 配合使用Using the elastic database client library with Dapper

适用于: Azure SQL 数据库

本文档面向依赖于使用 Dapper 生成应用程序,但同时想要运用弹性数据库工具创建应用程序来实现分片,以横向扩展其数据层的开发人员。This document is for developers that rely on Dapper to build applications, but also want to embrace elastic database tooling to create applications that implement sharding to scale out their data tier. 本文档演示了与弹性数据库工具集成所需的基于 Dapper 的应用程序发生的更改。This document illustrates the changes in Dapper-based applications that are necessary to integrate with elastic database tools. 我们将重点介绍如何使用 Dapper 构建弹性数据库分片管理和数据依赖型路由。Our focus is on composing the elastic database shard management and data-dependent routing with Dapper.

示例代码Azure SQL 数据库的弹性数据库工具 - Dapper 集成Sample Code: Elastic database tools for Azure SQL Database - Dapper integration.

DapperDapperExtensions 与 Azure SQL 数据库的弹性数据库客户端库的过程很简单。Integrating Dapper and DapperExtensions with the elastic database client library for Azure SQL Database is easy. 将新 SqlConnection 对象的创建和打开方式更改为使用来自客户端库OpenConnectionForKey 调用,应用程序即可使用数据依赖型路由。Your applications can use data-dependent routing by changing the creation and opening of new SqlConnection objects to use the OpenConnectionForKey call from the client library. 这会将应用程序中的更改限制为已创建和打开新连接的位置。This limits changes in your application to only where new connections are created and opened.

Dapper 概述Dapper overview

Dapper 是对象关系映射器。Dapper is an object-relational mapper. 它将应用程序中的 .NET 对象映射到关系型数据库(或者执行相反的映射)。It maps .NET objects from your application to a relational database (and vice versa). 示例代码的第一个部分演示了如何将弹性数据库客户端库与基于 Dapper 的应用程序相集成。The first part of the sample code illustrates how you can integrate the elastic database client library with Dapper-based applications. 示例代码的第二个部分演示了同时使用 Dapper 和 DapperExtensions 时如何集成。The second part of the sample code illustrates how to integrate when using both Dapper and DapperExtensions.

Dapper 中的映射器功能对数据库连接提供扩展方法,可以简化用于执行或查询数据库的 T-SQL 语句的提交。The mapper functionality in Dapper provides extension methods on database connections that simplify submitting T-SQL statements for execution or querying the database. 例如,使用 Dapper 可以轻松地在 .NET 对象与用于 Execute 调用的 SQL 语句参数之间进行映射,或者在 Dapper 中通过 Query 调用来使用对 .NET 对象执行 SQL 查询后返回的结果。For instance, Dapper makes it easy to map between your .NET objects and the parameters of SQL statements for Execute calls, or to consume the results of your SQL queries into .NET objects using Query calls from Dapper.

使用 DapperExtensions 时,不再需要提供 SQL 语句。When using DapperExtensions, you no longer need to provide the SQL statements. 对数据库连接执行 GetListInsert 等扩展方法会在幕后创建 SQL 语句。Extensions methods such as GetList or Insert over the database connection create the SQL statements behind the scenes.

Dapper 和 DapperExtensions 的另一个优点在于,应用程序可以控制数据库连接的创建。Another benefit of Dapper and also DapperExtensions is that the application controls the creation of the database connection. 这有助于与弹性数据库客户端库交互,从而可以通过将 shardlet 映射到数据库来中转数据库连接。This helps interact with the elastic database client library which brokers database connections based on the mapping of shardlets to databases.

若要获取 Dapper 程序集,请参阅 Dapper .NETTo get the Dapper assemblies, see Dapper dot net. 有关 Dapper 扩展,请参阅 DapperExtensionsFor the Dapper extensions, see DapperExtensions.

弹性数据库客户端库速览A quick look at the elastic database client library

使用弹性数据库客户端库,可以定义应用程序数据的分区(称为 shardlet),将它们映射到数据库,并根据分片键来识别这些分区 。With the elastic database client library, you define partitions of your application data called shardlets, map them to databases, and identify them by sharding keys. 可以根据需要创建任意数目的数据库,并在这些数据库之间分布 shardlet。You can have as many databases as you need and distribute your shardlets across these databases. 分片键值到数据库的映射由库的 API 提供的分片映射存储。The mapping of sharding key values to the databases is stored by a shard map provided by the library's APIs. 此功能称为 分片映射管理This capability is called shard map management. 分片映射还为带有分片键的请求充当数据库连接的代理。The shard map also serves as the broker of database connections for requests that carry a sharding key. 此功能称为数据依赖型路由。This capability is referred to as data-dependent routing.

分片映射和数据依赖型路由

分片映射管理器可防止用户不一致的视图到 shardlet 数据时并发 shardlet 管理操作发生在数据库上可能会出现。The shard map manager protects users from inconsistent views into shardlet data that can occur when concurrent shardlet management operations are happening on the databases. 为此,分片映射会代理使用库生成的应用程序的数据库连接。To do so, the shard maps broker the database connections for an application built with the library. 当分片管理操作可能会影响 shardlet 时,这可以允许分片映射功能自动终止数据库连接。When shard management operations could impact the shardlet, this allows the shard map functionality to automatically kill a database connection.

需要使用 OpenConnectionForKey 方法,而不是使用传统方法来创建 Dapper 的连接。Instead of using the traditional way to create connections for Dapper, you need to use the OpenConnectionForKey method. 这可确保所有验证都会发生,并在分片之间移动任何数据时正确管理连接。This ensures that all the validation takes place and connections are managed properly when any data moves between shards.

Dapper 集成的要求Requirements for Dapper integration

在使用弹性数据库客户端库和 Dapper API 时,希望保留以下属性:When working with both the elastic database client library and the Dapper APIs, you want to retain the following properties:

  • 横向扩展:我们需要根据应用程序的容量需求,在分片应用程序的数据层中添加或删除数据库。Scale out: We want to add or remove databases from the data tier of the sharded application as necessary for the capacity demands of the application.
  • 一致性:由于应用程序是使用分片横向扩展的,因此需要执行数据依赖型路由。Consistency: Since the application is scaled out using sharding, you need to perform data-dependent routing. 我们需要使用库的数据依赖型路由功能来实现此目的。We want to use the data-dependent routing capabilities of the library to do so. 具体而言,需要保留验证和一致性保证(由通过分片映射管理器中转的连接提供,目的在于避免损坏或错误的查询结果)。In particular, you want to retain the validation and consistency guarantees provided by connections that are brokered through the shard map manager in order to avoid corruption or wrong query results. 这可确保(举例而言)当前已使用 Split/Merge API 将 shardlet 移至其他分片时,拒绝或停止与给定 shardlet 的连接。This ensures that connections to a given shardlet are rejected or stopped if (for instance) the shardlet is currently moved to a different shard using Split/Merge APIs.
  • 对象映射:我们需要保留 Dapper 为了在应用程序中的类和基础数据库结构之间进行转换而提供的映射方便性。Object Mapping: We want to retain the convenience of the mappings provided by Dapper to translate between classes in the application and the underlying database structures.

以下部分基于 DapperDapperExtensions 的应用程序的这些要求的指南。The following section provides guidance for these requirements for applications based on Dapper and DapperExtensions.

技术指南Technical guidance

数据依赖型路由与 DapperData-dependent routing with Dapper

使用 Dapper 时,应用程序通常负责创建和打开与基础数据库的连接。With Dapper, the application is typically responsible for creating and opening the connections to the underlying database. 如果应用程序指定了类型 T,则 Dapper 将查询结果返回为 T 类型的 .NET 集合。Dapper 执行从 T-SQL 结果行到 T 类型对象的映射。同样,Dapper 将 .NET 对象映射到数据操作语言 (DML) 语句的 SQL 值或参数。Given a type T by the application, Dapper returns query results as .NET collections of type T. Dapper performs the mapping from the T-SQL result rows to the objects of type T. Similarly, Dapper maps .NET objects into SQL values or parameters for data manipulation language (DML) statements. Dapper 通过扩展方法的 ADO.NET SQL 客户端库中的常规 SqlConnection 对象提供此功能。Dapper offers this functionality via extension methods on the regular SqlConnection object from the ADO .NET SQL Client libraries. DDR 弹性缩放 API 返回的 SQL 连接也是常规 SqlConnection 对象。The SQL connection returned by the Elastic Scale APIs for DDR are also regular SqlConnection objects. 这样,我们便可以针对客户端库的 DDR API 返回的类型直接使用 Dapper 扩展,因为它也是一个简单的 SQL 客户端连接。This allows us to directly use Dapper extensions over the type returned by the client library's DDR API, as it is also a simple SQL Client connection.

根据这些规则,可以方便地使用 Dapper 的弹性数据库客户端库中转的连接。These observations make it straightforward to use connections brokered by the elastic database client library for Dapper.

此代码示例(摘自随附的示例)演示了应用程序在库中提供分片键,以将连接中转到正确分片的方案。This code example (from the accompanying sample) illustrates the approach where the sharding key is provided by the application to the library to broker the connection to the right shard.

    using (SqlConnection sqlconn = shardingLayer.ShardMap.OpenConnectionForKey(
                     key: tenantId1,
                     connectionString: connStrBldr.ConnectionString,
                     options: ConnectionOptions.Validate))
    {
        var blog = new Blog { Name = name };
        sqlconn.Execute(@"
                      INSERT INTO
                            Blog (Name)
                            VALUES (@name)", new { name = blog.Name }
                        );
    }

调用 OpenConnectionForKey API 会替换 SQL 客户端连接的默认创建和打开方法。The call to the OpenConnectionForKey API replaces the default creation and opening of a SQL Client connection. OpenConnectionForKey 调用采用数据依赖型路由所需的参数:The OpenConnectionForKey call takes the arguments that are required for data-dependent routing:

  • 用于访问数据依赖型路由接口的分片映射The shard map to access the data-dependent routing interfaces
  • 用于标识 shardlet 的分片键The sharding key to identify the shardlet
  • 用于连接分片的凭据(用户名和密码)The credentials (user name and password) to connect to the shard

分片映射对象会与保存给定分片键 shardlet 的分片建立连接。The shard map object creates a connection to the shard that holds the shardlet for the given sharding key. 弹性数据库客户端 API 还会标记连接以实现一致性保证。The elastic database client APIs also tag the connection to implement its consistency guarantees. 由于调用 OpenConnectionForKey 会返回一个常规 SQL 客户端连接对象,因此从 Dapper 后续调用 Execute 扩展方法遵循标准的 Dapper 做法。Since the call to OpenConnectionForKey returns a regular SQL Client connection object, the subsequent call to the Execute extension method from Dapper follows the standard Dapper practice.

查询的工作方式非常类似 - 首先从客户端 API 使用 OpenConnectionForKey 打开连接。Queries work very much the same way - you first open the connection using OpenConnectionForKey from the client API. 然后,可以使用常规 Dapper 扩展方法将 SQL 查询的结果映射到 .NET 对象:Then you use the regular Dapper extension methods to map the results of your SQL query into .NET objects:

    using (SqlConnection sqlconn = shardingLayer.ShardMap.OpenConnectionForKey(
                    key: tenantId1,
                    connectionString: connStrBldr.ConnectionString,
                    options: ConnectionOptions.Validate ))
    {
           // Display all Blogs for tenant 1
           IEnumerable<Blog> result = sqlconn.Query<Blog>(@"
                                SELECT *
                                FROM Blog
                                ORDER BY Name");

           Console.WriteLine("All blogs for tenant id {0}:", tenantId1);
           foreach (var item in result)
           {
                Console.WriteLine(item.Name);
            }
    }

请注意,将块与 DDR 连接一起使用会将块中的所有数据库操作划归到保存 tenantId1 的一个分片。Note that the using block with the DDR connection scopes all database operations within the block to the one shard where tenantId1 is kept. 该查询仅返回当前分片中存储的博客,而不是任何其他分片中存储的博客。The query only returns blogs stored on the current shard, but not the ones stored on any other shards.

数据依赖型路由与 Dapper 和 DapperExtensionsData-dependent routing with Dapper and DapperExtensions

Dapper 随附了可以在开发数据库应用程序时提供更大方便性和从数据库抽象其他扩展的生态系统。Dapper comes with an ecosystem of additional extensions that can provide further convenience and abstraction from the database when developing database applications. DapperExtensions 就是一个示例。DapperExtensions is an example.

在应用程序中使用 DapperExtensions 不会更改创建和管理数据库连接的方式。Using DapperExtensions in your application does not change how database connections are created and managed. 应用程序仍要负责打开连接,并且扩展方法要求使用常规 SQL 客户端连接对象。It is still the application's responsibility to open connections, and regular SQL Client connection objects are expected by the extension methods. 我们可以依赖于上述 OpenConnectionForKeyWe can rely on the OpenConnectionForKey as outlined above. 如以下代码示例所示,唯一的变化是不再需要编写 T-SQL 语句:As the following code samples show, the only change is that you no longer have to write the T-SQL statements:

    using (SqlConnection sqlconn = shardingLayer.ShardMap.OpenConnectionForKey(
                    key: tenantId2,
                    connectionString: connStrBldr.ConnectionString,
                    options: ConnectionOptions.Validate))
    {
           var blog = new Blog { Name = name2 };
           sqlconn.Insert(blog);
    }

下面是查询的代码示例:And here is the code sample for the query:

    using (SqlConnection sqlconn = shardingLayer.ShardMap.OpenConnectionForKey(
                    key: tenantId2,
                    connectionString: connStrBldr.ConnectionString,
                    options: ConnectionOptions.Validate))
    {
           // Display all Blogs for tenant 2
           IEnumerable<Blog> result = sqlconn.GetList<Blog>();
           Console.WriteLine("All blogs for tenant id {0}:", tenantId2);
           foreach (var item in result)
           {
               Console.WriteLine(item.Name);
           }
    }

处理暂时性故障Handling transient faults

Microsoft 模式和实践团队发布了暂时性故障处理应用程序块,以帮助应用程序开发人员消除在云中运行应用程序时遇到的常见暂时性故障状态。The Microsoft Patterns & Practices team published the Transient Fault Handling Application Block to help application developers mitigate common transient fault conditions encountered when running in the cloud. 有关详细信息,请参阅坚持不懈,所有胜利的秘密:使用暂时性故障处理应用程序块For more information, see Perseverance, Secret of All Triumphs: Using the Transient Fault Handling Application Block.

该代码示例依赖于暂时性故障库来防止暂时性故障。The code sample relies on the transient fault library to protect against transient faults.

    SqlDatabaseUtils.SqlRetryPolicy.ExecuteAction(() =>
    {
       using (SqlConnection sqlconn =
          shardingLayer.ShardMap.OpenConnectionForKey(tenantId2, connStrBldr.ConnectionString, ConnectionOptions.Validate))
          {
              var blog = new Blog { Name = name2 };
              sqlconn.Insert(blog);
          }
    });

上述代码中的 SqlDatabaseUtils.SqlRetryPolicy 定义为 SqlDatabaseTransientErrorDetectionStrategy,重试计数为 10,每两次重试的等待时间为 5 秒。SqlDatabaseUtils.SqlRetryPolicy in the code above is defined as a SqlDatabaseTransientErrorDetectionStrategy with a retry count of 10, and 5 seconds wait time between retries. 如果正在使用事务,请确保在出现暂时性故障的情况下重试范围可以恢复为事务开始时间。If you are using transactions, make sure that your retry scope goes back to the beginning of the transaction in the case of a transient fault.

限制Limitations

本文档中概述的方法存在一些限制:The approaches outlined in this document entail a couple of limitations:

  • 本文档示例代码未演示如何管理不同分片的架构。The sample code for this document does not demonstrate how to manage schema across shards.
  • 对于给定的请求,我们假设它的所有数据库处理都包含在该请求提供的分片键标识的单个分片内。Given a request, we assume that all its database processing is contained within a single shard as identified by the sharding key provided by the request. 但是,这种假设并不总是合理,例如,在无法使用某个分片键的情况下。However, this assumption does not always hold, for example, when it is not possible to make a sharding key available. 为了解决此问题,弹性数据库客户端库包含了 MultiShardQuery 类To address this, the elastic database client library includes the MultiShardQuery class. 该类实现了一个连接抽象用于查询多个分片。The class implements a connection abstraction for querying over several shards. MultiShardQuery 与 Dapper 的结合使用超出了本文档的讨论范围。Using MultiShardQuery in combination with Dapper is beyond the scope of this document.

结论Conclusion

使用 Dapper 和 DapperExtensions 的应用程序很容易从 Azure SQL 数据库的弹性数据库工具受益。Applications using Dapper and DapperExtensions can easily benefit from elastic database tools for Azure SQL Database. 通过本文档中所述的步骤,这些应用程序可以使用该工具的功能,通过将新 SqlConnection 对象的创建和打开方式更改为使用弹性数据库客户端库的 OpenConnectionForKey 调用,来实现数据依赖型路由。Through the steps outlined in this document, those applications can use the tool's capability for data-dependent routing by changing the creation and opening of new SqlConnection objects to use the OpenConnectionForKey call of the elastic database client library. 这会将应用程序更改限制为已创建和打开新连接的位置。This limits the application changes required to those places where new connections are created and opened.

其他资源Additional resources

尚未使用弹性数据库工具?Not using elastic database tools yet? 请查看入门指南Check out our Getting Started Guide.