Monday, November 28, 2011

C# extension methods for IDbCommand and IDataReader

The data access layers of my .NET applications still use the SqlCommand and SqlDataReader classes that have been part of the .NET framework since the beginning. Why? Because they are simple and fast. I recently delved into the possibilities of Fluent NHibernate, only to discover that creating the mappings between my database columns and object properties looked very much like what I have always been doing with getting results from a DataReader. Also some of the many (complex) stored procedures I had already written seemed rather difficult to translate to the NHibernate query syntax. I am sure someone in the ORM world may be able to convince me to use a ORM tool (the author of this article did not succeed), but until that time...

I stick with the good-old SqlCommand and SqlReader. Of course I, too, do not like to repeat the same data access code in every DAL in every project. Therefore I have written some extension methods for the IDbCommand and IDataReader classes.These methods perform the following tasks:
  • Execute an IDbCommand and get an entity or a list of entities from the IDataReader that is built.
  • Get integers, strings and other types from an IDataReader in a 'safe' way, i.e. after checking whether a reader value is NULL.
The extension methods are listed below.

/// <summary>
/// Executes the specified command and returns a list of entities from the reader that
/// is built.
/// This method should be used when the command is expected to return multiple records.
/// </summary>
/// <typeparam name="T">The type of the objects in the list.</typeparam>
/// <param name="command">The command to execute.</param>
/// <param name="getEntityMethod">A method that returns one entity from the
/// current reader value.</param>
/// <returns>List of entities.</returns>
public static IList<T> GetEntities<T>(this IDbCommand command, Func<IDataReader, T> getEntityMethod)
{
    List<T> list = new List<T>();
    using (IDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            T entity = getEntityMethod(reader);
            if (entity != null)
            {
                list.Add(entity);
            }
        }
    }
    return list;
}

/// <summary>
/// Executes the specified command and returns one entity from the reader that is built.
/// This method should be used when the command is expected to return a single record.
/// </summary>
/// <typeparam name="T">The type of the object to return.</typeparam>
/// <param name="command">The command to execute.</param>
/// <param name="getEntityMethod">A method that returns an entity from the
/// current reader value.</param>        
/// <returns>Entity.</returns>
public static T GetEntity<T>(this IDbCommand command, Func<IDataReader, T> getEntityMethod)
{
    using (IDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))
    {
        return reader.Read() ? getEntityMethod(reader) : default(T);
    }
}

/// <summary>
/// Gets an integer value at the specified index from the specified reader,
/// or 0 if the value is null.
/// </summary>
/// <param name="reader">Reader to get the value from.</param>
/// <param name="columnIndex">Column index of the value.</param>
/// <returns>Integer.</returns>
public static int SafeGetInt32(this IDataReader reader, int columnIndex)
{
    return !reader.IsDBNull(columnIndex) ? reader.GetInt32(columnIndex) : 0;
}

/// <summary>
/// Gets a string value at the specified index from the specified reader,
/// or null if the value is null.
/// </summary>
/// <param name="reader">Reader to get the value from.</param>
/// <param name="columnIndex">Column index of the value.</param>
/// <returns>String.</returns>
public static string SafeGetString(this IDataReader reader, int columnIndex)
{
    return !reader.IsDBNull(columnIndex) ? reader.GetString(columnIndex) : null;
}

/// <summary>
/// Gets a float value at the specified index from the specified reader,
/// or 0 if the value is null.
/// </summary>
/// <param name="reader">Reader to get the value from.</param>
/// <param name="columnIndex">Column index of the value.</param>
/// <returns>Float.</returns>
public static float SafeGetFloat(this IDataReader reader, int columnIndex)
{
    return !reader.IsDBNull(columnIndex) ? reader.GetFloat(columnIndex) : 0;
}

/// <summary>
/// Gets a boolean value at the specified index from the specified reader,
/// or false if the value is null.
/// </summary>
/// <param name="reader">Reader to get the value from.</param>
/// <param name="columnIndex">Column index of the value.</param>
/// <returns>Boolean.</returns>
public static bool SafeGetBool(this IDataReader reader, int columnIndex)
{
    return !reader.IsDBNull(columnIndex) ? reader.GetBoolean(columnIndex) : false;
}

/// <summary>
/// Gets a byte array at the specified index from the specified reader,
/// or null if the value is null.
/// </summary>
/// <param name="reader">Reader to get the value from.</param>
/// <param name="columnIndex">Column index of the value.</param>
/// <returns>Byte array.</returns>
public static byte[] SafeGetByteArray(this IDataReader reader, int columnIndex)
{
    return !reader.IsDBNull(columnIndex) ? (byte[])reader[columnIndex] : null;
}

/// <summary>
/// Gets a DateTime value at the specified index from the specified reader,
/// or null if the value is null.
/// </summary>
/// <param name="reader">Reader to get the value from.</param>
/// <param name="columnIndex">Column index of the value.</param>
/// <returns>DateTime.</returns>
public static DateTime? SafeGetDateTime(this IDataReader reader, int columnIndex)
{
    if (!reader.IsDBNull(columnIndex))
    {
        return reader.GetDateTime(columnIndex);
    }
    else
    {
        return null;
    }
}

The first two methods expect a 'getEntityMethod' parameter. What does such a method look like? Here is an example DAL method that retrieves a list of languages from a database:
public IList<Language> GetLanguages()
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand("SelectLanguages", connection);
        command.CommandType = CommandType.StoredProcedure;
        connection.Open();
        // Use the SqlCommand extension method GetEntities to retrieve a list of
        // Language objects. The GetLanguageFromReader method is a 'getEntityMethod'
        // example.
        return command.GetEntities(GetLanguageFromReader);
    }
}

protected Language GetLanguageFromReader(IDataReader reader)
{
    Language language = new Language();
    for (int n = 0; n < reader.FieldCount; n++)
    {
        switch (reader.GetName(n))
        {
            case "LanguageId":
                language.Id = reader.SafeGetInt32(n);
                break;
            case "Name":
                language.Name = reader.SafeGetString(n);
                break;
        }
    }
    return language;
}

No comments:

Post a Comment