Generate SQL Statements with objects, Attributes and Reflection

Related EbooksTop Videos

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 this._DatabaseColumn ;
    }
    set
    {
        _DatabaseColumn = value;
    }
}

public Type ValueType
{
    get
    {
        return this._ValueType ;
    }
    set
    {
        _ValueType = value
    }
}

public bool PrimaryKey
{
    get
    {
        return this._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;

}

}

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.

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) == 0)

{

param.DbType = (DbType)Enum.Parse(typeof(DbType), dao.ValueType.Name);

cmd.Parameters.Add(param);

param.Value = mi.Invoke(obj, 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!!!