Extension methods help making many routine codes much cleaner and simpler. In this post I’ll show how I use extension methods with classes that implement IDataReader such as SqlDataReader. I’ll also throw in some examples how to convert fields to enums etc tips for usual needs.
While LINQ is now the coolest kid in the block, the oldskool data readers are still the most efficient way for most cases and every now and then I still use them.
When you loop through the data reader, you have to convert each column into right type. For example:
SqlDataReader reader;
reader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow);
reader.Read();
product.ProductID = Convert.ToInt32(reader["ProductID"]);
Converting data with a helper class
Typically I moved the conversion to some helper tools class to encapsulate and unify the conversion:
public static class Tools
{
public static int GetInt(IDataReader reader, string column)
{
if (reader[column] != DBNull.Value)
{
return Convert.ToInt32(reader[column]);
}
else
return 0;
}
With that helper method the code would look something like this:
product.ProductID = Tools.GetInt(reader, “ProductID”);
product.ProductName = Tools.GetString(reader, “ProductName”);
product.UnitPrice = Tools.GetDouble(reader, “UnitPrice”);
product.Discontinued = Tools.GetBool(reader, “Discontinued”);
This looks better and if I need to change the rules I can do it in one location. Still, there is a bit nicer way after the extension methods came with .NET 3.5.
Converting data with extension methods
I won’t go through the basic examples about what the extension methods are, as there already is plenty of those examples in the web. Without further ado, here are some of my extension methods for the IDataReader:
public static class DataReaderExtensions
{
public static int ToInt(this IDataReader reader, string column)
{
if (reader[column] != DBNull.Value)
{
return Convert.ToInt32(reader[column]);
}
else
return 0;
}
public static Guid ToGuid(this IDataReader reader, string column)
{
if (reader[column] != DBNull.Value)
{
return new Guid(reader[column].ToString());
}
else
return Guid.Empty;
}
public static DateTime ToDateTime(this IDataReader reader, string column)
{
if (reader[column] != DBNull.Value)
{
return Convert.ToDateTime(reader[column]);
}
else
return DateTime.MinValue;
}
//This converts an integer column to the given enum (T)
public static T ToEnum<T>(this IDataReader reader, string column)
{
if (!typeof(T).IsEnum)
{
throw new ArgumentException(typeof(T).ToString() + ” is not an Enum”);
}
return (T)Enum.ToObject(typeof(T), reader.ToInt(column));
}
I put only some examples to get the idea through, but with similar pattern you can easily add support to all the types you need.
After that I can convert the columns with less code:
//product.ProductID = Tools.GetInt(reader, “ProductID”);
//product.ProductName = Tools.GetString(reader, “ProductName”);
//product.UnitPrice = Tools.GetDouble(reader, “UnitPrice”);
//product.Discontinued = Tools.GetBool(reader, “Discontinued”);
product.ProductID = reader.ToInt(“ProductID”);
product.ProductName = reader.ToString(“ProductName”);
product.UnitPrice = reader.ToDouble(“UnitPrice”);
product.Discontinued = reader.ToBoolean(“Discontinued”);
The difference might not seem that big, but when typing that routinely line after line, you’ll notice the difference. It is also easier to read.
How about enums?
Earlier I showed the code of ToEnum<T> -extension method that converts an integer (usually primary/foreign key in a table) to an enum.
Let’s say I have Product -type and Category-enum like this:
public class Product
{
public int ProductID { get; set; }
public string ProductName { get; set; }
public double UnitPrice { get; set; }
public bool Discontinued { get; set; }
public Category Category { get; set; }
}
public enum Category
{
Beverage = 1,
Condiments = 2
}
I can convert the foreign key in the Products -table into Category -enum with the extension method, if the ID’s of the primary key match in the Categories -table:
product.Category = reader.ToEnum<Category>(“CategoryID”);
Think big – add the extension method to interface or base-type
Here is signature of one extension method:
public static int ToInt(this IDataReader reader, string column)
First comes the type you want to extend (IDataReader) and after that one extra parameter, if needed (column name in this case).
For the type parameter, I have chosen IDataReader. I used to have SqlDataReader, but then I realized that these extension methods would work with any data reader, as long as it implements the IDataReader. This made them more reusable, though I rarely use other than SqlDataReader.
Here I made sure that the extension methods work with native .NET readers that implement the IDataReader:
public static void Test()
{
int pid;
SqlDataReader sqlReader;
pid = sqlReader.ToInt(“ProductID”);
OleDbDataReader oleDBReader;
pid = oleDBReader.ToInt(“ProductID”);
OdbcDataReader odbcDataReader;
pid = odbcDataReader.ToInt(“ProductID”);
DbDataReader dbDataReader;
pid = dbDataReader.ToInt(“ProductID”);
OracleDataReader oracleReader;
pid = oracleReader.ToInt(“ProductID”);
}
Extension methods aren’t really that confusing
The blue arrows and the tooltip in the intellisense menu reveal that those are just extension methods, if you are afraid that somebody gets confused:
Conclusion
Extension methods help to make the .NET Framework more usable to your needs. Downside is that you’ll easily get dependent on them, but just add them to your common tools -class that you can attach to your own projects and they’ll tag along.
In projects related to work they may require some team-wide agreement, but the same applies to all the other helper classes, too.
March 24, 2008 at 03:32 |
[...] Using extension methods with a data reader Using extension methods for enum ToString Extension methods: Copy all files in a directory (recursive) ASP.Net MVC Framework leads you to extension method heaven [...]
June 12, 2008 at 13:50 |
The following is also possible.
public static T ToT(this IDataReader reader, string column)
{
return reader[column] != DBNull.Value ? (T)reader[column] : default(T);
}
June 12, 2008 at 13:53 |
Note the blog parser left the <T> after the ToT
ToT[T]
June 12, 2008 at 13:59 |
@Cathal:
Thanks for the comment.
Indeed your generics approach works well for straight-forward conversions.