Introduction

Many developers hate one thing about software development, writing SQL Statements. SQL is a language on its own and it is ubiquitous in today's development environment. When there are changes made to the tables, we have to modify the affected SQL statements, be it in the application codes or in Stored Procedures.

Would it be good if we do not have to write or modify SQL statements, just modify some C# codes, compile and Voila, your application is good to go.

Can it be done?

Sure.

How?

We will be using Attributes that can be used on your objects to map the properties to a Database Table Column. And we will look into  Reflection classes to help us to create a simple SELECT Statement Command dynamically, with parameters and values assigned to the parameters.

Attributes

During development, we may have used attributes in our codes, one of the most commonly used attributes is the WebMethod attributes to expose methods in a web service. And who can forget about DllImport when calling Windows API.

For this solution, we will need to create a custom attribute object. To do that, we have to create a class and inherit the Attribute class.

For this class, we need to declare 3 variables and exposed it through properties.

1.  _DatabaseColumn, this variable stores the database Column name
2. _ValueType, this variable represents the Value Type
3.  _PrimaryKey, indicates if the Database Column is a primary key

Next, we need to modify the Constructor to initialize the 3 variables

And we are ready to get to the next level.

Implementing the Attribute

Below is the code for the object that I will be using for this example

}

Assume you have an existing customer object that you want to generate SQL statement from. All you need now is to add in the attributes to the Get function of each property. Why put it at the GET function? This is because we will be using Reflection to extract the value. We will come to the Reflection Part later.

Below are the codes, you noticed that I set the Primary Key to True for CustomerIDNumber. This means that I have determined that the Customer ID number in the database is the Primary Key. You can determine your own Primary Key(s) , it is advisable to follow the relevant Database Table that the object is representing. If there are no Primary Keys set for the Database Table, you can determine your own for this object.

}

Executing it with Reflection

To retrieve the attributes from the object, we have to go deeper into the .NET framework. We are going to use classes in the Reflection namespace to extract the attribute details from the Customer object.  Below are the codes to create a generic DbCommand object with a Select Statement with Parameters in place.  

}

Beyond Select Statements

This is a very practical example of using Attributes and Reflection. You may even create your own Insert Statement Commands with these codes. And also you may want to extend the DAOAttribute object to cater to your requirements.

Besides generating SQL statements, there are a lot of cool stuff that you can do with Attributes and Reflection, you can even create your own XML Attributes and Formatter for the classes you created!!!