Fun With Extension Methods - Extending IDataRecord

For some reason, the IDataRecord interface and classes that implement it (DbDataReader, SqlDataReader and so on) only have Get* methods that accept the zero-based column index of a column in the result set. They don't allow you to get data by specifying a column name. As an example, consider the private FillDataRecord method to fill an e-mail address, as discussed in my article series about N-Layer development. 

private static EmailAddress FillDataRecord(IDataRecord myDataRecord)
{
  EmailAddress myEmailAddress = new EmailAddress();
  myEmailAddress.Id = 
          myDataRecord.GetInt32(myDataRecord.GetOrdinal("Id"));
  myEmailAddress.Email = 
          myDataRecord.GetString(myDataRecord.GetOrdinal("Email"));
  // More fields here
  return myEmailAddress;
}

The GetInt32 and GetString methods only have a single overload: one that accepts the zero-based index of the column. To satisfy these method signatures and make your code more readable at the same time, you can use GetOrdinal as shown in the previous example. Based on the column's name, GetOrdinal returns the column index. So, given the fact that the Id column is the first in the result set and Email the second, the previous piece of code equates to this: 

myEmailAddress.Id = myDataRecord.GetInt32(0);
myEmailAddress.Email = myDataRecord.GetString(1);

Clearly, this is much more difficult to read and maintain than the previous example as you need to know the column indices and you should take great care not to mess with the column order in your select statements.

With a few extension methods, you can have the short syntax of the latter example, but still have readable code as the first example.

The trick for this is to implement a number of extension methods for IDataRecord. This interface is implemented by a number of data aware classes, like the DbDataReader, the SqlDataReader, the OleDbReader and so on. These readers help you copy data out of the current record from the database into your own variables.

Each extension method accepts an instance that implements the IDataRecord interface and a column name. Internally, it calls the GetOrdinal method to "translate" the column name into its zero-based index, and then calls the existing Get* method on the IDataRecord that is being extended. Below is an example of the GetBoolean method that returns a bool from the database. The download at the end of the article contains implementations and comments for all the relevant Get* methods of the IDataRecord interface, allowing you to get at other data than booleans alone as well. 

namespace Spaanjaars.Toolkit.Extensions
{
  /// <summary>
  /// Provides a set of extension methods for the IDataRecord class adding 
  /// support for calling the Get* methods with a column name as well.
  /// </summary>
  public static class IDataRecordExtensions
  {
    /// <summary>
    /// Gets the value of the specified column as a Boolean.
    /// </summary>
    /// <param name="myIDataRecord">The IDataRecord to extend.</param>
    /// <param name="name">The name of the column in the result set.</param>
    /// <returns>The value of the specified column.</returns>
    public static bool GetBoolean(this IDataRecord myIDataRecord, string name)
    {
      return myIDataRecord.GetBoolean(myIDataRecord.GetOrdinal(name));
    }
  }
}

If you import the Spaanjaars.Toolkit.Extensions namespace into your code with a using (C#) or Imports (VB) statement, you can now use the following code to get the exact same behavior: 

private static EmailAddress FillDataRecord(IDataRecord myDataRecord)
{
  EmailAddress myEmailAddress = new EmailAddress();
  myEmailAddress.Id = myDataRecord.GetInt32("Id");
  myEmailAddress.Email = myDataRecord.GetString("Email");
  // More fields here
  return myEmailAddress;
}

A lot cleaner to read, and much easier to maintain. And, as you might expect, IntelliSense and help just work as advertised:

IntelliSense popping up for the new extension method

Download Files


Where to Next?

Wonder where to go next? You can post a comment on this article.

Doc ID 441
Full URL https://imar.spaanjaars.com/441/fun-with-extension-methods-extending-idatarecord
Short cut https://imar.spaanjaars.com/441/
Written by Imar Spaanjaars
Date Posted 03/17/2008 18:39
Listened to when writing Silver Rocket by Sonic Youth (Track 2 from the album: Daydream Nation)

Comments

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the Doc ID of the document.

(Plain text only; no HTML or code that looks like HTML or XML. In other words, don't use < and >. Also no links allowed.