Click here to Skip to main content
12,510,403 members (48,112 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

5.1K views
Posted

Experiments with ADO.Net, Extension Methods and Anonymous Methods

, 21 Jul 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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:     /// <span class="code-SummaryComment"><summary>
</span>

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Martin Jarvis
Software Developer (Senior) Freestyle Interactive Ltd
United Kingdom United Kingdom
I'm a lead developer for Freestyle Interactive Ltd where we create many wonderful websites built on Microsofts ASP.Net and Ektron CMS.

I've been developing .Net applications (both Windows and Web) since 2002.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160929.1 | Last Updated 21 Jul 2011
Article Copyright 2011 by Martin Jarvis
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid