Taking Advantage of Custom Attributes and Reflection to Eliminate A Tedious Task

Welcome to the next installment of the .NET Nuts & Bolts column. In this article we’ll talk about how you can use custom attributes and reflection to simplify the process of configuring objects. Those of you who’ve been reading this column may recognize that this column represents a “changing of the guard” as I take over Mickey Williams’ good work.

I am constantly seeking ways to improve my coding skills and practices in a never-ending effort to produce error free code. Despite what my programming ego likes to think, the more code generated the more likely there is to be a
defect. When writing code there is a tendency to use what is affectionately known as editor inheritance. Editor inheritance is a fancy description for cutting, pasting, and then modifying existing code.

For myself personally, this practice is more likely to lead to a defect than actually creating the code from scratch. It is too easy to miss something in the copied code that does not apply, behaves differently than expected, or may already contain an unknown defect. In addition, although I enjoy programming, I do not particularly enjoy producing the same code over and over again. It starts to feel like a mundane and tedious task, which can easily lead to mistakes. As soon as I find myself cutting, pasting, and modifying code from somewhere else I start wondering if there is a better way.

This brings me to the focus of this article. Accessing data from a database has to be one of the most repetitive and tedious tasks that programmers deal with on a daily basis. When architecting applications the data access layer is generally segmented from the business logic and user interface layer. Whether querying for data to process through business logic, or for display in a user interface, most of the code in the data access layer performs the same functionality varying only by the stored procedure or database tables used.  How
many times have you written code to fetch data from a database and you start by cutting and pasting some other code that has a similar Connection, Command, and Parameter objects to what you are going to use? Let’s explore a way to eliminate this tedious programming task once and for all.

What is Reflection?

Is a reflection that blurry face I see in the monitor during a late night coding session? Well, yes, but for the sake of this column we’ll stick to what Reflection means in the context of the .NET Framework and leave the project management discussion on how to avoid the late night session to another column. Reflection is used to retrieve the internal details of assemblies and classes at runtime. It is used to programmatically discover everything about a class from properties and methods to attributes and events. In addition, reflection can be used for creating dynamic objects and invoking any number of methods.

Sample Uses of Reflection

// List of properties that belong to the type 
PropertyInfo[] itemTypeProperties = typeof(string).GetProperties();

// List of methods that belong to the type
MethodInfo[] itemTypeMethods = typeof(string).GetMethods();

// List of events that belong to the type
EventInfo[] itemTypeEvents = typeof(string).GetEvents();

What are Custom Attributes?

Custom attributes are classes that are used to provide additional descriptive information about properties, methods, etc. used in your classes. The information from the attribute can be obtained programmatically using our friend
Reflection.

A custom attribute begins with the AttributeUsage attribute, which defines the way the your attribute class can be used. Since a custom attribute uses an attribute it provides one example of how attributes can be used just by creating a custom attribute. There are three parameters that can be applied to attributes. The AttributeTargets defines where the attribute can be used, for example, properties, methods, classes, etc. The Inherited parameter defines
whether or not classes that are derived from classes that use your attribute inherit your attribute too. The AllowMultiple parameter defines whether or not multiple instances of your parameter can exist on an item.

Sample Custom Attribute

Here is a simple example of a custom attribute created to map a property to a database field. The attribute consists of a FieldName that represents that contains the name of the database field.

/// <remarks>
/// Attribute used to match a Property to its corresponding 
/// database field.
/// Valid only on Properties and only one attribute per property.
/// <remarks>
[AttributeUsage(AttributeTargets.Property,

                Inherited = true,

                AllowMultiple = false)]

public class DatabaseFieldAttribute : Attribute
{
  // Internal FieldName value
  private string _FieldName = “”;
  /// <value>Get or set the name of the database field<value>
  public string FieldName
  {
    get { return this._FieldName; }
    set { this._FieldName = value; }
  }

  /// <summary>
  /// Class constructor
  /// </summary>
  /// <param name=”DBFieldName”>Database field name</param>
  public DatabaseFieldAttribute(string DBFieldName)
  {
    this.FieldName = DBFieldName;
  }
}

Combining Reflection and Custom Attributes to Dynamically Load Data

Now we’ll combine the use of reflection and our custom attribute to eliminate some of the tedious database programming. We need a database table that contains data. For simplicity, I’ll use the Products table in the Northwind database. The Northwind database is a sample database available from Microsoft for Access or SQL Server. I am using the version from SQL Server. The SQL script to create the table is listed below.

Table Script to Generate Products Table from Northwind Database

CREATE TABLE [dbo].[Products] (
  [ProductID] [int] IDENTITY (1, 1) NOT NULL,
  [ProductName] [nvarchar] (40) 
            COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [SupplierID] [int] NULL,
  [CategoryID] [int] NULL,
  [QuantityPerUnit] [nvarchar] (20) 
            COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [UnitPrice] [money] NULL,
  [UnitsInStock] [smallint] NULL,
  [UnitsOnOrder] [smallint] NULL,
  [ReorderLevel] [smallint] NULL,
  [Discontinued] [bit] NOT NULL 
) ON [PRIMARY]

Sample Product Class

Now we need a class the represents a row of data in the Products table. Notice how use our custom attribute on the properties to describe the database field that contains the data. Also notice how only the “DatabaseField” portion of the DatabaseFieldAttribute class name is used and the “Attribute” is left off.

/// <remarks>
/// Sample class representing a partial Product record from the
/// Northwind sample database.
/// Random fields were chosen to show different data types.
/// <remarks>
public class Product
{
  // Internal ProductId value
  private int _ProductId = 0;
  /// <value> Get or set the unique product identifier </value>
  [DatabaseField(“ProductID”)]
  public int ProductId
  {

    get { return this._ProductId; }
    set { this._ProductId = value; }
  }

   // Internal ProductName value
  private string _ProductName = “”;
  /// <value> Get or set the name of the product </value>
  [DatabaseField(“ProductName”)]
  public string ProductName
  {
    get { return this._ProductName; }
    set { this._ProductName = value; }
  } 

  // Internal UnitsInStock value
  private int _UnitsInStock = 0;
  /// <value> Get or set the number of units in stock </value>
  [DatabaseField(“UnitsInStock”)]
  public int UnitsInStock
  {
    get { return this._UnitsInStock; }
    set { this._UnitsInStock = 
             (value >= 0 ? value : this._UnitsInStock ); }
  }
 

  // Internal UnitPrice value
  private decimal _UnitPrice = 0.0m;
  ///<value> Get or set the price per unit </value>  
  [DatabaseField(“UnitPrice”)]
  public decimal UnitPrice
  {
    get { return this._UnitPrice; }
    set { this._UnitPrice = value; }
  } 

  // Internal IsDiscontinued value
  private bool _IsDiscontinued = false;
  /// <value>Get or set the indicator if the product has been
  /// discontinued </value>
  [DatabaseField(“Discontinued”)]
  public bool IsDiscontinued
  {
    get { return this._IsDiscontinued; }
    set { this._IsDiscontinued = value; }
  }
}

Now we have a class that represents data in our database and a custom attribute to map the properties to the appropriate database field. Our goal is to now perform a database query to return a specific database row and populate an instance of the Product class. Here is an outline of the steps to perform:

• Perform the query
• If there is data, create an instance of the Product class
• Get a list of the properties of the Product class
• For each property in the list, get the DatabaseField custom attribute
• Retrieve data from a column in the query results based on the FieldName property of the DatabaseField attribute.
• Assign the retrieved value to the property

DataReflector Class

Here is an class called DataReflector that performs the steps outlined above.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection; 

/// <summary>
/// Retrieve data from the database and populate the given object
/// with the data.
/// </summary>
/// <param name=”CmdText”>Command text to retrieve data</param>
/// <param name=”ItemType”>Object type to create</param>
public static object FillObject(string CmdText, Type ItemType)
{
  object fillObject = Activator.CreateInstance(ItemType);
  SqlConnection connection = new SqlConnection(
  “Server=localhost;Database=Northwind;Integrated _
Security=false;User Id=sa;Password=;”);
  SqlCommand command = new SqlCommand(CmdText, connection);

  SqlDataReader reader = null;      

  try
  {
    connection.Open();
    reader = 
       command.ExecuteReader(CommandBehavior.CloseConnection); 

    // Fill the object with the first entry in the DataReader
    if( reader.Read() ) 
    {
    // Custom DatabaseFieldAttribute assigned to the object
    DatabaseFieldAttribute customAttr;    

    // Ordinal location of a field in the DataReader
    int ordinal = 0; 

    // Custom attributes assigned to the object of the given Type
    object[] attributes;        

    // List of properties that belong to the given Type
    PropertyInfo[] itemTypeProperties = ItemType.GetProperties();

    // Object property
    PropertyInfo property;         

    // Loop through the properties of the specified Type
    for(int i[rlb7]  = 0; i < itemTypeProperties.Length; i++ )
    {
    // Select a property from the property list
    property = itemTypeProperties[i]; 

    // Get the DatabaseField attribute of the current property
    attributes = property.GetCustomAttributes(
             typeof(DatabaseFieldAttribute),true);            

    // Verify the property has only one DatabaseField attribute
    if( attributes != null && attributes.Length == 1 ) 
    {
      // Check if the attribute has a database field name
      customAttr = (DatabaseFieldAttribute) attributes[0];
      if( customAttr != null && customAttr.FieldName.Length > 0 )
      {
      // Determine if the database field exists 
      // in the DataReader
      ordinal = reader.GetOrdinal(customAttr.FieldName);
      if( ordinal >= 0 )
      {
        property.SetValue(fillObject, 
          reader.GetValue(ordinal), null);
      }
      }
    }
    }
    }
    return fillObject;
  }
  finally
  {
    // Clean up after ourselves
    if( reader != null ) reader.Close();
    if( command != null ) command.Dispose();
    if( connection != null ) connection.Dispose();
  }
}

Using the DataReflector

So now for the grand finale. Let’s use our DataReflector class to get an instance of the Product class populated with a result. The FillObject method in the DataReflector class is static we don’t need to create an instance of the
DataReflector to use it. FillObject returns an object so we need to cast the return value as the desired type.

string query = “SELECT * FROM Products WHERE ProductID=11”;

Product product = 
   (Product) DataReflector.FillObject(query, typeof(Product));

Possible Enhancements

Now we have an automated way for getting out database data. There are all sorts of enhancements that could make this even more valuable. Here are some ideas that you could consider for yourself.

• Modify the databaseFieldAttribute to include other properties such as a default value or validation.
• Create a method similar to FillObject that creates and returns an array of objects for when there is more than row in the query result.
• Abstract the use of the
  System.Data.SqlServer to make it data provider independent.
• Create an additional FillObject in the DataReflector that allows for an existing object to be used instead of creating an object. An example where this would be useful is in an ASP.NET page where you have a form to display or edit database data. In the code behind of the page you could create properties and bind the properties to server controls on the form.
  The object represented by the code behind page could then be passed to FillObject and automatically populated with data from the database, which then in turn populates the form contents.

Future Columns

The next article in this column will be on building a database access layer that is independent of a particular data provider. If you have something in particular that you would like to see explained here you can reach me at mstrawmyer@crowechizek.com

About the Author

Mark Strawmyer, MCSD, MCSE (NT4/W2K), MCDBA is a Senior Architect of .NET applications for large and mid-size organizations. Mark is a technology leader with Crowe Chizek in Indianapolis, Indiana. He specializes in architecture, design and development of Microsoft-based solutions. You can reach Mark at mstrawmyer@crowechizek.com.