Using async code (with await) in .Net 4.0

Recently I needed to add a UI for marketing folks here to be able to edit some pages that are presented to players on logging in to the online casino. The pages show featured promotions, games and so on.

So I opened up Visual Studio 2012, and created a nice UI with upload controls and so on. The project targeted .Net 4.5 and I used async code (and by that I mean of course, the new syntax async code using the await keyword), including several of my own async helper methods, for the database calls. (This is the first time I’ve used async code in ASP.Net. Maybe that, async code in ASP.Net, deserves a post on its own… another time.) Then, lo and behold, I realized that the web server here is still Windows Server 2003 R2. You can’t install the 4.5 .net Framework on such an old server.

“Oh, now I’m screwed”, I thought. But no, a quick internet search revealed that .Net 4.0 can run async code, with a bit of work. Then I struggled half a day getting that to work. To save others the trouble, don’t believe everything you read. A BCL blog post will give you the impression that Microsoft.Bcl.Async is enough… It is not. You also need Microsoft.CompilerServices.AsyncTargetingPack. That’s all you need to know… the rest is straight-forward.

Here are my async SQL helper methods, for any who would like some free code:

  1. using System;
  2. using System.Collections;
  3. using System.Collections.Generic;
  4. using System.Configuration;
  5. using System.Data;
  6. using System.Data.SqlClient;
  7. using System.Linq;
  8. using System.Threading;
  9. using System.Threading.Tasks;
  10.  
  11. namespace Motd
  12. {
  13.     public static class SqlHelper
  14.     {
  15.         #region Fields
  16.  
  17.         private static int commandTimeout = 300;
  18.  
  19.         private static string connectionString = ConfigurationManager.ConnectionStrings[“MOTD”].ConnectionString;
  20.  
  21.         #endregion Fields
  22.  
  23.         #region Properties
  24.  
  25.         public static int CommandTimeout
  26.         {
  27.             get { return SqlHelper.commandTimeout; }
  28.         }
  29.  
  30.         public static string ConnectionString
  31.         {
  32.             get
  33.             {
  34.                 var connectionString = SqlHelper.connectionString;
  35.                 var upperCaseConnectionString = connectionString.ToUpperInvariant();
  36.  
  37.                 if (!upperCaseConnectionString.Contains(“CONNECTION TIMEOUT”) &&
  38.                     !upperCaseConnectionString.Contains(“CONNECTTIMEOUT”))
  39.                 {
  40.                     connectionString += “;Connection Timeout=60”;
  41.                 }
  42.  
  43.                 if (!upperCaseConnectionString.Contains(“ASYNCHRONOUS PROCESSING=TRUE”))
  44.                 {
  45.                     connectionString += “;Asynchronous Processing=true”;
  46.                 }
  47.  
  48.                 return connectionString;
  49.             }
  50.         }
  51.  
  52.         #endregion Properties
  53.  
  54.         public static async Task<DataTable> OpenQueryAsync(string query)
  55.         {
  56.             return await OpenQueryAsync(query, new Dictionary<string, object>());
  57.         }
  58.  
  59.         public static async Task<DataTable> OpenQueryAsync(string query, Dictionary<string, object> parameters)
  60.         {
  61.             return await InternalOpenQuery(query, parameters, CommandType.Text, CancellationToken.None);
  62.         }
  63.  
  64.         public static async Task<DataTable> OpenStoredProcedureAsync(string query)
  65.         {
  66.             return await OpenStoredProcedureAsync(query, new Dictionary<string, object>());
  67.         }
  68.  
  69.         public static async Task<DataTable> OpenStoredProcedureAsync(string query, Dictionary<string, object> parameters)
  70.         {
  71.             return await InternalOpenQuery(query, parameters, CommandType.StoredProcedure, CancellationToken.None);
  72.         }
  73.  
  74.         public static async Task<DataTable> OpenStoredProcedureAsync(string query, Dictionary<string, object> parameters, CancellationToken cancellationToken)
  75.         {
  76.             return await InternalOpenQuery(query, parameters, CommandType.StoredProcedure, cancellationToken);
  77.         }
  78.  
  79.         /// <summary>Opens a SQL text query or stored procedure and asynchronously reads it into a table, which is then returned.</summary>
  80.         /// <remarks>There are several overloads, allowing for either a stored procedure or text query, with or without parameters, and
  81.         /// with or without cancellation.</remarks>
  82.         private static async Task<DataTable> InternalOpenQuery(string query, Dictionary<string, object> parameters, CommandType commandType, CancellationToken cancellationToken)
  83.         {
  84.             using (var connection = new SqlConnection(ConnectionString))
  85.             {
  86.                 using (var adapter = new SqlDataAdapter(query, connection))
  87.                 {
  88.                     var table = new DataTable();
  89.                     adapter.SelectCommand.CommandType = commandType;
  90.                     adapter.SelectCommand.CommandTimeout = CommandTimeout;
  91.  
  92.                     foreach (var kvp in parameters)
  93.                     {
  94.                         adapter.SelectCommand.Parameters.AddWithValue(kvp.Key, kvp.Value);
  95.                     }
  96.  
  97.                     if (connection.State != ConnectionState.Open)
  98.                         connection.Open();
  99.  
  100.                     using (var reader = await adapter.SelectCommand.ExecuteReaderAsync(cancellationToken))
  101.                     {
  102.                         using (var schemaTable = reader.GetSchemaTable())
  103.                         {
  104.                             foreach (DataRow row in schemaTable.Rows)
  105.                             {
  106.                                 cancellationToken.ThrowIfCancellationRequested();
  107.                                 table.Columns.Add((string)row[“ColumnName”], Type.GetType(row[“DataType”].ToString()));
  108.                             }
  109.                         }
  110.  
  111.                         var columns = table.Columns.ToArray<DataColumn>();
  112.  
  113.                         while (reader.Read())
  114.                         {
  115.                             table.Rows.Add(columns.Select(c => reader[c.Ordinal]).ToArray());
  116.                         }
  117.                     }
  118.                     return table;
  119.                 }
  120.             }
  121.         }
  122.  
  123.         public static async Task<int> ExecuteStoredProcedureAsync(string query)
  124.         {
  125.             return await ExecuteStoredProcedureAsync(query, new Dictionary<string, object>());
  126.         }
  127.  
  128.         public static async Task<int> ExecuteStoredProcedureAsync(string query, Dictionary<string, object> parameters)
  129.         {
  130.             return await InternalExecuteQuery(query, parameters, CommandType.StoredProcedure, CancellationToken.None);
  131.         }
  132.  
  133.         public static async Task<int> ExecuteStoredProcedureAsync(string query, Dictionary<string, object> parameters, CancellationToken cancellationToken)
  134.         {
  135.             return await InternalExecuteQuery(query, parameters, CommandType.StoredProcedure, cancellationToken);
  136.         }
  137.  
  138.         /// <summary>Opens a SQL text query or stored procedure and asynchronously reads it into a table, which is then returned.</summary>
  139.         /// <remarks>There are several overloads, allowing for either a stored procedure or text query, with or without parameters, and
  140.         /// with or without cancellation.</remarks>
  141.         private static async Task<int> InternalExecuteQuery(string query, Dictionary<string, object> parameters, CommandType commandType, CancellationToken cancellationToken)
  142.         {
  143.             using (var connection = new SqlConnection(ConnectionString))
  144.             {
  145.                 using (var adapter = new SqlDataAdapter(query, connection))
  146.                 {
  147.                     var table = new DataTable();
  148.                     adapter.SelectCommand.CommandType = commandType;
  149.                     adapter.SelectCommand.CommandTimeout = CommandTimeout;
  150.  
  151.                     foreach (var kvp in parameters)
  152.                     {
  153.                         adapter.SelectCommand.Parameters.AddWithValue(kvp.Key, kvp.Value);
  154.                     }
  155.  
  156.                     if (connection.State != ConnectionState.Open)
  157.                         connection.Open();
  158.  
  159.                     return await adapter.SelectCommand.ExecuteNonQueryAsync();
  160.                 }
  161.             }
  162.         }
  163.  
  164.         /// <summary>The UpdateSqlAsync overloads are not used in this solution.</summary>
  165.         public static async Task<int> UpdateSqlAsync(string query)
  166.         {
  167.             return await UpdateSqlAsync(query, CancellationToken.None);
  168.         }
  169.  
  170.         /// <summary>The UpdateSqlAsync overloads are not used in this solution.</summary>
  171.         public static async Task<int> UpdateSqlAsync(string query, CancellationToken cancellationToken)
  172.         {
  173.             using (var connection = new SqlConnection(ConnectionString))
  174.             {
  175.                 using (var adapter = new SqlDataAdapter(“Not used”, connection))
  176.                 {
  177.                     adapter.UpdateCommand = new SqlCommand(query, connection);
  178.                     adapter.UpdateCommand.CommandType = System.Data.CommandType.Text;
  179.                     adapter.UpdateCommand.CommandTimeout = CommandTimeout;
  180.  
  181.                     if (connection.State != ConnectionState.Open)
  182.                         connection.Open();
  183.  
  184.                     return await adapter.UpdateCommand.ExecuteNonQueryAsync();
  185.                 }
  186.             }
  187.         }
  188.     }
  189.  
  190.     static class Extensions
  191.     {
  192.         /// <summary>Converts a non-generic collection to an array.</summary>
  193.         public static T[] ToArray<T>(this ICollection collection)
  194.         {
  195.             var items = new T[collection.Count];
  196.             collection.CopyTo(items, 0);
  197.  
  198.             return items;
  199.         }
  200.     }
  201. }

I don’t recall if I’ve shared the “normal” version of these methods anywhere… if I did, the main difference between the normal code and this is, I added a bit to the connection string, and there is no SQLDataReader.ReadAsync method, so I call the synchronous Read method from here. Otherwise, these methods should prove useful to others…

Enjoy

Advertisements

About Jerome

I am a senior C# developer in Johannesburg, South Africa. I am also a recovering addict, who spent nearly eight years using methamphetamine. I write on my recovery blog about my lessons learned and sometimes give advice to others who have made similar mistakes, often from my viewpoint as an atheist, and I also write some C# programming articles on my programming blog.
This entry was posted in Programming and tagged , , , . Bookmark the permalink.

2 Responses to Using async code (with await) in .Net 4.0

  1. ericardezp74 says:

    Excellent post,
    Could you share a project where the class is implemented?
    Thanks

    Like

    • Jerome says:

      I will have a look at home and see if I still have the code… I don’t have it at work since I changed jobs since then and am working in a company that uses VS 2012.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s