Enveloped abstractions

Ever find yourself writing code like this?

// look up connection string
// open sql connection
// execute command
// DO SOMETHING WITH RESULT SET
// close data reader
// close command
// close connection

From time to time I see patterns like this, where there's common code that envelopes something specific that I want to do. I'd like to factor out the enveloping code that is the same (everything except "DO SOMETHING WITH RESULT SET").

One way to approach this using traditional OO languages would be to create a class that represented the operation, and have that class call a virtual function that you could override in a derived class. Or, use a function pointer or delegate. In either case, you'd end up splitting off the "DO SOMETHING" part into some other function, which sometimes obscured the block of code you were writing.

Now that we have anonymous delegates in C# 2.0, it becomes very natural to abstract this enveloping code:

DatabaseHelper.ExecuteSelect("select au_fname, au_lname from authors",
                             delegate(SqlDataReader r) {
    while (r.Read()) {
      Console.WriteLine("{0} {1}", r.GetString(0), r.GetString(1));
    }
  });

Using an anonymous delegate like this allows me to write code that almost looks like it's extended C# by adding an “ExecuteSelect“ statement (kind of like the using statement). Here's some simple code that shows how you might accomplish this. Add whatever error handling makes sense for your application, but this demonstrates the idea.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public delegate void HandleResultSet(SqlDataReader r);

public static class DatabaseHelper {
    public static void ExecuteSelect(string selectStatement,
                                     HandleResultSet fcn) {
        string connStr =
          ConfigurationManager.ConnectionStrings["pubs"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(connStr))
        using (SqlCommand cmd = conn.CreateCommand()) {
            cmd.CommandText = selectStatement;
            conn.Open();
            using (SqlDataReader r = cmd.ExecuteReader()) {
                fcn(r);
            }
        }
    }
}

class Program {
    static void Main(string[] args) {

        Console.WriteLine("*** Authors ***");
        DatabaseHelper.ExecuteSelect("select au_fname, au_lname from authors",
          delegate(SqlDataReader r) {
            while (r.Read()) {
              Console.WriteLine("{0} {1}", r.GetString(0), r.GetString(1));
            }
          });

        Console.WriteLine("*** Titles ***");
        DatabaseHelper.ExecuteSelect("select title from titles",
          delegate(SqlDataReader r) {
            while (r.Read()) {
              Console.WriteLine(r.GetString(0));
            }
          });
    }
}