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.
public class DAOAttribute : System.Attribute
{
public DAOAttribute() : base()
{
}
}
For this class, we need to declare 3 variables and exposed it through properties.
private string _DatabaseColumn;
private Type _ValueType;
private bool _PrimaryKey;
public string DatabaseColumn
{
get
{
return _DatabaseColumn;
}
set
{
_DatabaseColumn = value;
}
}
public Type ValueType
{
get
{
return _ValueType;
}
set
{
_ValueType = value;
}
}
public bool PrimaryKey
{
get
{
return _PrimaryKey;
}
set
{
_PrimaryKey = value;
}
}
- _DatabaseColumn: this variable stores the database
- _Column name: ValueType This variable represents the Value Type
- _PrimaryKey: indicates if the Database Column is a primary key
Next, we need to modify the Constructor to initialize the 3 variables.
public DAOAttribute(string databaseColumn, Type valueType, bool primaryKey) : base()
{
_DatabaseColumn = databaseColumn;
_ValueType = valueType;
_PrimaryKey = primaryKey;
}
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
public class Customer
{
private string _CustomerFirstName;
private string _CustomerLastName;
private string _CustomerIDNumber;
public Customer()
{
_CustomerFirstName = "";
_CustomerLastName = "";
_CustomerIDNumber = "";
}
public string CustomerFirstName
{
get
{
return _CustomerFirstName;
}
set
{
_CustomerFirstName = value;
}
}
public string CustomerLastName
{
get
{
return _CustomerLastName;
}
set
{
_CustomerLastName = value;
}
}
public string CustomerIDNumber
{
get
{
return _CustomerIDNumber;
}
set
{
_CustomerIDNumber = value;
}
}
}
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 KeyYou 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.
public string CustomerFirstName
{
[DAO("FirstName", typeof(string), false)]
get
{
return _CustomerFirstName;
}
set
{
_CustomerFirstName = value;
}
}
public string CustomerLastName
{
[DAO("LastName", typeof(string), false)]
get
{
return _CustomerLastName;
}
set
{
_CustomerLastName = value;
}
}
public string CustomerIDNumber
{
[DAO("CustID", typeof(string), true)]
get
{
return _CustomerIDNumber;
}
set
{
_CustomerIDNumber = value;
}
}
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.
private DbCommand CreateSelectCommand(object dataObject, DbProviderFactory factory, string TableName)
{
Type t = dataObject.GetType();
DAOAttribute dao;
DbCommand cmd = factory.CreateCommand();
DbParameter param;
StringCollection Fields = new StringCollection();
StringBuilder sbWhere = new StringBuilder(" WHERE ");
bool HasCondition = false; // Indicates that there is a WHERE Condition
foreach (System.Reflection.MethodInfo mi in t.GetMethods()) // Go thru each method of the object
{
foreach (Attribute att in Attribute.GetCustomAttributes(mi)) // Go thru the attributes for the method
{
if (typeof(DAOAttribute).IsAssignableFrom(att.GetType())) // Checks that the Attribute is of the right type
{
dao = (DAOAttribute)att;
Fields.Add(dao.FieldName); // Append the Fields
if (dao.PrimaryKey)
{
// Append the Conditions
if (HasCondition) sbWhere.Append(" AND ");
sbWhere.AppendFormat("{0} = @{0}", dao.FieldName);
param = factory.CreateParameter();
param.ParameterName = "@" + dao.FieldName;
if (cmd.Parameters.IndexOf(param.ParameterName) == -1)
{
param.DbType = (DbType)Enum.Parse(typeof(DbType), dao.ValueType.Name);
cmd.Parameters.Add(param);
param.Value = mi.Invoke(dataObject, null);
}
HasCondition = true; // Set the HasCondition flag to true
}
}
}
}
string[] arrField = new string[Fields.Count];
Fields.CopyTo(arrField, 0);
cmd.CommandText = "SELECT " + string.Join(",", arrField) + " FROM " + TableName + (HasCondition ? sbWhere.ToString() : " ");
return cmd;
}
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.