{"id":3351,"date":"2015-03-11T13:00:20","date_gmt":"2015-03-11T13:00:20","guid":{"rendered":"http:\/\/writeasync.net\/?p=3351"},"modified":"2015-03-16T05:36:44","modified_gmt":"2015-03-16T05:36:44","slug":"from-sync-to-async-sql","status":"publish","type":"post","link":"http:\/\/writeasync.net\/?p=3351","title":{"rendered":"From sync to async: SQL"},"content":{"rendered":"

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

Let me start by saying that async SQL programming in .NET is not actually new. The legacy async programming model (IAsyncResult<\/code> and friends) has been supported since .NET 2.0<\/a> — check out these blasts<\/a> from the past<\/a>. Async is even easier now in .NET 4.5 using the Task<\/code>-based async methods of SqlConnection<\/a> and SqlCommand<\/a> 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<\/a>.) Consult the ADO.NET<\/a> blog<\/a> for more details.<\/p>\n

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

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

Secondly, users of LINQ to SQL<\/a> 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<\/a>. The trick involves asking DataContext<\/code> to convert the expression to a SqlCommand via GetCommand<\/code><\/a> and then handling the execution explicitly using the async methods:<\/p>\n

\r\nprivate static async Task LinqDemoAsync(string connectionString)\r\n{\r\n    using (SqlConnection connection = new SqlConnection(connectionString))\r\n    {\r\n        await connection.OpenAsync();\r\n        DataContext db = new DataContext(connection);\r\n        await GetRowAsync(db, 1);\r\n        await GetRowAsync(db, 2);\r\n        await GetRowAsync(db, 3);\r\n    }\r\n}\r\n\r\nprivate static async Task GetRowAsync(DataContext db, int id)\r\n{\r\n    Table<Demo> table = db.GetTable<Demo>();\r\n    var query = from r in table where r.Id == id select r;\r\n    Demo item = await QueryAsync(db, query);\r\n    Console.WriteLine(item);\r\n}\r\n\r\nprivate static async Task<T> QueryAsync<T>(DataContext db, IQueryable<T> query)\r\n{\r\n    using (SqlCommand command = (SqlCommand)db.GetCommand(query))\r\n    using (SqlDataReader reader = await command.ExecuteReaderAsync())\r\n    {\r\n        return db.Translate<T>(reader).First();\r\n    }\r\n}\r\n\r\n[Table]\r\nprivate sealed class Demo\r\n{\r\n    [Column]\r\n    public int Id { get; set; }\r\n\r\n    [Column]\r\n    public string Name { get; set; }\r\n\r\n    public override string ToString()\r\n    {\r\n        return "(" + this.Id + ", " + this.Name + ")";\r\n    }\r\n}\r\n<\/pre>\n
Now go forth and write async queries and inserts!<\/p>\n","protected":false},"excerpt":{"rendered":"
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… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21,91],"tags":[],"class_list":["post-3351","post","type-post","status-publish","format-standard","hentry","category-async","category-design"],"_links":{"self":[{"href":"http:\/\/writeasync.net\/index.php?rest_route=\/wp\/v2\/posts\/3351","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/writeasync.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/writeasync.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/writeasync.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/writeasync.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3351"}],"version-history":[{"count":0,"href":"http:\/\/writeasync.net\/index.php?rest_route=\/wp\/v2\/posts\/3351\/revisions"}],"wp:attachment":[{"href":"http:\/\/writeasync.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3351"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/writeasync.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3351"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/writeasync.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3351"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}