Thursday 14 July 2011

Experiments with ADO.Net, Extension Methods and Anonymous Methods

As much as at pains me to admit it, an ORM Framework is not the answer to all data access problems.  Sometimes the quickest, easiest and most maintainable approach is back-to-basics flat ADO.Net.  For those developers whose (development) life began with Entity Framework, NHibernate or SubSonic.  This is a fairly typical example of an ADO.Net method (in a well structured code base):

   1: private static IEnumerable<ContentAwaitingApproval> GetPendingApprovals(long approvalGroupId)
   2: {
   3:     List<ContentAwaitingApproval> approvals = new List<ContentAwaitingApproval>();
   4:     using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[Properties.Settings.Default.ConnectionStringName].ConnectionString))
   5:     {
   6:         using (SqlCommand cmd = conn.CreateCommand())
   7:         {
   8:             cmd.CommandText = "SELECT ContentId, LanguageId, Status FROM Approvals WHER ApprovalGroupID=@groupId";
   9:             cmd.Parameters.AddWithValue("groupId", approvalGroupId);
  10:             cmd.Connection.Open();
  11:             using (SqlDataReader dr = cmd.ExecuteReader())
  12:             {
  13:                 while (dr.Read())
  14:                 {
  15:                     approvals.Add(new ContentAwaitingApproval
  16:                     {
  17:                         ContentId = dr.GetInt64(0)
  18:                         ,
  19:                         LanguageId = dr.GetInt32(1)
  20:                         ,
  21:                         Status = dr.GetString(2)
  22:                     });
  23:                 }
  24:             }
  25:         }
  26:     }
  27:     return approvals;
  28: }

What’s occuring:

  1. Create a list to contain the results
  2. Create a new connection object (using a connection string defined in the configuration file)
  3. Create a SQL Command object
  4. Set the parameterised query text (if this is a stored procedure then you need to set the CommandType to StoredProcedure)
  5. Set any parameters the query requires
  6. Open the connection to the database
  7. Execute the query
  8. Read through all the results record by record
  9. For each record add a new entry to the results list
  10. Close the reader,command and connection and dispose objects
  11. Return the results list

So, out of 11 steps only 4 actually related to the functionality of the method.  The other 7 are all boiler plate, repeat each time, steps that are required for ADO.Net.  If you want to add more detailed exception handling then that’s even more boiler plate code messing up your coding zen.  When you look at it like that, it’s easy to start reaching from the ORM toolbox.

But wait, ORM isn’t the only thing that’s happened over the last 10 years!  We’ve now got refactoring tools, extension methods and anonymous methods!  What can these modern day miracles do for us?

Well, for starters we can tidy up that SqlConnection initializer to get something a bit cleaner and maybe some configuration validation code in place:

   1: private static SqlConnection ToConnection(this string connectionStringName)
   2: {
   3:     if (connectionStringName.IsNullOrWhiteSpace()) throw new ArgumentException("Connection String Name cannot be empty", "connectionStringName");
   4:     ConnectionStringSettings cns = ConfigurationManager.ConnectionStrings[connectionStringName];
   5:     if (cns == null) throw new ArgumentException("Connection String Name '{0}' cannot be found".ToFormattedString(connectionStringName), "connectionStringName");
   6:     return new SqlConnection(cns.ConnectionString);
   7: }

So replace:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[Properties.Settings.Default.ConnectionStringName].ConnectionString))

With

using (SqlConnection conn = Properties.Settings.Default.ConnectionStringName.ToConnection())

Next, that parameter mapping code can be easily refactored away into a reusable block and add some additional intelligence too:

   1: private static void AddParametersToCommand(IDictionary<string, object> parameters, SqlCommand cmd)
   2: {
   3:     if (parameters == null) return;
   4:     foreach (string key in parameters.Keys)
   5:     {
   6:         if (parameters[key] == null)
   7:         {
   8:             cmd.Parameters.AddWithValue(key, DBNull.Value);
   9:         }
  10:         else if (parameters[key].GetType().IsPrimitive)
  11:         {
  12:             cmd.Parameters.AddWithValue(key, parameters[key]);
  13:         }
  14:         else
  15:         {
  16:             cmd.Parameters.AddWithValue(key, "{0}".ToFormattedString(parameters[key]));
  17:         }
  18:  
  19:     }
  20: }

Finally, the per record processing can be made into delegate method, allowing you the opportunity to add additional exception information to the process:

   1: Func<SqlDataReader, bool> recordAction = (dr)=>{return true;};
   2:  
   3: using (SqlDataReader dr = cmd.ExecuteReader())
   4: {
   5:    int i = 0;
   6:    try
   7:    {
   8:        while (dr.Read() && recordAction(dr)) i++;
   9:    }
  10:    catch (Exception ex)
  11:    {
  12:        ex.Data["Record Ordinal"] = i;
  13:        throw;
  14:    }
  15: }

Combining all this can give us a set of reusable ADO.Net extension methods, which allow us to separate the business concern from the ADO.Net concerns, so the example code a the top of the page can become:

   1: private static IEnumerable<ContentAwaitingApproval> GetPendingApprovals(long approvalGroupId)
   2: {
   3:     List<ContentAwaitingApproval> approvals = new List<ContentAwaitingApproval>();
   4:     "SELECT ContentId, LanguageId, Status FROM Approvals WHER ApprovalGroupID=@groupId"
   5:         .ProcessRecords(
   6:             Properties.Settings.Default.ConnectionStringName
   7:             , new Dictionary<string, object> { { "groupId", approvalGroupId } }
   8:             , (dr) =>
   9:                 {
  10:                     approvals.Add(new ContentAwaitingApproval
  11:                     {
  12:                         ContentId = dr.GetInt64(0)
  13:                         ,
  14:                         LanguageId = dr.GetInt32(1)
  15:                         ,
  16:                         Status = dr.GetString(2)
  17:                     });
  18:                     return true;
  19:                 }
  20:             );
  21:     return approvals;
  22: }

Every single line (all 3 if the readability whitespace is removed) are focused on the business side of the query with minimal ADO.Net specific requirements (if you count the connection string name as a ADO.Net specific).

The full (experimental) extension method class is:

   1: using System;
   2: using System.Configuration;
   3: using System.Data.SqlClient;
   4: using System.Linq;
   5: using System.Collections.Generic;
   6: using MartinOnDotNet.TypeConversion;
   7:  
   8: namespace MartinOnDotNet.Data.SqlClient
   9: {
  10:     /// <summary>
  11:     /// Handy methods for handling SQL
  12:     /// </summary>
  13:     public static class SqlClientExtensions
  14:     {
  15:         private static SqlConnection ToConnection(this string connectionStringName)
  16:         {
  17:             if (connectionStringName.IsNullOrWhiteSpace()) throw new ArgumentException("Connection String Name cannot be empty", "connectionStringName");
  18:             ConnectionStringSettings cns = ConfigurationManager.ConnectionStrings[connectionStringName];
  19:             if (cns == null) throw new ArgumentException("Connection String Name '{0}' cannot be found".ToFormattedString(connectionStringName), "connectionStringName");
  20:             return new SqlConnection(cns.ConnectionString);
  21:         }
  22:  
  23:         public static void Execute(this string commandText, string connectionStringName)
  24:         {
  25:             commandText.Execute(connectionStringName, null);
  26:         }
  27:  
  28:         public static void Execute(this string commandText, string connectionStringName, IDictionary<string, object> parameters)
  29:         {
  30:             PerformSqlAction<bool>(commandText, connectionStringName, parameters, (cmd) =>
  31:             {
  32:                 cmd.ExecuteNonQuery();
  33:                 return true;
  34:             });
  35:         }
  36:  
  37:         public static T ExecuteScalar<T>(this string commandText, string connectionStringName)
  38:         {
  39:             return commandText.ExecuteScalar<T>(connectionStringName, null);
  40:         }
  41:  
  42:         public static T ExecuteScalar<T>(this string commandText, string connectionStringName, IDictionary<string, object> parameters)
  43:         {
  44:             return PerformSqlAction<T>(commandText, connectionStringName, parameters, (cmd) =>
  45:             {
  46:                 object value = cmd.ExecuteScalar();
  47:                 if (value == DBNull.Value) return default(T);
  48:                 return (T)value;
  49:             });
  50:         }
  51:  
  52:         public static void ProcessRecords(this string commandText, string connectionStringName, Func<SqlDataReader, bool> recordAction)
  53:         {
  54:             commandText.ProcessRecords(connectionStringName, recordAction);
  55:         }
  56:  
  57:         public static void ProcessRecords(this string commandText, string connectionStringName, IDictionary<string, object> parameters, Func<SqlDataReader, bool> recordAction)
  58:         {
  59:             PerformSqlAction<bool>(commandText, connectionStringName, parameters, (cmd) =>
  60:            {
  61:                using (SqlDataReader dr = cmd.ExecuteReader())
  62:                {
  63:                    int i = 0;
  64:                    try
  65:                    {
  66:                        while (dr.Read() && recordAction(dr)) i++;
  67:                    }
  68:                    catch (Exception ex)
  69:                    {
  70:                        ex.Data["Record Ordinal"] = i;
  71:                        throw;
  72:                    }
  73:                }
  74:                return true;
  75:            });
  76:         }
  77:  
  78:         private static void AddParametersToCommand(IDictionary<string, object> parameters, SqlCommand cmd)
  79:         {
  80:             if (parameters != null)
  81:             {
  82:                 foreach (string key in parameters.Keys)
  83:                 {
  84:                     if (parameters[key] == null)
  85:                     {
  86:                         cmd.Parameters.AddWithValue(key, DBNull.Value);
  87:                     }
  88:                     else if (parameters[key].GetType().IsPrimitive)
  89:                     {
  90:                         cmd.Parameters.AddWithValue(key, parameters[key]);
  91:                     }
  92:                     else
  93:                     {
  94:                         cmd.Parameters.AddWithValue(key, "{0}".ToFormattedString(parameters[key]));
  95:                     }
  96:  
  97:                 }
  98:             }
  99:         }
 100:  
 101:         public static bool HasResults(this string commandText, string connectionStringName)
 102:         {
 103:             return commandText.HasResults(connectionStringName, null);
 104:         }
 105:  
 106:         public static bool HasResults(this string commandText, string connectionStringName, IDictionary<string, object> parameters)
 107:         {
 108:             return PerformSqlAction<bool>(commandText, connectionStringName, parameters, (cmd) =>
 109:             {
 110:                 using (SqlDataReader dr = cmd.ExecuteReader())
 111:                 {
 112:                     return dr.Read();
 113:                 }
 114:             });
 115:         }
 116:  
 117:         private static T PerformSqlAction<T>(string commandText, string connectionStringName, IDictionary<string, object> parameters, Func<SqlCommand, T> action)
 118:         {
 119:             if (commandText.IsNullOrWhiteSpace()) throw new ArgumentException("Command cannot be empty", "commandText");
 120:             if (action == null) throw new ArgumentNullException("action");
 121:             try
 122:             {
 123:                 using (SqlConnection conn = connectionStringName.ToConnection())
 124:                 using (SqlCommand cmd = conn.CreateCommand())
 125:                 {
 126:                     cmd.CommandText = commandText;
 127:                     AddParametersToCommand(parameters, cmd);
 128:                     conn.Open();
 129:                     return action(cmd);
 130:                 }
 131:             }
 132:             catch (Exception ex)
 133:             {
 134:                 ex.Data["Connection String"] = connectionStringName;
 135:                 ex.Data["Command"] = commandText;
 136:                 if (parameters != null)
 137:                 {
 138:                     ex.Data["Parameters"] = parameters.Keys.Select(k => "{0} = '{1}'".ToFormattedString(k, parameters[k])).ToDelimitedString("\n");
 139:                 }
 140:                 throw;
 141:             }
 142:         }
 143:     }
 144: }
These provide DataReader, Scalar and Execute methods that all leverage the same generic ADO.Net method PerformSqlAction using best practise boiler plate code with detailed exception logging backed in.

2 comments:

  1. This is a fairly typical example of an ADO.Net method.Thanks for sharing it.


    .net Programmer

    ReplyDelete
  2. Great article! If you like this sort of thing check out Dapper.NET a micro ORM which I think you might like! I wrote a blog post here http://ilovedevelopment.blogspot.co.uk/2012/10/adonet-dappernet-using-c-aspnet.html . Lovely little generics based micro-ORM written by some MVP's - developed and used to handle data functions on stackoverflow.com . Its on github here https://github.com/SamSaffron/dapper-dot-net and Nuget here http://nuget.org/packages/Dapper .

    ReplyDelete

Got something to say? Let it out then!
Comments are moderated, so it may take a while to for them to be displayed here!