Click here to Skip to main content
Click here to Skip to main content

Automated Class Builder for Database Tables

, 11 Nov 2004 CPOL
Rate this:
Please Sign up or sign in to vote.
This will create a set of classes that generate a set of C# class files which map into the database tables. They will have the class name same as the table name and they will have a set of properties that are same as the table attributes.

Sample Image - ModelCreator.jpg

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Nirosh
Architect MAS Holdings
Sri Lanka Sri Lanka
In-depth coverage of Microsoft .Net, Cloud and many other cutting-edge Technologies.
 
- The Mandelbrot set – someone has called it the thumb-print of God – is one of the most beautiful and remarkable discoveries in the entire history of mathematics. My profile picture is generated with that equation.
 
You may contact Nirosh for Consultations, Code Reviews and Architecture Guide Workshops via c_nir*o*sh@hotmail.com (Remove * to use)
 

View Nirosh L.W.C.'s profile on LinkedIn
Follow on   Twitter   Google+

Comments and Discussions

 
QuestionGood Work PinmemberZubair Masoodi24-Feb-12 4:48 
GeneralThanks PinmemberNitin Sawant8-Oct-09 21:13 
GeneralSQL Management Studio PinmemberDerek Bartram1-May-08 3:11 
GeneralRe: SQL Management Studio PinmemberNirosh1-May-08 17:05 
GeneralRe: SQL Management Studio PinmemberDerek Bartram3-May-08 3:14 
GeneralRe: SQL Management Studio [modified] PinmemberNirosh3-May-08 6:26 
GeneralRe: SQL Management Studio PinmemberDerek Bartram3-May-08 8:00 
GeneralGetData StoredProcedure Pinmembergaurav8424-Jun-07 22:49 
GeneralRe: GetData StoredProcedure PinmemberNirosh25-Jun-07 16:17 
QuestionRe: GetData StoredProcedure Pinmemberfantasy121521-Aug-08 16:31 
i create the procedure in the pubs database,
and i run the program using pubs, sa, and my ip in the textbox.
But nothing happens, or error prompt!
AnswerRe: GetData StoredProcedure PinmemberNirosh22-Aug-08 4:15 
GeneralThis is not a good example of well written code PinmemberLuciano Bargmann9-Jun-07 17:03 
GeneralRe: This is not a good example of well written code PinmemberNirosh10-Jun-07 17:42 
GeneralRe: This is not a good example of well written code PinmemberLuciano Bargmann14-Jun-07 3:51 
GeneralSmall bug - Last class file doesn't get saved properly Pinmemberfuzzy2logic28-May-07 13:25 
GeneralRe: Small bug - Last class file doesn't get saved properly PinmemberNirosh28-May-07 13:28 
GeneralRoutine skips 1st field of every table PinmemberDanShoop20-Feb-07 6:10 
GeneralRe: Routine skips 1st field of every table PinmemberNirosh20-Feb-07 18:05 
Questionis getData DB-generic ? PinmemberFrank Olorin Rizzi12-Nov-04 3:45 
AnswerRe: is getData DB-generic ? PinmemberGfw13-Nov-04 10:28 
GeneralRe: is getData DB-generic ? PinmemberNirosh14-Nov-04 18:58 
AnswerRe: is getData DB-generic ? PinmemberNirosh14-Nov-04 17:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.1411023.1 | Last Updated 12 Nov 2004
Article Copyright 2004 by Nirosh
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid