How can I use Dapper with Elastic Scale MultiShardConnection and MultiShardDataReader?

Question

I'm trying to parse query results returned from an Azure Elastic Scale MultiShardConnection. It doesn't look like it inherits from SqlConnection or DbConnection so the Dapper methods are not available. This make sense when you consider that it's executing a fan-out query that is union'ed together. What I was hoping to do was to use existing Dapper functionality to just handle the parser of the reader results to a type.

Are those mapping features available if I don't use Dapper for the original connection?

Below are the types I'm working with:

MultiShardConnection : IDisposable
MultiShardCommand : DbCommand
MultiShardDataReader : DbDataReader, IDataReader, IDisposable, IDataRecord

Here's an example query where I'm trying to use the Dapper mapper.

Customer customer = null;
using (MultiShardConnection conn = GetMultiShardConnection())
using (MultiShardCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = "SELECT * FROM [Customer] WHERE ...";
    cmd.CommandTimeout = 120;
    cmd.CommandTimeoutPerShard = 120;

    using (MultiShardDataReader reader = await cmd.ExecuteReaderAsync())
    {
        while (reader.Read())
        {
            // Replace this with mapper...
            customer = new Customer()
            {
                CustomerId = reader.GetInt32(0)
                //etc...
            };
        }
    }
}
return customer;

Update

I ended up needing to use sp_execute_fanout

using (var con = GetConnection())
{
    await con.OpenAsync();
    return (await con.QueryAsync<Customer>("sp_execute_fanout ", new
    {
        shard_map_manager_server = "my_server.database.windows.net",
        shard_map_manager_database = "my_shardmapmananger",
        user_id = "my_username",
        password = "my_password",
        shard_map_name = "my_shardmap",
        statement = "SELECT * FROM Customer"
    }, commandTimeout: 120, commandType: CommandType.StoredProcedure)).FirstOrDefault();
}

score 2 · Answer 1 · answered Dec 10 '15 at 20:38

2

Currently, MultiShardConnection is not integrated with Dapper. The reason is exactly as you point out that it does not implement DbConnection. As an alternative solution, I would recommend to try elastic database query (EDQ): https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/. With EDQ, you can simply connect to a single database in Azure DB and use regular Dapper over the EDQ external tables to query across your shards. EDQ is now available on all service tiers in Azure SQL DB.

Let us know how that works for you.

Thanks, Torsten

answered Dec 10 '15 at 20:38

Torsten Grabs

481
1
3
5

Thanks Torsten for nudging me to consider options! Instead of using external tables we ended up calling sp_execute_fanout with Dapper. We didn't want to have to create additional external tables in case future queries required a join. Additionally, we didn't have a good home for an external table. Could you provide insight as to why it is not recommended to add custom tables to the ShardMapManager database itself? It seemed like it would be a great place to store just our External Tables which would be used for fan-out queries. Otherwise we need to make a new "core" database just for these. – Vyrotek Dec 10 '15 at 21:41
Putting your external tables into the ShardMapManager databases is just fine since the ShardMapManager databases is a regular database. Using fanout is a good approach, too, as long as you do not require any extensive post-processing in your app over the UNION ALL result. – Torsten Grabs Dec 11 '15 at 23:13

Joost · Answer 2 · 2022-11-17T13:40:07.040

I tried the solution from OP today, because I also wanted to use Dapper to map query results from multiple shards, but I noticed that sp_execute_fanout is deprecated and replaced by sp_execute_remote.

Before you can use this sp, you need to create an external source as a reference to the Shard Map Manager. You can then use the name of this external source as the data source name ('TestExtScr' in my example) and do something like this in your code:

using (var con = new SqlConnection(connString))
{
    return await con.QueryAsync<Customer>("sp_execute_remote", new
        {
            data_source_name = "TestExtSrc",
            statement = "SELECT foo FROM bar"
         }, commandTimeout: 120, commandType: CommandType.StoredProcedure);
}

This way you can use Dapper while querying multiple shards.

I know I'm bumping an old post, but when I was looking for a solution to my problem, this post kept showing up. So I added this bit in case someone in the future is looking for the same thing :) .

How can I use Dapper with Elastic Scale MultiShardConnection and MultiShardDataReader?

2 Answers2