Asynchronous ADO.Net (BeginExecute/EndExecute)

Problem: we need to run a SQL command for one item synchronously (wait to avoid race condition), but then run it again asynchronously on a second set of items (“fire-and-forget”). We don’t need to wait on results from the second set of items and would also like to avoid timing out in case the second set is large.

Example:

dal.RunExample(new [] { 1 }, false); // Block on 1 to avoid race condition (async=false)...
... do something...
dal.RunExample(new [] { 3, 4, 5, 6, 7, 8, 9 }, true); // ...but fire-and-forget on 3 thru 9 (async=true)

One way to solve the problem is to leverage ADO.Net Asynchronous command execution (BeginExecute/EndExecute).

using Microsoft.Practices.EnterpriseLibrary.Data.Sql; // for SqlDatabase

/// 

/// Use sparingly. See
/// http://msdn.microsoft.com/en-us/library/ca56w9se.aspx
/// http://blogs.msdn.com/b/angelsb/archive/2004/09/02/224964.aspx        
///

private string AsyncConnectionString { get { const string AsyncEnabled = “Asynchronous Processing=true”; if (_connectionString.Contains(AsyncEnabled)) { return _connectionString; } return String.Format(“{0}; {1}”, _connectionString, AsyncEnabled); } } public void RunExample(IEnumerable ids, bool async = false) { try { if (releaseIds == null) { throw new ArgumentNullException(“ids”); } string sqlText = CreateMultiSqlTextForExample(ids); if(String.IsNullOrEmpty(sqlText)) { return; // nothing to do } var connectionString = async ? AsyncConnectionString : _connectionString; var sqlDatabase = new SqlDatabase(connectionString); if (!async) { sqlDatabase.ExecuteNonQuery(CommandType.Text, sqlText); } else { // Execute asynchronously so we don’t block the thread (we must return before timing out) sqlDatabase.BeginExecuteNonQuery( CommandType.Text, sqlText, delegate(IAsyncResult ar) // define our callback using anonynous method { try { sqlDatabase.EndExecuteNonQuery(ar); } catch (Exception ex) { // TODO: Handle exception here or throw if we can’t } // When we exit our clojure here there won’t be any references to our SqlDatabase instance, so it’ll be disposed later by the GC (and so will its connection). }, null); } } catch (Exception ex) { // TODO: Handle exception here or throw if we can’t } } // NOTE: since we are enumerating int rather than string, SQL injection isn’t a big concern in this case // if it were, we could use parameterized query syntax or perhaps batched stored procedure execution private string CreateMultiSqlTextForExample(IEnumerable ids) { var sqlBuilder = new StringBuilder(); foreach (int id in ids) { sqlBuilder.AppendFormat(“exec p_myStoredProc {0}”, id); } return sqlBuilder.ToString(); }

This implementation is preferred over explicitly pulling a thread from the thread pool, especially if we are running in IIS:

  • We don’t have to worry about the details of thread management.
  • The thread is used later upon completion of the request (rather than waiting for it), which is usually shorter-lived than if we had pulled a thread from the thread pool.

I’m using the SqlDatabase class from Microsoft.Practices.EnterpriseLibrary.Data.Sql (Microsoft Enterprise Library Data Access Application Block [DAAB]) because it handles the details of closing the connection when the instance goes out of scope, both in the synchronous case when we exit the method, and in the asynchronous case when we finish our clojure (exit our callback/delegate). In the asynchronous case the connection isn’t closed until we have completed our callback.

Note that if we call the method in asynchronous mode we cannot participate in an transaction (cannot put it inside a TransactionScope block) with normal synchronous calls to the database. This is because we would be using two separate connections. A prerequisite for a transaction is that it be on one connection—if multiple connections are involved the MS DTC Service must be enabled on the server running SQL Server because it’s treated as a distributed transaction.