From sync to async: SQL

Previously I discussed some common sync scenarios using network and file I/O and how they can be migrated to modern async patterns. Today I have an addendum about asynchronous programming with SQL connections.

Let me start by saying that async SQL programming in .NET is not actually new. The legacy async programming model (IAsyncResult and friends) has been supported since .NET 2.0 — check out these blasts from the past. Async is even easier now in .NET 4.5 using the Task-based async methods of SqlConnection and SqlCommand which as it happens are little more than wrappers around the existing Begin/End methods. (They do have a true async Open call now, so that’s a relief.) Consult the ADO.NET blog for more details.

Remember that asynchronous does not imply concurrent. You cannot issue multiple async commands in parallel against a single SqlConnection instance unless you enable Multiple Active Result Sets (“MARS”). Of course, this comes with its own set of caveats and should not be used lightly.

Alas, there are some more limitations. Firstly, SqlConnection works with a full blown SQL Server instance (that includes a local, remote, or even an Azure instance) but not SQL Server Compact Edition or SQLite. There are various techniques to simulate async by offloading work to the thread pool (e.g. AsyncTableQuery in the SQLite.Net.Async PCL) but apparently no true async implementations yet.

Secondly, users of LINQ to SQL should note that it uses synchronous APIs to do its work. However, there is a clever workaround to combine LINQ with async SQL documented by Scott Hanselman. The trick involves asking DataContext to convert the expression to a SqlCommand via GetCommand and then handling the execution explicitly using the async methods:

private static async Task LinqDemoAsync(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        DataContext db = new DataContext(connection);
        await GetRowAsync(db, 1);
        await GetRowAsync(db, 2);
        await GetRowAsync(db, 3);
    }
}

private static async Task GetRowAsync(DataContext db, int id)
{
    Table<Demo> table = db.GetTable<Demo>();
    var query = from r in table where r.Id == id select r;
    Demo item = await QueryAsync(db, query);
    Console.WriteLine(item);
}

private static async Task<T> QueryAsync<T>(DataContext db, IQueryable<T> query)
{
    using (SqlCommand command = (SqlCommand)db.GetCommand(query))
    using (SqlDataReader reader = await command.ExecuteReaderAsync())
    {
        return db.Translate<T>(reader).First();
    }
}

[Table]
private sealed class Demo
{
    [Column]
    public int Id { get; set; }

    [Column]
    public string Name { get; set; }

    public override string ToString()
    {
        return "(" + this.Id + ", " + this.Name + ")";
    }
}

Now go forth and write async queries and inserts!