Data Management

Applied reflection: Write 80 percent less access code when interacting with a database

With databases behind virtually every application, quite a bit of time is spent by application developers writing code to interact with databases. Zach Smith explains how to combine the Microsoft Data Access Block in .NET with custom reflection code to write 80 percent less data access code.

Database interaction is old hat to enterprise application developers. If you are a .NET developer, chances are you have used the SqlParameter, SqlCommand, and SqlConnection classes. There is also a high probability that you have noticed how monotonous and time consuming it is to use these classes to implement data access. Microsoft solved some of this by introducing their Data Access Block, which greatly simplifies writing database interaction code. However, there are still improvements to be made.

One area for improvement is the way parameters are added to a SqlCommand. Even when using the Data Access Block, you still have to add each SqlParameter individually — there isn't a way to automatically pull values out of a class and have them sent in as parameters. This is where reflection comes into play.

By using a function in the Data Access Block in combination with a custom reflection class, we can automatically populate the parameters of a stored procedure with values from an object. This type of solution enables you to greatly decrease the number of lines of code required for database interaction.

The Data Access Block

The Data Access Block is a free set of methods and classes that Microsoft provides to .NET developers to ease the task of writing database interaction code. Many best practices are implemented in this utility, and if you haven't been using it you might want to give it a look. To find more information about it and download the Data Access Block.

The functionality that we are interested in using from the Data Access Block resides in the SqlParameterDiscovery class. This class contains several static functions that are used to automatically discover the parameters of a stored procedure. The function we will be using is GetSpParameterSet, which returns an array of SqlParameter objects that match the parameters required by the stored procedure. The following is an example call to the GetSpParameterSet:

C#:

SqlParameter[] parameters =
SqlParameterDiscovery.GetSpParameterSet(connectionString, procedureName);

That call will populate the "parameters" variable with a SqlParameter object for each parameter that the procedure ("procedureName") accepts. After we have the array of SqlParameter objects we are able to loop through them and enumerate the names, types, and values of each parameter.

Combining the Data Access Block with reflection

Once we have retrieved the parameters via the GetSpParameterSet method, we are able to use reflection to extract data from an object's properties and set the value of each parameter in the SqlParameter array. To do this we use the Type.GetProperties() method that I detailed in a previous article.

Assume that we have a Customer object with the following definition (Listing A):

Listing A

C#:

    public class Customer
    {
        private int _customerID;
        private string _firstName;
        private string _lastName;
        private int _orderCount;

        public Customer(int customerID, string firstName,
                string lastName, int orderCount)
        {
            this.CustomerID = customerID;
            this.FirstName = firstName;
            this.LastName = lastName;
            this.OrderCount = orderCount;
        }

        public int CustomerID
        {
            get { return _customerID; }
            set { _customerID = value; }
        }

        public string FirstName
        {
            get { return _firstName; }
            set { _firstName = value; }
        }

        public string LastName
        {
            get { return _lastName; }
            set { _lastName = value; }
        }

        public int OrderCount
        {
            get { return _orderCount; }
            set { _orderCount = value; }
        }
    }

This is a standard C# class with no special attributes or inheritance applied. We also have the following stored procedure (Listing B) which is meant to save a new customer to the database:

Listing B

SQL:

CREATEPROCEDURE prSaveCustomer(      @CustomerID int=null out,      @FirstName varchar(100),      @LastName varchar(100),      @OrderCount int)ASINSERTINTO Customer             (                   FirstName,                   LastName,                   OrderCount             )VALUES(                   @FirstName,                   @LastName,                   @OrderCount             ) SET @CustomerID =SCOPE_IDENTITY()

To execute this stored procedure using conventional programming techniques we would have to complete the following steps:

  1. Create a SqlConnection object
  2. Create a SqlCommand object for the procedure
  3. Add a SqlParameter object for the @CustomerID parameter
  4. Add a SqlParameter object for the @FirstName parameter
  5. Add a SqlParameter object for the @LastName parameter
  6. Add a SqlParameter object for the @OrderCount parameter
  7. Execute the SqlCommand object

The code for this implementation would look similar to Listing C:

Listing C

C#:

privatevoid ManualSaveCustomer(Customer customer)
 {
SqlConnection connection = newSqlConnection(connectionString);

     connection.Open();

     SqlCommand command = newSqlCommand("prSaveCustomer", connection);
     command.CommandType = CommandType.StoredProcedure;
     command.Parameters.Add(newSqlParameter("CustomerID", customer.CustomerID));
     command.Parameters.Add(newSqlParameter("FirstName", customer.FirstName));
     command.Parameters.Add(newSqlParameter("LastName", customer.LastName));
     command.Parameters.Add(newSqlParameter("OrderCount", customer.OrderCount));

     command.ExecuteNonQuery();

     connection.Close();
 }

This is obviously a time consuming task that leaves room for improvement. To make this task easier we are going to implement a custom reflection routine that uses the output of the GetSpParameterSet method of the Data Access Block. Listing D illustrates how to save a Customer object using the reflection solution:

Listing D

C#:

publicvoid SaveCustomer(Customer customer)
 {
    //Call our custom method to get the parameters for the database call.
    SqlParameter[] parameters = BindParameters(customer, "prSaveCustomer");

    //Call the ExecuteNonQuery function in the Data Access Block to execute
    // our database call.
    SqlHelper.ExecuteNonQuery(connectionString, "prSaveCustomer", parameters);
 }

You can see from the example above that using reflection in combination with the Data Access Block significantly reduces the lines of code required to execute the stored procedure. We went from ten (10) lines to two (2) lines of code. This method could actually be compressed to one (1) line of code, but for readibility I have left it at two (2) lines.

The most interesting part of the example above is the BindParameters method. This is the method that combines the power of reflection with the simplicity of the Data Access Block. The code shown in Listing E below is the implementation for the BindParameters method:

Listing E

C#:

publicSqlParameter[] BindParameters(object inputObject, string procedureName)
{
      //Get the type of the inputObject (used later in the function)
      Type inputType = inputObject.GetType();

      //Get the parameters for the procedure using the Data Access Block
      SqlParameter[] parameters =
SqlParameterDiscovery.GetSpParameterSet(connectionString,
procedureName);

       //Loop through the parameters and find all parameters of the procedure
// that match properties of our inputObject.
      foreach(SqlParameter parameter in parameters)
      {
//Get the property. The SqlParameter.ParameterName will include the "@"
// sign so we must remove it. Notice that the parameters of the stored
// procedure must exactly match the property values. It is possible to
// map properties of one name to parameters of another name, and that
// will likely be covered in a future article.
                  PropertyInfo property =
inputType.GetProperty(parameter.ParameterName.Replace("@", ""));

//If the property doesn't exist, we will have a null property reference,
// so we have to check for that here before setting the parameter's value
// from the property value.
             if(property != null)
             parameter.Value = property.GetValue(inputObject, null);
      }

    return parameters;
}

The code shown above is included (along with all the other code in this article) in the Visual Studio example project that is included with the download zip file.

Generic access layer

While the solution detailed in this article is certainly powerful, it is really only half of what it needs to be. To be totally efficient, you need to combine this type of functionality with the solution detailed in my previous article titled Applied Reflection: Extracting Business Objects from Data Structures. When combined, the solutions detailed in each article will allow you to create a generic data access layer that can be used transparently throughout your projects.

Editor's Picks

Free Newsletters, In your Inbox