Click here to Skip to main content
Click here to Skip to main content

Templating via Generics and delegates

, 1 Mar 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
Using the new mojo of .NET 2.0 Generics for a "Real World" database querying template.

What's this article all about?

It took me some time to understand the real power behind .NET 2.0 Generics. Until now, hearing the word "Generics" immediately gave me the association of strongly typed collections. Yes, this is a nice feature as it prevents quite expensive casting while moving on collections, but is it the only usage of Generics? Well, in most cases, yes, but I'll try to open your mind to a new set of abilities, with a few examples. This, I hope, will allow you to think about cleaner and more powerful solutions in the future.

Looks familiar?

Look at the following code:

try
{
   // cmd is from IDbCommand (SqlCommand, OracleCommand, OleDbCommend etc) type.
   // This is the command which we want to run against our database.

   using (IDbConnection conn = ProviderFactory.CreateConnection())
   {
      cmd.Connection = conn;

      conn.Open();

      // use the cmd object. 


   } //"using" will close the connection even in case of exception.

}
catch (Exception e)
{
   // 1. Trace ?
   // 2. Rollback transaction ?
   // 3. Throw a wrapper exception with some more information ?
}

I bet that this pattern exists in each of your applications ... a lot more than once! Think about it, if those lines were some sort of a template, it will enable you to trace the entire traffic (SQL queries) between your application and your database in one single place! It even gives you the ability to change the type of the exception it throws, or the information it adds, in just one single place! You got the idea...

To accomplish that, we need to create a template for executing any IDbCommand object as a Reader, NonQuery, Scalar - you name it.

Refactoring time - making a template from this pattern via Generics and delegates

Step 1 - Defining a delegate as a generic handler

public delegate T CommandHandler<T>(IDbCommand cmd);

Before I run along, just to make sure you're still with me:

  1. A delegate is simply a pointer to a method with the same signature as the delegate itself.
  2. T is a generic type which means the ReaderHandler<T> and CommandHanlder<T> will be able to return any given <T> type. We'll want to return int after parsing an IDbCommand object via the ExecuteNonQuery() method. But, we'll probably want to return some sort of collection after parsing IDbCommand via the ExecuteReader() method. Generics will enable us to do just that.

(If this is still not clear, just hang on, the following samples will make it clearer.)

Step 2 - The generic "command executer" template

/// <SUMMARY>
/// Simple command executer "design pattern".
/// </SUMMARY>
/// <TYPEPARAM name="T">The type to return</TYPEPARAM>
/// <PARAM name="cmd">The command</PARAM>
/// <PARAM name="handler">The handler which will receive
/// the open command and handle it (as required)</PARAM>
/// <RETURNS>A generic defined result, according to the handler choice</RETURNS>

public static T ExecuteCommand<T>(IDbCommand cmd, CommandHandler<T> handler) //*1
{
   try
   {
      using (IDbConnection conn = ProviderFactory.CreateConnection()) //*2
      {
         cmd.Connection = conn;

         // Trace the query & parameters.
         DatabaseTracer.WriteToTrace(TraceLevel.Verbose, cmd, 
                 "Data Access Layer - Query profiler"); //*3

         conn.Open();
         return handler(cmd); //*4

      } //"using" will close the connection even in case of exception.

   }
   catch (Exception e)
   {
      // Trace the exception into the same log.
      Tracer.WriteToTrace(TraceLevel.Error, e, "Data Access Layer - Exception"); //*5

      throw WrapException(e); //*6
   }
}

I owe you some explanations:

  1. Notice the generic type T - This will be necessary for returning different types depending on the programmer's choice.
  2. Create a connection - I'm using some factory I've built in order to return a strongly typed connection depending on the selected provider in the application configuration(*.config) file.
  3. Trace the command (CommandText, parameters etc.) - The DataBaseTracer class checks the "switch" I've declared in the .config file and traces the query only if it was requested. This will give me that ability to trace all the queries later on in the production environment (good for production debugging).
  4. Send the live command (the connection was opened) to the handler so it can use the command for its needs.
  5. Trace the exception, again, only if requested.
  6. Wrap the exception in DalException - I, as an architect, believe that the Data Access Layer should throw only DalException exceptions.

Step 3 - A first usage of the "command executer" template

Let's use the template in order to parse a command with the ExecuteReader() method. First, we'll declare a delegate for handling a live reader (returning a generic type, for better extendibility).

public delegate T ReaderHandler<T>(IDataReader reader);

Now, let's use the template:

/// <summary>
/// Execute the db command as reader and parse it via the given handler.
/// </summary>
/// <typeparam name="T">The type to return after parsing the reader.</typeparam>
/// <param name="cmd">The command to execute</param>
/// <param name="handler">The handler which will parse the reader</param>
/// <returns>A generic defined result, according to the handler choice</returns>

public static T ExecuteReader<T>(IDbCommand cmd, ReaderHandler<T> handler)
{
   return ExecuteCommand<T>(cmd,
      delegate(IDbCommand liveCommand) //*1

      {
     // This is the anonymous delegate handler.

     // REMINDER: The original template sends the live command as parameter.

         IDataReader r = liveCommand.ExecuteReader();
         return handler(r);
      });
}

This one is even harder to follow, but relax, it's not as bad as you might think.

  1. You can see that I'm using an anonymous delegate for CommandHandler<T>, so the delegate gets the live command object from the ExecuteCommand method and calls ExecuteReader() on it. Afterwards, it sends the reader to the ReaderHandler<T> handler (given as a parameter).

Step 4 - Real life example, using ExecuteReader<T> to parse a reader into a List<Person> and string

/// <SUMMARY>
/// Retrieve the persons according to the specified command.
/// </SUMMARY>
/// <RETURNS>Typed collection of person.</RETURNS>

public static List<Person> GetPersonsList()
{
   IDbCommand cmd = ProviderFactory.CreateCommand();
   cmd.CommandText = "SELECT Name,Age,Email FROM Persons";
   cmd.CommandType = CommandType.Text;

   return DalServices.ExecuteReader<List<Person>>(cmd,
          delegate(IDataReader r)
      {
         List<Person> persons = new List<Person>();
         
         while (r.Read())
         {
             // Create a Person object, fill it by the reader
             // and add it to the "persons" list.

            Person person = new Person(r["Name"].ToString(), 
                            Convert.ToInt32(r["Age"]), 
                            r["Email"].ToString());
            persons.Add(person);
         }
      
         return persons;
      });
}


/// <SUMMARY>
/// Retrieve the persons xml according to the specified command.
/// </SUMMARY>
/// <RETURNS>Xml representation of the persons.</RETURNS>

public static string GetPersonsXml()
{
   IDbCommand cmd = ProviderFactory.CreateCommand();
   cmd.CommandText = "SELECT Name,Age,Email FROM Persons";
   cmd.CommandType = CommandType.Text;

   return DalServices.ExecuteReader<string>(cmd,
      delegate(IDataReader r)
      {
    StringBuilder builder = new StringBuilder(500);
                
    builder.Append("<Persons>");
    while (r.Read())
    {
        // Create a Person object, fill it by the reader
        // and add it to the "persons" list.

        Person person = new Person(r["Name"].ToString(), 
                        Convert.ToInt32(r["Age"]), 
                        r["Email"].ToString());
        builder.Append(person.ToXml());
    }
    builder.Append("</Persons>");
                
    return builder.ToString();         
      });
}

The first method returns a strongly typed collection of Person objects, while the other method returns an XML representation of the results. This (strongly typed return values) is possible only by Generics.

Step 5 - Leveraging the command executer template

Now that we understand the template, let's wrap some more execution "modes". You can add to it later on, according to your needs.

/// <summary>
/// Execute the db command in "NonQuery mode".
/// </summary>
/// <param name="cmd">The command to parse</param>
/// <returns>Affected rows number</returns>

public static int ExecuteNonQuery(IDbCommand cmd)
{
   return ExecuteCommand<int>(cmd,
      delegate(IDbCommand liveCommand)
      {
         return liveCommand.ExecuteNonQuery();
      });
}

/// <summary>
/// Execute the db command in "Scalar mode".
/// </summary>
/// <typeparam name="T">The type to return after parsing the reader.</typeparam>
/// <param name="cmd">The command to execute</param>
/// <returns>A generic defined result, according to the handler choice</returns>

public static T ExecuteScalar<T>(IDbCommand cmd)
{
   return ExecuteCommand<T>(cmd,
      delegate(IDbCommand liveCommand)
      {
         return (T)liveCommand.ExecuteScalar();
      });
}

Conclusions

I hope I managed to show you another kind of usage for Generics and delegates through a real world refactoring I've made in my infrastructure. Prior to Generics, this had to be done via interfaces, which cost us performance for each casting we had to make.

Can you now think about further usages of Generics??

Updates

  • [01/03/2006] - I've fixed some of the method signatures and attached a demo project. I hope this will make the article a bit more approachable and easier to understand.

License

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

Share

About the Author

Oren Ellenbogen
Web Developer
Israel Israel
I'm working as a software developer for SQLink where I'm currently
positioned as a Team Leader in our R&D department.
I've started programming way back in my early adolescence via C++, Delphi and PHP and
later on continued to Microsoft .Net platform for the last 3 years.
 
You can find much more tech info and some further details about Oren Ellenbogen at his blog - http://www.lnbogen.com.

Comments and Discussions

 
GeneralSuper Clever Pinmemberleandroshan9-Sep-07 18:44 
Generalgood article Pinmembersstoyan9-Apr-07 13:05 
GeneralGenerics are completely useless PinmemberAlexis MICHEL1-Mar-06 12:05 
QuestionNice approach, still missing some real concrete examples PinmemberSalam Y. ELIAS1-Mar-06 3:38 
AnswerRe: Nice approach, still missing some real concrete examples PinmemberOren Ellenbogen1-Mar-06 9:53 
GeneralRe: Nice approach, still missing some real concrete examples PinmemberSalam Y. ELIAS1-Mar-06 10:27 
GeneralHelpful article - Add some working example Pinmemberegozi1320-Feb-06 22:03 
GeneralRe: Helpful article - Add some working example PinmemberOren Ellenbogen1-Mar-06 9:55 
GeneralRe: Helpful article - Add some working example Pinmemberegozi134-Mar-06 9:27 
GeneralGreat post! PinmemberDroren20-Feb-06 20:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141220.1 | Last Updated 1 Mar 2006
Article Copyright 2006 by Oren Ellenbogen
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid