Some questions concerning the combination of ADO.NET, Dapper QueryAsync and Glimpse.ADO

ado.net asp.net dapper glimpse

Question

I have been experimenting with a lightweight solution for handling my business logic. It consists of a vanilla ADO.NET connection that is extended with Dapper, and monitored by Glimpse.ADO. The use case for this setup will be a web application that has to process a handful of queries asynchronously per request. Below a simple implementation of my setup in an MVC controller.

public class CatsAndDogsController : Controller
{
    public async Task<ActionResult> Index()
    {
        var fetchCatsTask = FetchCats(42);
        var fetchDogsTask = FetchDogs(true);
        await Task.WhenAll(fetchCatsTask, fetchDogsTask);
        ViewBag.Cats = fetchCatsTask.Result;
        ViewBag.Dogs = fetchDogsTask.Result;
        return View();
    }

    public async Task<IEnumerable<Cat>> FetchCats(int breedId)
    {
        IEnumerable<Cat> result = null;
        using (var connection = CreateAdoConnection())
        {
            await connection.OpenAsync();
            result = await connection.QueryAsync<Cat>("SELECT * FROM Cat WHERE BreedId = @bid;", new { bid = breedId });
            connection.Close();
        }
        return result;
    }

    public async Task<IEnumerable<Dog>> FetchDogs(bool isMale)
    {
        IEnumerable<Dog> result = null;
        using (var connection = CreateAdoConnection())
        {
            await connection.OpenAsync();
            result = await connection.QueryAsync<Dog>("SELECT * FROM Dog WHERE IsMale = @im;", new { im = isMale });
            connection.Close();
        }
        return result;
    }

    public System.Data.Common.DbConnection CreateAdoConnection()
    {
        var sqlClientProviderFactory = System.Data.Common.DbProviderFactories.GetFactory("System.Data.SqlClient");
        var dbConnection = sqlClientProviderFactory.CreateConnection();
        dbConnection.ConnectionString = "SomeConnectionStringToAwesomeData";
        return dbConnection;
    }
}

I have some questions concerning the creation of the connection in the CreateAdoConnection() method. I assume the following is happening behind the scenes.

The call to sqlClientProviderFactory.CreateConnection() returns an instance of System.Data.SqlClient.SqlConnection passed as a System.Data.Common.DbConnection. At this point Glimpse.ADO.AlternateType.GlimpseDbProviderFactory kicks in and wraps this connection in an instance of Glimpse.Ado.AlternateType.GlimpseDbConnection, which is also passed as a System.Data.Common.DbConnection. Finally, this connection is indirectly extended by the Dapper library with its query methods, among them the QueryAsync<>() method used to fetch the cats and dogs.

The questions:

  1. Is the above assumption correct?
  2. If I use Dapper's async methods with this connection - or create a System.Data.Common.DbCommand with this connection's CreateCommand() method, and use it's async methods - will those calls internally always end up using the vanilla async implementations of these methods as Microsoft has written them for System.Data.SqlClient.SqlConnection and System.Data.SqlClient.SqlCommand? And not some other implementations of these methods that are actually blocking?
  3. How much perf do I lose with this setup compared to just returning a new System.Data.SqlClient.SqlConnection directly? (So, without the Glimpse.ADO wrapper)
  4. Any suggestions on improving this setup?

Accepted Answer

  1. Yes pretty much. GlimpseDbProviderFactory wraps/decorates/proxies all the registered factories. We then pass any calls we get through to the factory we wrap (in this case SQL Server). In the case of CreateConnection() we ask the inner factory we have, to create a connection, when we get that connection, we wrap it and then return it to the originating caller
  2. Yes. Glimpse doesn't turn what was an async request into a blocking request. We persevere the async chain all the way though. If you are interested, the code in question is here.
  3. Very little. In essence, using a decorator pattern like this adds only one or two frames to the call stack. Compared to most operations performed during the request lifecycle, the time to observe whats happening here is extremely minimal.
  4. What you have looks great. Only suggestion is to maybe us this code to build the factory. This code means that you can shift your connection string, etc to the web.config.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why