Wrapper Code Generator for MS SQL Server Databases

• Download source files - 22.4 Kb
• Download demo project - 20.7 Kb

Introduction

Today, application development paradigm has expanded into many areas. Developers/ architects use many different approaches to keep their phase with the fast moving industry by developing applications rapidly. In all cases, the bottom lines are speed, accuracy, scalability and performance of the system they develop. When considering this, writing of database manipulation (model) class for a fairly big database can be both tedious and potentially error prone with many developers using a cut and paste solution each time. This kind of repetition leads one to consider alternative approaches based on code generation techniques, code that automatically generates the wrappers for your database. This article is written to respond to the need to deliver database driven systems very fast, preferable if the development team is small.

In this article, I don’t force developers to adapt any particular development methodology or process, as well as do not force to use any specific architecture in order to develop the system. As far as the comments I gathered so far, this type of code generation tool is most suitable for applications that have separated-class-set to manipulate the database operations.

The application generates two types of classes, one is a set of C# (referred as CS here in after) files which map into the database tables. They will have the same class name as the table name, and they will have the properties that are same as the table attributes. The other is another set of CS files which covers the basic/ standard database operations such as add, delete, update (one whole row or separately each column) and select (one whole row or separately each column). As the above figure illustrates, the developers can directly plug the auto generated code into their application's architecture. The code generation tool will only generate a set of predictable methods as described, and right now, the tool does not respond to relations that you have in the database schema as well as for complex data mining options.

Background

In practice, changing the database schema after fully or partially developing the application is a fairly common experience for many developers today. Many times, the system specification changes at the development stages of the system, or else the development methodologies we adapt require that type of restructuring/ flexibility. Then, once you change the database, as a parallel process, you got to change the model classes (if you are following the MVC – Model View Controller; architecture), in other words, the class sets that handle the database manipulation and operations. This is time consuming as well as this reduces the developer’s enthusiasm. This article presents an automated approach to solve the problem completely. There, the application will develop a set of classes that are responsible for doing most of the database manipulations, such as add, delete, update, and select field and records.

Using the Application

• Input to the application:
  1. The server name or the IP (Internet Protocol) address of the machine you have the MS SQL Server running.
  2. The name of the database.
  3. Super user credential, i.e., user name and password to login to the database server.
  4. Adding "Namespace" property is currently not active.
• Process:
  1. Click button named "Connect and Get Data".
  2. Click button named "Creates Model Classes".
• Output of the application:
  1. Generates a set of CS files (to do all basic database operations such as add, edit, delete, select etc.) mapping each of the table of the database, in the application's working folder.

Application Overview

The figure illustrates a sample output of the application. Here in this example, we have used the "Sale" table as the source database table for this application. Once you provide all required details as it was described in the “Using the Application” section above, it creates two CS classes named "Sale" and "HandlerSale". Sale class is the one which maps with the column name of the table. Since the table containes two columns named “saleid” and “description”, the Sale class has two properties named saleid and description. The HandlerSale is the class that has code to do all basic database manipulations. The Handler class uses “Sale” type object as parameter and/or return type as is needed by its methods. The application generates all the T-SQL needed along with parameters as well. These things also will reside with the “Handler” class. In simple terms, once the application generates the classes, you have nothing to do other than straightaway add them to your project.

Requirements

The users who read/ test this article need to have some understanding of C# Windows Forms applications, MS SQL Server and T-SQL. Additionally, you are expected to have the Visual Studio .NET IDE and MS SQL Server 7 or higher installed in your machine (need the super user credentials to login to the DB server). After all, if you are familiar with popular application development architectures such as MVC, it would be an added advantage.

Note: If you are planning to use a SQL Server that resides in a network machine, you need to have the "SQL Server Enterprise Manager" installed in your machine to create a test database, or you need to talk to your friendly network admin to create a test database for you.

Important: To use the code, you have to add reference to the "Microsoft.ApplicationBlocks.Data.dll", which resides in the DLL folder of the "CSharp_Wrapper_src" directory.

Using the code

The application consists of four classes named:

• ModelCreatorFrm - The main user interface of the application.
• DynamicSqlGenerator - The class which is responsible for creating code dynamically depending on the information gathered from the database.
• Table - The one which temporarily stores details about the tables of the database as well as generates the methods and variable declarations for each table.
• Attribute – It holds all the details related to each column of the tables. This class is a private class of the Table class.

Application Flow

The application starts as the user clicks on the "Connect and Get Data" button. The Click event fires the method named GetAllTheDetials, after initializing the connection string and getting the DataReader to read the table data of the given database.

private void lbtnConnect_Click(object sender, System.EventArgs e)
{
    if (CreateConnectionString())
    {
        GetAllTheDetials(tcGetDataReader());
        this.lbtnCreate.Enabled = true;
    }
}

Then, application creates object of type "Table" for each table and stores them in memory using a System.Collection.ArrayList named “lobjTables”. Once this is finished, user can click on the second button named "Creates Model Classes", which will fire the method below. This method is responsible for generating the code using the “lobjTables” ArrayList.

private void lbtnCreate_Click(object sender, System.EventArgs e)
{
    StringBuilder sbMapClass, sbHandlerClass;
    StreamWriter swMapClass = null;
    StreamWriter swHandlerClass = null;
    foreach(Table tb in lobjTables)
    {
        try
        {
            //Get the class name for the Database Map class
            //Example Asset.cs
            sbMapClass = new System.Text.StringBuilder(tb.Name);
            sbMapClass.Append(".cs");
            //Get the Class name for the DB handler class
            //Example HandlerAsset.cs
            sbHandlerClass = new StringBuilder("Handler");
            sbHandlerClass.Append(tb.Name);
            sbHandlerClass.Append(".cs");
            
            FileInfo lobjFileInfoMapClass = new FileInfo(
                sbMapClass.ToString());
            FileInfo lobjFileInfoHandlerClass = new FileInfo(
                sbHandlerClass.ToString());
            
            swMapClass = lobjFileInfoMapClass.CreateText();
            swHandlerClass = lobjFileInfoHandlerClass.CreateText();

            swMapClass.Write(tb.MapClassTopPartCode.ToString());
            swMapClass.Write(tb.MapClassMidPartCode.ToString());
            swMapClass.Write(tb.MapClassBottomPart.ToString());

            swHandlerClass.Write(tb.HandlerClassCode.ToString());
        }
            //The file name is a directory.
        catch (System.UnauthorizedAccessException ev)
        {
            MessageBox.Show(ev.Message);
        }
            //The disk is read-only.
        catch (System.IO.IOException ev)
        {
            MessageBox.Show(ev.Message);
        }
            //The caller does not have the required permission.
        catch (System.Security.SecurityException ev)
        {
            MessageBox.Show(ev.Message);
        }
        finally 
        {
            swMapClass.Flush();
            swHandlerClass.Flush();
            swMapClass.Close();
            swHandlerClass.Close();
        }
    }
    MessageBox.Show("Done !!");
    this.lbtnCreate.Enabled = false;
}

Get Table's Name, Attributes and Types from the Database

In the above code, one of the most interesting and important section is the T-SQL command that is used to get the table name list of the database along with other additional data.

private const string SQL_GET_TABLES = "SELECT 
        table_name, 
        column_name, 
        data_type FROM information_schema.columns 
            WHERE table_name in (select table_name    
                FROM Information_Schema.Tables 
                    WHERE Table_Type='Base Table') 
        ORDER BY table_name";

Please note that you can use "*" to see all the selections other than the table_name, column_name, and data_type.

Get the Primary Key of the Table

Another important T-SQL command is the one which is used to select the primary key of a given table.

private const string SQL_SELECT_PRIMARYKEY = "SELECT 
        column_name 
        FROM information_schema.key_column_usage 
            WHERE constraint_name like 'pk%' 
                and table_name=@tablename";

Once every thing finishes, you should get a set of commented and formatted CS classes, having two classes for each table in the database. Below you see the classes generated for the table named Sale.

Sale Class Generated for the Sale Table

using System;

public class Sale
{
    /// <SUMMARY>
    /// Default Contructor
    /// <SUMMARY>
    public Sale()
    {}


    public string saleid
    { 
        get { return _saleid; }
        set { _saleid = value; }
    }
    private string _saleid;

    public string description
    { 
        get { return _description; }
        set { _description = value; }
    }
    private string _description;

    public Sale(

        string saleid, 
        string description)
    {
        this._saleid = saleid; 
        this._description = description; 
    }
}

HandlerSale Class Generated for the Sale Table

using System;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Text;
using Microsoft.ApplicationBlocks.Data;
/// <summary>
/// Summary description for HandlerTBN.
/// </summary>
public class HandlerSale
{
    private const string SQL_CONN_STRING 
        = "data source=PRD-01;initial catalog=ManGoDB;"
          + "integrated security=false;persist" + 
          " security info=True;User ID=sa;Password=d7972";
    private const string SQL_DELETE_Sale 
        = "DELETE FROM Sale WHERE saleid = @saleid";
    private const string SQL_SELECT_Sale 
        = "SELECT * FROM Sale WHERE saleid = @saleid";
    private const string SQL_INSERT_Sale 
        = "INSERT INTO Sale VALUES(@saleid, @description)";
    private const string SQL_UPDATE_Sale 
        = "UPDATE Sale SET saleid = @saleid," + 
          " description = @description WHERE saleid = @saleid";
    private const string SQL_UPDATE_description 
        = "UPDATE Sale SET description = @description WHERE saleid = @saleid";
    private const string SQL_SELECT_description
        = "SELECT description FROM Sale WHERE saleid = @saleid";
    private const string PARAM_saleid 
        = "@saleid";
    private const string PARAM_description 
        = "@description";
    public HandlerSale()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    /// <summary>
    /// Insert a New TBN record to the table
    /// </summary>
    /// <param name="tobjTBN">Object to be inserted to the table</param>
    /// <returns>Status of the data insertion</returns>
    public bool InsertSale(Sale tobjSale)
    {
        if(tobjSale != null)
        {
            //Get the parameter list needed by the given object
            SqlParameter[] lParamArray =  GetParameters(tobjSale);
            SetParameters(lParamArray, tobjSale);
            //Get the connection
            SqlConnection con = GetConnection(SQL_CONN_STRING);
            if (con == null)
                //Connection is not created 
                return false;
            //Execute the insertion
            int i = SqlHelper.ExecuteNonQuery(
                con, 
                CommandType.Text, 
                SQL_INSERT_Sale, 
                lParamArray);
            //Dispose the Sql connection 
            con.Dispose();
            if (i ==1)
                //Done and insert the object to the table
                return true;
            else 
                //Fail to execute teh insertion
                return false;
        }
        else
            //No object found to insert
            return false;
    }
    /// <summary>
    /// Get a Object given the Object ID
    /// </summary>
    /// <param name="tstrObjectId"></param>
    /// <returns></returns>
    public Sale tcSelectSale(string tstrsaleid)
    {
        // SqlConnection that will be used to execute the sql commands
        SqlConnection connection = null;
        SqlParameter[] aParms = 
            new SqlParameter[]{
                     new SqlParameter(PARAM_saleid
                    , tstrsaleid)};
        try
        {
            try
            {
                connection = GetConnection(SQL_CONN_STRING);
            }
            catch (System.Exception e)
            {
                //lobjError = ManGoErrors.ERROR_CONNECT_TO_DB;
                //log.Error(lobjError, e);
                return null;
            }
            // Call ExecuteDataReader static method of 
            // SqlHelper class that returns an DataReader
            // We pass in an open database connection object
            // , command type, and command text
            SqlDataReader reader = SqlHelper.ExecuteReader(
                connection
                , CommandType.Text
                , SQL_SELECT_Sale
                , aParms);
            // read the contents of data reader and return the results:
            while (reader.Read())
            {
                return new Sale(
                    reader.GetString(0),
                    reader.GetString(1));
            }
            // close Reader
            reader.Close();
            return null;
        }
        catch(Exception ex)
        {
            //lobjError = ManGoErrors.ERROR_UNKNOWN;
            System.Diagnostics.Trace.WriteLine(ex.Message);
            return null;
        }
        finally
        {
            if(connection != null)
                connection.Dispose();
        }
    }
    /// <summary>
    /// Get a Column given the Object ID
    /// </summary>
    /// <param name="tstrObjectId"></param>
    /// <returns></returns>
    public string tcSelectdescription(string tstrsaleid)
    {
        // SqlConnection that will be used to execute the sql commands
        SqlConnection connection = null;
        SqlParameter[] aParms = new SqlParameter[]{
                             new SqlParameter(
                                PARAM_saleid
                                , tstrsaleid)};
        try
        {
            try
            {
                connection = GetConnection(SQL_CONN_STRING);
            }
            catch (System.Exception e)
            {
                //lobjError = ManGoErrors.ERROR_CONNECT_TO_DB;
                //log.Error(lobjError, e);
                return string.Empty;
            }
            // Call ExecuteReader static method of
            // SqlHelper class that returns an DataReader
            // We pass in an open database connection object
            // , command type, and command text
            SqlDataReader reader = SqlHelper.ExecuteReader(
                connection
                , CommandType.Text
                , SQL_SELECT_description
                , aParms);
            // read the contents of Data reader and return the result:
            while (reader.Read())
            {
                return reader.GetString(0);
            }
            // close Reader
            reader.Close();
            return string.Empty;
        }
        catch(Exception ex)
        {
            //lobjError = ManGoErrors.ERROR_UNKNOWN;
            System.Diagnostics.Trace.WriteLine(ex.Message);
            return string.Empty;
        }
        finally
        {
            if(connection != null)
                connection.Dispose();
        }
    }
    /// <summary>
    /// Delete the Profile from the Profile table
    /// </summary>
    /// <param name="tstrObjectId"></param>
    /// <returns></returns>
    public bool tcDeleteSale(string tstrsaleid)
    {
        SqlConnection connection = null;
        SqlParameter[] aParms = new SqlParameter[]{
                             new SqlParameter(
                            PARAM_saleid
                            , tstrsaleid)};
        try
        {
            connection = GetConnection(SQL_CONN_STRING);
            if (connection == null)
                return false;
            // Call ExecuteNoneQuery static method 
            // of SqlHelper class that returns an Int
            // We pass in an open database connection object
            // , command type, and command text
            int i = SqlHelper.ExecuteNonQuery(
                connection
                , CommandType.Text
                , SQL_DELETE_Sale
                , aParms);
            return true;
        }
        catch(Exception ex)
        {
            //lobjError = ManGoErrors.ERROR_UNKNOWN;
            System.Diagnostics.Trace.WriteLine(ex.Message);
            return false;
        }
        finally
        {
            if(connection != null)
                connection.Dispose();
        }
    }
    /// <summary>
    /// Update a object given the object primary key
    /// </summary>
    /// <param name="tstrobjectId"></param>
    /// <returns></returns>
    public void tcUpdateSale(Sale tobjSale)
    {
        SqlParameter[] aParms = GetParameters(tobjSale);
        SetParameters(aParms, tobjSale);
        using (SqlConnection conn = GetConnection(SQL_CONN_STRING))
        {
            // conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    SqlHelper.ExecuteNonQuery(
                        trans
                        , CommandType.Text
                        , SQL_UPDATE_Sale, aParms);
                    trans.Commit();
                }
                catch(System.Exception e)
                {
                    trans.Rollback();
                    //log.Error(lobjError, e);
                    throw;
                }
            }
        }
    }
    /// <summary>
    /// Update an Item given the object primary key
    /// </summary>
    /// <param name="tstrobjectId"></param>
    /// <returns></returns>
    public void tcUpdatedescription(
        string tstrItemId
        , string tobjData)
    {
        SqlParameter[] aParms = new SqlParameter[]{
         new SqlParameter(
         PARAM_saleid
         , tstrItemId)
        , new SqlParameter(PARAM_description, tobjData)};
        using (SqlConnection conn = GetConnection(SQL_CONN_STRING))
        {
            // conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    SqlHelper.ExecuteNonQuery(
                        trans
                        , CommandType.Text
                        , SQL_UPDATE_description
                        , aParms);
                    trans.Commit();
                }
                catch(System.Exception e)
                {
                    trans.Rollback();
                    System.Diagnostics.Trace.WriteLine(e.Message);
                    throw;
                }
            }
        }
    }
    /// <summary>
    /// Get the Parameter List for this object
    /// </summary>
    /// <param name="tobjTBN">TBN object</param>
    /// <returns>Sql Parameter List</returns>
    private SqlParameter[] GetParameters(Sale tobjSale)
    {
        SqlParameter[] objParamArray = SqlHelperParameterCache.GetCachedParameterSet(
            SQL_CONN_STRING
            , SQL_INSERT_Sale);
        if (objParamArray == null)
        {
            //Represents a parameter to a System.Data.SqlClient.SqlCommand, 
            //and optionally, its mapping to System.Data.DataSet columns. 
            objParamArray = new SqlParameter[]
            {
                new SqlParameter(PARAM_saleid, tobjSale.saleid),
                new SqlParameter(PARAM_description, tobjSale.description),
            };
            SqlHelperParameterCache.CacheParameterSet(
                SQL_CONN_STRING
                , SQL_INSERT_Sale
                , objParamArray);
        }
        return objParamArray;
    }
    /// <summary>
    /// Fill database parameters for a perticular profile
    /// </summary>
    private void SetParameters(SqlParameter[] SaleParms,Sale tobjSale)
    {
        SaleParms[0].Value = tobjSale.saleid;
        SaleParms[0].Value = tobjSale.description;
    }
    /// <summary>
    /// Create data base conection
    /// </summary>
    /// <param name="connectionString"></param>
    /// <returns></returns>
    private SqlConnection GetConnection(string tstrConnectionString)
    {
        //Represents an open connection to a SQL Server database.
        //This class cannot be inherited.
        SqlConnection dbconnection = new SqlConnection(tstrConnectionString);
        try
        {
            //Opens a database connection with the property settings
            //specified by the ConnectionString.
            dbconnection.Open();
            return dbconnection;
        }
            //Cannot open a connection without specifying a data source.
            //or The connection is already open.
        catch (System.InvalidOperationException e)
        {
            return null;
        }
            //A connection-level error occurred while opening.
        catch (System.Data.SqlClient.SqlException e)
        {
            return null;
        }
    }
}

Points of Interest

Some parts of the code still needs some improvement. At the same time, it seems to me that this application can be greatly improved to be served for multiple databases as well.