Automated Class Builder for Database Tables

• Download source files - 8.64 Kb
• Download demo project - 13.9 Kb

Introduction

Today, the application development process has vastly expanded. There are enough number of processes around that helps Rapid Application Development (RAD). But we all know that, for a project, which time is of utmost importance. None of these approaches suit well. At that instance, all of us, doesn’t really matter whether we like it or not, go for an ad-hoc approach. You change most of the definitions and define them to suite your current need. I am not trying to prove that it is the best way to go about, but when something has to be done fast fighting with the time, I feel that the ad-hoc approach suits well. But it is even better if we have some automated process within our application development life cycle, or in other words, it is good to have a sub application written to develop some part of our application automatically so that it matches both speed and efficiency.

This article is trying to find a fast way to develop database model classes set automatically using a sub application that generates code automatically/ dynamically. As the initial approach, I will create this application that generates a set of CS 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.

As this figure illustrates, the application creates model classes that map with all the tables of the database. It creates get/ set methods for the property as well as user defined constructor for each class so that we can use the user defined constructor to pass attributes (set properties) to the class as it is being created.

Background

As I explained a while ago, some times, we develop applications in defined, standard ways, which is the better way to go. But still there are many instances, at least I have enough previous experiences that happened to change the database after fully or partially developing the application. Then, as you change the database, as a parallel process, you got to change the model classes (if you are following the MVC architecture), in other words, the class sets that handle the database manipulation and operations. The next version of this application will introduce 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. As the initial approach, I will focus on developing an application that can generate a set of C# class files which map with the database tables.

Requirements

The users who read/ test this article need to have some understanding of C# Windows Forms application development, MS SQL server and stored procedures. Additionally, they are expected to have the Visual Studio .NET IDE and MS SQL server 7 or higher installed in their machine.

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, or you need to talk to your friendly network admin to create a test database for you.

Using the code

In order to test this application, you have to have a valid database server IP address, a simple database, and the administrator user credential to connect to the database. Obviously, you got to create a database with a set of tables in it. In my case, I have the database in my database server with the name “PRD-01”. In that server, I have created a database called “ManGoDB”, and I used the super user “sa” to log in to my database.

Entry to the Application

The application is at its very basic stage and it has one main class with the name “ModelCreator.cs” that does all the key operations. Entry point to this application is the “Connect and Create” button click event. It will fire a “CreateConnectionString()” method, which basically gets the input from the user and dynamically creates the connection string.

private void lbtnConnect_Click(object sender, System.EventArgs e)
{
  if (CreateConnectionString())
    CreateModelClassFiles(tcGetDataReader());
}

Once that process is done, the application calls the method named tcGetDataReader():

/// <summary>
/// Get the SqlDataReader object
/// SqlDataReader
/// </summary>
public SqlDataReader tcGetDataReader()
{
  SqlConnection connection = null;
  try
  {
    connection = GetConnection(SQL_CONN_STRING);
    if (connection == null)
      return null;
    SqlDataReader dr = SqlHelper.ExecuteReader(
             connection,
             CommandType.StoredProcedure,
             "getData");
    if (dr.HasRows)
      return dr;
    else
      return null;
  }
  catch(Exception ex)
  {
    MessageBox.Show(ex.Message);
    return null;
  }
}

Get Table Names, Attributes and their types from the database

One of the more important sections of this article is the "getData" stored procedure. It retrieves the table names, their attributes as well as the data types of these attributes in a table. The stored procedure reads as follows:

CREATE PROCEDURE getData AS 
 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
GO

Main Method, CreateModelClassFiles

/// <summary>
/// Create the Model class list iterating through the tables
/// </summary>
/// <param name="dr">Sql Data reader for the database schema</param>
private void CreateModelClassFiles(SqlDataReader dr)
{
  if (dr != null)
  {
    string lstrOldTableName = string.Empty;
    StreamWriter sw = null;
    System.Text.StringBuilder sb = null;
    System.Text.StringBuilder sbAttr = null;
    while(dr.Read())
    {
      string lstrTableName = dr.GetString(0);
      string lstrAttributeName = dr.GetString(1);
      string lstrAttributeType = GetSystemType(dr.GetString(2));
      if (lstrOldTableName != lstrTableName)
      {
        if (sw != null)
        {
          this.CreateClassBottom(sw, sb.ToString().TrimEnd(
                     new char[]{',', ' ', '\r', '\t', '\n'}),
                     sbAttr.ToString());
            sw.Close();
        }
        sb = new System.Text.StringBuilder(lstrTableName);
        sb.Append(".cs");
        FileInfo lobjFileInfo = new FileInfo(sb.ToString());
        sw = lobjFileInfo.CreateText();
        this.CreateClassTop(sw, lstrTableName);
        sb = new System.Text.StringBuilder("\r\n\t/// \r\n\t" + 
             "/// User defined Contructor\r\n\t/// \r\n\tpublic ");
        sbAttr = new System.Text.StringBuilder();
        sb.Append(lstrTableName);
        sb.Append("(");
      }
      else
      {
        this.CreateClassBody(sw, lstrAttributeType, lstrAttributeName);
        sb.AppendFormat("{0} {1}, \r\n\t\t", 
           new object[]{lstrAttributeType, lstrAttributeName});
        sbAttr.AppendFormat("\r\n\t\tthis._{0} = {0};", 
           new object[]{lstrAttributeName});
      }
      lstrOldTableName = lstrTableName;
      this.progressBarMain.Increment(1); 
    }
    MessageBox.Show("Done !!");
  }
}

Once this method is called, it does every thing for you.

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 very effective when the database schema is changing at a rapid rate.