Generate SQL Statements With Objects, Attributes and Reflection

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 need to modify the affected SQL statements, be it in the application code or in Stored Procedures.

Would it be good if we do not need 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 code. One of the most commonly used attributes is the WebMethod attributes to expose methods in a web service. And who can forget DllImport for calling the Windows API.

For this solution, we need to create a custom attribute object. To do that, we need 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 expose them 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;
    }
}

 

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.

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

The following 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 a SQL statement from. All you need now is to add in the attributes to the Get function of each property. Why use the GET function? This is because we will be using Reflection to extract the value. We will get to the Reflection part later.

The following is the code. Notice 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 then 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 need to go deeper into the .NET framework. We will use classes in the Reflection namespace to extract the attribute details from the Customer object.  The following 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 is 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 create!