SQL Script Generator Using Generics and Reflection

• Download source code - 28.1 KB

Introduction

This article will demonstrate how to use Generics and Reflection in order to programmatically create SQL scripts using C#. Although there are many ways to create SQL scripts automatically, I have come to realize that using Generics and Reflection makes the generation of SQL queries much easier. I decided to come-up with this kind of an example for Generics and Reflection because I noticed a few things in the companies that I have worked with: they manually create SQL scripts and add those scripts to the code. I have no question there though, but I was thinking that it would be much easier if we let the framework handle the very repetitive SQL script creation. As we go along, I will define Generics and Reflection and then I will show how to use them to generate SQL scripts.

Sample Scenario

Every time we create a database table(s) or add a new column(s), we always need to spend time creating Insert, Update, Delete, and Select SQL statements instead of focusing on the business logic. For example, the client would like to have Office Supplies Information be added to the existing system. The developer has to create an OfficeSupply database table and define its corresponding columns. Then the developer has to create the UI interface, code-behind, and then the SQL queries for the newly added table. With that process alone, the developer at some point will be introduced to the following problems:

Common Problems

1. The programmer might forget to update the SQL statement in the program after updating the database object.
2. The programmer will have to think of the repetitive tasks instead of focusing on the business layer or the main logic.
3. Could trigger some typographical errors.

What is Generics

A Generics type is a single programming element that adapts to perform the same functionality for a variety of data types. It is the technique used in .NET to help programmers write code without the need to test for the correct datatype because it is already enforced at compile time by the .NET compiler.

Common Benefits of Generics

• Eliminate the need to box and unbox a value stored in an object.
• Let the compiler do type checking at compile time rather than runtime.

It’s difficult to picture out the idea of Generics without first having an understanding of boxing and unboxing. Though boxing and unboxing is out of the scope of this article, to give you a brief description: Boxing is the technique where you convert the value of a specific type into a common data type called object; e.g., object intObjectValue = 123;. Unboxing is the opposite of boxing; it converts the object type to a specific data type; e.g., int i = (int) intObjectValue;. For more information about boxing and unboxing, please click this link: http://www.codeproject.com/KB/cs/boxing.aspx.

Analogy of Generics

In Figure 1, you’ll notice that the father instructs the boy to get some asphalt and the son wonders what asphalt is and what container he should use to get the asphalt. The son uses the plastic container without thinking that the asphalt might be hot. So as a result, when the son returns, he brings with him the melted plastic container with a few asphalt left inside, and the father is so mad about it. Unlike in Figure 1, in Figure 2, the father gives an explicit instruction to the son to use the metal wheelbarrow for getting the asphalt, for the father has the idea that the son might have a wrong impression of what asphalt is, whether it's hot or not. And the father knows that using plastic is no good for it will just melt.

Figure 2 is the same with Generics, where developers need to give explicit instruction to use a specific data type in manipulating or getting the value(s) for we already have the best picture in mind of what it's going to be like. The following table contains some common examples of Generics:

Different Kinds of Generics	Note: I am not going to discuss the details of each kind of Generics; however, I will be providing links below for your direction to detailed explanation for each. In this article, I will, however, give an example for a Generic class. For a detailed definition of the different kinds of Generics, please follow this link: http://msdn.microsoft.com/en-us/library/512aeb7t(v=vs.80).aspx.
Generic Collection
Generic Class
Generic Interfaces
Generic Methods
Generic Delegates

Sample Generics Code

I will be using a Generic Collection in this example using a key/value pair Dictionary. Its syntax is Dictionary<TKey, TValue>. TKey and TValue represent any data type being passed as a parameter, so if we type Dictionary<int, string>, it means TKey is an integer type while TValue is a string type.

//Creating an instance
Dictionary<string, int> genCollection = new Dictionary<string, int>();

//Adding the value to the collection
genCollection.Add("index1",123);
genCollection.Add("index2",456);

//Getting the value to the collection
genCollection["index1"];    //this will output 123

//Erroneous Approach
genCollection.Add("index1", "123");
//This will prompt an error at compile time trying
//to insert string value to integer type

What is Reflection

This is a .NET framework API for version 2.0 that gives the privilege to the developer to fetch type (assembly) information programmatically during runtime. This information includes Type, Properties, Fields (class variables), Methods, and Events of an object.

Using the System.Reflection namespace, you can have access to the following classes, which are the most common classes that developers can use to obtain information in an assembly.

Common Classes	Description
FieldInfo	Holds information of the variable defined in the class.
MethodInfo	Holds information of the functions/methods defined in the class.
PropertyInfo	Holds information of the properties defined in the class.
EventInfo	Holds information of events defined in the class. Use this for late binding.

The information below in Figure 3 shows how, using Reflection, we'll be able to get Type information.

Analogy of Reflection

Reflection is just like a blue print of (let's say) a house. Through it, we get the idea of the house layout, how it looks like, or how many rooms are there in the house. Basically, it’s very easy to have a visual representation of how it would look like inside the house with the help of the blue print. So if you wanted to obtain assembly information of black box programs, third party programs, etc., you need to use Reflection to obtain such information like how many methods are there, how to invoke a method, and much more.

Purpose of Reflection

The purpose of Reflection is to give the developer an upper hand to modify or observe other assemblies or its own structure and behavior at runtime.

Benefits of Reflection

• Can dynamically bind the object properties to an array of the Stored Procedure’s parameter.
• Dramatically decrease our number of code base and make it easy to maintain applications.
• Use to create customizable application architecture.

Sample Visual Representation of Reflection in the Process

As you can see in Figure 5, using Reflection, you will not only have access to the assembly created using .NET, but also gain access to assemblies in other languages such as Java and C++, for instance.

The following is a code snippet of how to implement Reflection to obtain the list of methods in a class. To get the class name of the object, just enter, based on the code below, type.name, and this will return a string value Student. You can try to explore using the GetFields method to return an array of the FieldInfo class that will represent the number of class variables being declared inside the class Student.

Script Generation User Interface

This is the sample UI that can be used to Generate a SQL Query string where the result string is ready for parsing for the actual value. In this example, we only have three database tables: Employee, Position, and Asset master tables. To begin with, it takes into consideration that each table has a corresponding class object where a column is defined as a property that will hold the value for data binding. I will later write a separate article on how to automatically create a class object based on database tables. For now, we'll just assume that these classes were already generated.

This application is comprised of four entities: frmGenerateSQLScript.cs, GenericEntity.cs, EmployeeEntity.cs, and PositionEntity.cs. The following is how it looks like in the Solution Explorer panel:

frmGenerateSQLScript.cs

Based on the screenshot shown in Figure 7, the code below is the event handler that will trigger when we click the Generate button. In here, we'll just call the GetQuery() method to return the desired SQL query. The trick to identify the selected database table is to look for the entity inside the less than and the greater than brackets, "<" and ">".

/// <Summary>
/// Event to execute the script generation
/// </Summary
private void btnRun_Click(object sender, EventArgs e)
{
    int sqlOption = rdoSelect.Checked ? 0 : 
        rdoUpdate.Checked ? 1 : rdoDelete.Checked ? 2 : 3;
    if (rdoEmployee.Checked)
        rtxtResult.Text = new GenericEntity<EmployeeEntity>(sqlOption).GetQuery();
    else if(rdoPosition.Checked)
        rtxtResult.Text = new GenericEntity<PositionEntity>(sqlOption).GetQuery();
    else
        rtxtResult.Text = new GenericEntity<AssetEntity>(sqlOption).GetQuery();
    
}

PositionEntity

This is an auto-generated .cs file and I am going to write a separate article on how to generate .cs files for newly added tables. This class has a property that corresponds to the columns of the database table named Position. These properties will be utilized for databinding, but that topic is out of the scope of this article.

using System;

namespace GenericSQLGenerator
{
    public class PositionEntity
    {
        private Guid _positionID;
        private string _title;
        private string _titleDescription;
        private DateTime _DateCreated;
        private DateTime _DateModified;
        private Guid _CreatedBy;
        private Guid _ModifiedBy;

        public PositionEntity() { }

        public Guid PositionID
        {
            get { return _positionID; }
            set { _positionID = value; }
        }
        public string Title
        {
            get { return _title; }
            set { _title = value; }
        }
        public string TitleDescription
        {
            get { return _titleDescription; }
            set { _titleDescription = value; }
        }
        public DateTime DateCreated
        {
            get { return _DateCreated; }
            set { _DateCreated = value; }
        }
        public DateTime DateModified
        {
            get { return _DateModified; }
            set { _DateModified = value; }
        }
        public Guid CreatedBy
        {
            get { return _CreatedBy; }
            set { _CreatedBy = value; }
        }
        public Guid ModifiedBy
        {
            get { return _ModifiedBy; }
            set { _ModifiedBy = value; }
        }
    }
}

EmployeeEntity.cs

Same as PositionEntity.cs, it has properties corresponding to the columns of the Employee database table. This is automatically generated and the utility used to generate it is yet to be made.

using System;

namespace GenericSQLGenerator
{
    public class EmployeeEntity
    {
        private Guid _employeeID;
        private string _firstName;
        private string _lastName;
        private string _address;
        private string _gender;
        private string _maritalStatus;
        private DateTime _dateHired;
        private DateTime _dateResigned;
        private DateTime _DateCreated;
        private DateTime _DateModified;
        private Guid _CreatedBy;
        private Guid _ModifiedBy;

        public EmployeeEntity() { }

        public Guid EmployeeID
        {
            get { return _employeeID; }
            set { _employeeID = value; }
        }
        public string FirstName
        {
            get { return _firstName; }
            set { _firstName = value; }
        }
        public string LastName
        {
            get { return _lastName; }
            set { _lastName = value; }
        }
        public string Address
        {
            get { return _address; }
            set { _address = value; }
        }
        public string Gender
        {
            get { return _gender; }
            set { _gender = value; }
        }
        public string MaritalStatus
        {
            get { return _maritalStatus; }
            set { _maritalStatus = value; }
        }
        public DateTime DateHired
        {
            get { return _dateHired; }
            set { _dateHired = value; }
        }
        public DateTime DateResigned
        {
            get { return _dateResigned; }
            set { _dateResigned = value; }
        }
        public DateTime DateCreated
        {
            get { return _DateCreated; }
            set { _DateCreated = value; }
        }
        public DateTime DateModified
        {
            get { return _DateModified; }
            set { _DateModified = value; }
        }
        public Guid CreatedBy
        {
            get { return _CreatedBy; }
            set { _CreatedBy = value; }
        }
        public Guid ModifiedBy
        {
            get { return _ModifiedBy; }
            set { _ModifiedBy = value; }
        }
    }
}

AssetEntity.cs

This has properties corresponding to the columns of the Asset database table.

using System;

namespace GenericSQLGenerator
{
    public class AssetEntity
    {
        private Guid _AssetID;
        private string _Name;
        private string _AssetNo;
        private string _Description;
        private DateTime _DateCreated;
        private DateTime _DateModified;
        private Guid _CreatedBy;
        private Guid _ModifiedBy;

        public AssetEntity() { }

        public Guid AssetID
        {
            get { return _AssetID; }
            set { _AssetID = value; }
        }
        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }
        public string AssetNo
        {
            get { return _AssetNo; }
            set { _AssetNo = value; }
        }
        public string Descriptioin
        {
            get { return _Description; }
            set { _Description = value; }
        }
        public DateTime DateCreated
        {
            get { return _DateCreated; }
            set { _DateCreated = value; }
        }
        public DateTime DateModified
        {
            get { return _DateModified; }
            set { _DateModified = value; }
        }
        public Guid CreatedBy
        {
            get { return _CreatedBy; }
            set { _CreatedBy = value; }
        }
        public Guid ModifiedBy
        {
            get { return _ModifiedBy; }
            set { _ModifiedBy = value; }
        }
    }
}

GenericEntity.cs

The one that will do the tricks in generating the SQL query is the GenericEntity class. You don't need to modify the methods GetInsert(), GetUpdate(), GetDelete(), and GetSelect() here because it will dynamically fetch all the properties being declared in our table objects (EmployeeEntity.cs, PositionEntity.cs, and AssetEntity.cs) which would represent our database table columns. In this event, we only use the GetProperties() method to fetch an array of PropertyInfo objects.

using System;
using System.Text;

namespace GenericSQLGenerator
{
    public class GenericEntity <t />
    {
        int _sqlOption;

        public GenericEntity()
        {
            _sqlOption = 0;
        }
        public GenericEntity(int sqlOption)
        {
            _sqlOption = sqlOption;
        }

        public string GetQuery()
        {
            if (_sqlOption == 0)
                return GetSelect();
            else if (_sqlOption == 1)
                return GetUpdate();
            else if (_sqlOption == 2)
                return GetDelete();
            else
                return GetInsert();
        }

        private string GetDelete()
        {
            Type type = typeof(T);
            StringBuilder sbQry = new StringBuilder();
            System.Reflection.PropertyInfo[] propInfo = type.GetProperties();

            sbQry.AppendFormat("Delete From {0} Where {1}={2}", 
              type.Name.Replace("Entity", string.Empty), propInfo[0].Name, "{0}");

            return sbQry.ToString();
        }

        private string GetUpdate()
        {
            int ctr = 0;
            Type type = typeof(T);
            StringBuilder sbQry = new StringBuilder();
            System.Reflection.PropertyInfo[] propInfo = type.GetProperties();
            foreach (System.Reflection.PropertyInfo pi in propInfo)
            {
                if (sbQry.ToString() == string.Empty)
                    sbQry.AppendFormat("Update {0} Set {1}={2}", 
                             type.Name, pi.Name, "[" + ctr + "]");
                else
                    sbQry.AppendFormat(", {0}={1}", pi.Name, "[" + ctr + "]");

                ctr++;
            }

            if (sbQry.ToString() != string.Empty)
                sbQry.AppendFormat(" Where {0}={1} ", propInfo[0].Name, "[" + ctr + "]");

            sbQry.Replace("[", "{").Replace("]", "}");

            return sbQry.ToString();
        }

        private string GetInsert()
        {
            StringBuilder sb = new StringBuilder();
            int ctr = 0;

            Type type = typeof(T);
            StringBuilder sbQry = new StringBuilder();
            System.Reflection.PropertyInfo[] propInfo = type.GetProperties();
            foreach (System.Reflection.PropertyInfo pi in propInfo)
            {
                if (sbQry.ToString() == string.Empty)
                    sbQry.AppendFormat("INSERT INTO {0} ({1}", 
                       type.Name.Replace("Entity", string.Empty), pi.Name);
                else
                {
                    sbQry.AppendFormat(", {0}", pi.Name);
                    sb.Append(",");
                }
                sb.Append("{" + ctr++ + "}");
            }

            if (sbQry.ToString() != string.Empty)
                sbQry.AppendFormat(") VALUES({0})", sb.ToString());

            return sbQry.ToString();
        }

        private string GetSelect()
        {
            Type type = typeof(T);
            StringBuilder sbQry = new StringBuilder();
            System.Reflection.PropertyInfo []propInfo = type.GetProperties();
            foreach (System.Reflection.PropertyInfo pi in propInfo)
            {
                if (sbQry.ToString() == string.Empty)
                    sbQry.AppendFormat("Select {0}", pi.Name);
                else
                    sbQry.AppendFormat(", {0}", pi.Name);
            }

            if (sbQry.ToString() != string.Empty)
                sbQry.AppendFormat(" From {0} ", type.Name.Replace("Entity",string.Empty));

            return sbQry.ToString();
        }
    }
}

History

• [04.11.11]: New release.