Introduction
Ever had to do a project, and had to create and recreate your classes for your database tables? Well I have been there. As frustrating as it is, I decided to create my own web based tool which will generate the c# classes. I could either type the table name, select it from a drop down list, or generate for all tables of a database. Since the connection string is in the web config file, I can easily change it to point to which ever project I am working on.
System Minimum Requirements
Visual Studio 2003
Framework 1.1
SQL/ORACLE/DB Connection String (Insert into Web Config)
Why Should I Use This?
The real reason why I have even releases this is because it can save developers a lot time. I know in the real world, the database structures that are made are never the final structures. Lots of normalization occurs as well as adding or removing fields. Sometimes the data types change. I had a project were a date field had te become a nvarchar field to accomadate for multilanguage dates. Any how, I hope it will be helpful to others.
Modifications I Hope To Make
I am going to create a method to handle the connections. I personally don't like to use datareaders, but if you do, make sure you close your connections. If I release some new versions I will definately update it here.
Screen Shots
1. Default Page:
Here you will see all your tables in the drop down list. There are 3 tasks that can be perfomed here.
A. Type in the table/view name, to generate a class for it.
B. Select the table/view name, to generate a class for it.
C. Select the checkbox, to go generate all the classes for your database for all tables.
2. Example of typing in the table name:
Just type in the table name, and click [Generate]. Wallah! Your class is instantly created.
3. Example of selecting the checkbox to generate all tables.
4. Result of clicking generate from clicking from drop down list, or checkbox. (Note: Messages for be more if checkbox is selected based on number of tables)
5. Where to see your new class:
Make sure you refresh your project. The default directory is within the project.
6. Sample screenshot of class created.
Now to the good part...
THE CODE
Ever had to do a project, and had to create and recreate your classes for your database tables? Well I have been there. As frustrating as it is, I decided to create my own web based tool which will generate the c# classes. I could either type the table name, select it from a drop down list, or generate for all tables of a database. Since the connection string is in the web config file, I can easily change it to point to which ever project I am working on.
System Minimum Requirements
Visual Studio 2003
Framework 1.1
SQL/ORACLE/DB Connection String (Insert into Web Config)
Why Should I Use This?
The real reason why I have even releases this is because it can save developers a lot time. I know in the real world, the database structures that are made are never the final structures. Lots of normalization occurs as well as adding or removing fields. Sometimes the data types change. I had a project were a date field had te become a nvarchar field to accomadate for multilanguage dates. Any how, I hope it will be helpful to others.
Modifications I Hope To Make
I am going to create a method to handle the connections. I personally don't like to use datareaders, but if you do, make sure you close your connections. If I release some new versions I will definately update it here.
Screen Shots
1. Default Page:
Here you will see all your tables in the drop down list. There are 3 tasks that can be perfomed here.
A. Type in the table/view name, to generate a class for it.
B. Select the table/view name, to generate a class for it.
C. Select the checkbox, to go generate all the classes for your database for all tables.
2. Example of typing in the table name:
Just type in the table name, and click [Generate]. Wallah! Your class is instantly created.
3. Example of selecting the checkbox to generate all tables.
4. Result of clicking generate from clicking from drop down list, or checkbox. (Note: Messages for be more if checkbox is selected based on number of tables)
5. Where to see your new class:
Make sure you refresh your project. The default directory is within the project.
6. Sample screenshot of class created.
Now to the good part...
THE CODE
protected void Button1_Click(object sender, EventArgs e)
{
if (txtTableName.Text.Length > 0)
{
//Creates a file of type cs to generate a class with methods.
{
if (txtTableName.Text.Length > 0)
{
//Creates a file of type cs to generate a class with methods.
string TableName = txtTableName.Text.Trim().Replace(" ", "");
string TopBody = "using System; \nusing System.Data; \nusing System.Configuration; \nusing System.Web; \nusing System.Web.Security; \nusing System.Web.UI; \nusing System.Web.UI.WebControls;\nusing System.Web.UI.HtmlControls;\nusing ALLFunc;";
string TopBody = "using System; \nusing System.Data; \nusing System.Configuration; \nusing System.Web; \nusing System.Web.Security; \nusing System.Web.UI; \nusing System.Web.UI.WebControls;\nusing System.Web.UI.HtmlControls;\nusing ALLFunc;";
string NameSpaceStart = "\nnamespace DB \n{";
string MainClassStart = "\n\tpublic class " + TableName + "\n\t{ ";
string FunctionBODY = GenerateInsertStatements(TableName) + " \n\t\t\treturn \"\"; \n\t\t} ";
FunctionBODY += GenerateStringInsertStatements(TableName) + " \n\t\t\treturn \"\"; \n\t\t} ";
FunctionBODY += GenerateUpdateStatements(TableName) + " \n\t\t\treturn \"\"; \n\t\t} ";
FunctionBODY += GenerateStringUpdateStatements(TableName) + " \n\t\t\treturn \"\"; \n\t\t} ";
FunctionBODY += GenerateSelectStatementsSqlDataReader(TableName) + " \n\t\t}";
FunctionBODY += GenerateSelectStatementsDataSet(TableName) + " \n\t\t}";
FunctionBODY += GenerateDeleteStatements(TableName) + " \n\t\t}";
FunctionBODY += GenerateUpdateStatementsWithParamatersOnly(TableName) + " \n\t\t}";
string MainClassEnd = "\n\t}";
string NameSpaceEnd = "\n}";
string Path = Server.MapPath("..") + "\\Class\\ClassInfo\\" + TableName + ".cs";
System.IO.StreamWriter SW;
SW = System.IO.File.CreateText(Path);
SW = System.IO.File.CreateText(Path);
SW.WriteLine(TopBody + NameSpaceStart + MainClassStart + FunctionBODY + MainClassEnd + NameSpaceEnd);
SW.Close();
Msg.Text = "Class created for " + TableName + " at path: " + Path;
}
}
SW.Close();
Msg.Text = "Class created for " + TableName + " at path: " + Path;
}
}
This is not it though. I have a bunch of other methods which you can see in the middle which creates the body of the *.cs files. If you want it in visual basic (vb) make sure you change the extensions. Here are a list of the other methods I call:
GenerateInsertStatements(TableName)
GenerateStringInsertStatements(TableName)
GenerateUpdateStatements(TableName)
GenerateStringUpdateStatements(TableName)
GenerateSelectStatementsSqlDataReader(TableName)
GenerateSelectStatementsDataSet(TableName)
GenerateDeleteStatements(TableName)
GenerateUpdateStatementsWithParamatersOnly(TableName)
They are pretty straight forward in the method name. The method names identity the scripts it is going to generates.
