Click here to Skip to main content
11,811,971 members (65,843 online)
Click here to Skip to main content

Automated Class Builder for Database Tables

, 11 Nov 2004 CPOL 114.8K 6.1K 76
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


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.


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.


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())

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;
    connection = GetConnection(SQL_CONN_STRING);
    if (connection == null)
      return null;
    SqlDataReader dr = SqlHelper.ExecuteReader(
    if (dr.HasRows)
      return dr;
      return null;
  catch(Exception ex)
    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:

 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

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;
      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'}),
        sb = new System.Text.StringBuilder(lstrTableName);
        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();
        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;
    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.


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


About the Author

Architect Virtusa Pvt. Ltd.
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* (Remove * to use)

View Nirosh L.W.C.'s profile on LinkedIn

You may also be interested in...

Comments and Discussions

QuestionNon sense programmer. Who allowed you on code project? Pin
sushilsmart19889-Mar-15 23:18
membersushilsmart19889-Mar-15 23:18 
AnswerRe: Non sense programmer. Who allowed you on code project? Pin
Nirosh10-Mar-15 1:02
professionalNirosh10-Mar-15 1:02 
QuestionGood Work Pin
Zubair Masoodi24-Feb-12 3:48
memberZubair Masoodi24-Feb-12 3:48 
GeneralThanks Pin
Nitin Sawant8-Oct-09 20:13
memberNitin Sawant8-Oct-09 20:13 
GeneralSQL Management Studio Pin
Derek Bartram1-May-08 2:11
memberDerek Bartram1-May-08 2:11 
GeneralRe: SQL Management Studio Pin
Nirosh1-May-08 16:05
memberNirosh1-May-08 16:05 
GeneralRe: SQL Management Studio Pin
Derek Bartram3-May-08 2:14
memberDerek Bartram3-May-08 2:14 
Nirosh wrote:
That book is taken it from me

What is that all about anyway..

Appologies, I had removed it anyway though. Looked very much like an article someone I know had posted.

Nirosh wrote:
Yes, SQL support this now, but it was not there by the time I thought/ write this.. Now I wanted to delete it but CP does not support deleting it.

Give them an email I guess
GeneralRe: SQL Management Studio [modified] Pin
Nirosh3-May-08 5:26
memberNirosh3-May-08 5:26 
GeneralRe: SQL Management Studio Pin
Derek Bartram3-May-08 7:00
memberDerek Bartram3-May-08 7:00 
GeneralGetData StoredProcedure Pin
gaurav8424-Jun-07 21:49
membergaurav8424-Jun-07 21:49 
GeneralRe: GetData StoredProcedure Pin
Nirosh25-Jun-07 15:17
memberNirosh25-Jun-07 15:17 
QuestionRe: GetData StoredProcedure Pin
fantasy121521-Aug-08 15:31
memberfantasy121521-Aug-08 15:31 
AnswerRe: GetData StoredProcedure Pin
Nirosh22-Aug-08 3:15
memberNirosh22-Aug-08 3:15 
GeneralThis is not a good example of well written code Pin
Luciano Bargmann9-Jun-07 16:03
memberLuciano Bargmann9-Jun-07 16:03 
GeneralRe: This is not a good example of well written code Pin
Nirosh10-Jun-07 16:42
memberNirosh10-Jun-07 16:42 
GeneralRe: This is not a good example of well written code Pin
Luciano Bargmann14-Jun-07 2:51
memberLuciano Bargmann14-Jun-07 2:51 
GeneralSmall bug - Last class file doesn't get saved properly Pin
fuzzy2logic28-May-07 12:25
memberfuzzy2logic28-May-07 12:25 
GeneralRe: Small bug - Last class file doesn't get saved properly Pin
Nirosh28-May-07 12:28
memberNirosh28-May-07 12:28 
GeneralRoutine skips 1st field of every table Pin
DanShoop20-Feb-07 5:10
memberDanShoop20-Feb-07 5:10 
GeneralRe: Routine skips 1st field of every table Pin
Nirosh20-Feb-07 17:05
memberNirosh20-Feb-07 17:05 
Questionis getData DB-generic ? Pin
Frank Olorin Rizzi12-Nov-04 2:45
memberFrank Olorin Rizzi12-Nov-04 2:45 
AnswerRe: is getData DB-generic ? Pin
Gfw13-Nov-04 9:28
memberGfw13-Nov-04 9:28 
GeneralRe: is getData DB-generic ? Pin
Nirosh14-Nov-04 17:58
memberNirosh14-Nov-04 17:58 
AnswerRe: is getData DB-generic ? Pin
Nirosh14-Nov-04 16:29
memberNirosh14-Nov-04 16: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
Web02 | 2.8.151002.1 | Last Updated 12 Nov 2004
Article Copyright 2004 by Nirosh
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid