use of extension methods in data access code in C#

Already there are 2 articles posted on the blog about extension methods in C#. I'm writing one more on the same subject because extension methods can be used to reduce the number of lines of code to be written while improving the readability aspect of the code.

Earlier article on the extension methods

1. Extension methods introduction and overview
2. A practical example usage of extension methods in C#

Problem statement while writing repository code

Consider a scenario where you are writing repository code for saving customer details. The Customer Repository class usually will have Create, Read, Update and Delete methods(the typical CRUD operations).

public class CustomerRepository
{
  public void Create(Customer newCustomer)
  {
     //Other code...
     if (contact.Email != null)
       {
       sqlCommand.Parameters["@p_customer_email"].Value = 
               newCustomer.Email;
       }
    else
       {
      sqlCommand.Parameters["@p_customer_email"].Value =
                 DBNull.Value;
      }
   }
}

When new customer email is NULL and if you don't assign the Database specific NULL value(DBNull.Value), you are bound to get an EXCEPTION saying, missing parameter value or parameter value not provided for @p_customer_email while executing the stored procedures.(You will see this error only when in a stored procedure you have indicating @p_customer_email parameter as mandatory.)

To avoid such errors, you will end up in writing above NULL check for parameter value and assign DBNull.Value to SQL parameter when actual value is NULL.

But, when the number of such nullable parameter increases, the lines of code you have written also increases resulting in too much of duplicates lines of code. Now let's see, how this code duplication can be removed by using Extension methods.

Solution using extension methods

1. Create a static helper class and add a extension method like below:
   namespace DBExtensions
   {
   public static class DBExtensions
    {
     public static object GetDatabaseValue(this string value)
       {
         if (value != null)
                   return value;
   
               return DBNull.Value;
       }
    }
   }
2. Now in the customer repository class import the namespace in which DBExtensions class is defined
   using DBExtensions;
3. Let's use the extension method added on the string class
   public class CustomerRepository
   {
     public void Create(Customer newCustomer)
     {
        //Other code...
        sqlCommand.Parameters["@p_customer_email"].Value =
        newCustomer.Email.GetDatabaseValue();
      }
   }

Advantage of extension methods

By using extension methods, the number of lines of code is reduced from 4 lines to just 1 line of code. Duplicated lines of code is reduced (Now code is inline with DRY principle). Also with the extension methods, code readability is improved.