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:
- The server name or the IP (Internet Protocol) address of the machine you have the MS SQL Server running.
- The name of the database.
- Super user credential, i.e., user name and password to login to the database server.
- Adding "Namespace" property is currently not active.
- Process:
- Click button named "Connect and Get Data".
- Click button named "Creates Model Classes".
- Output of the application:
- 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
{
sbMapClass = new System.Text.StringBuilder(tb.Name);
sbMapClass.Append(".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());
}
catch (System.UnauthorizedAccessException ev)
{
MessageBox.Show(ev.Message);
}
catch (System.IO.IOException ev)
{
MessageBox.Show(ev.Message);
}
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
{
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;
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()
{
}
public bool InsertSale(Sale tobjSale)
{
if(tobjSale != null)
{
SqlParameter[] lParamArray = GetParameters(tobjSale);
SetParameters(lParamArray, tobjSale);
SqlConnection con = GetConnection(SQL_CONN_STRING);
if (con == null)
return false;
int i = SqlHelper.ExecuteNonQuery(
con,
CommandType.Text,
SQL_INSERT_Sale,
lParamArray);
con.Dispose();
if (i ==1)
return true;
else
return false;
}
else
return false;
}
public Sale tcSelectSale(string tstrsaleid)
{
SqlConnection connection = null;
SqlParameter[] aParms =
new SqlParameter[]{
new SqlParameter(PARAM_saleid
, tstrsaleid)};
try
{
try
{
connection = GetConnection(SQL_CONN_STRING);
}
catch (System.Exception e)
{
return null;
}
SqlDataReader reader = SqlHelper.ExecuteReader(
connection
, CommandType.Text
, SQL_SELECT_Sale
, aParms);
while (reader.Read())
{
return new Sale(
reader.GetString(0),
reader.GetString(1));
}
reader.Close();
return null;
}
catch(Exception ex)
{
System.Diagnostics.Trace.WriteLine(ex.Message);
return null;
}
finally
{
if(connection != null)
connection.Dispose();
}
}
public string tcSelectdescription(string tstrsaleid)
{
SqlConnection connection = null;
SqlParameter[] aParms = new SqlParameter[]{
new SqlParameter(
PARAM_saleid
, tstrsaleid)};
try
{
try
{
connection = GetConnection(SQL_CONN_STRING);
}
catch (System.Exception e)
{
return string.Empty;
}
SqlDataReader reader = SqlHelper.ExecuteReader(
connection
, CommandType.Text
, SQL_SELECT_description
, aParms);
while (reader.Read())
{
return reader.GetString(0);
}
reader.Close();
return string.Empty;
}
catch(Exception ex)
{
System.Diagnostics.Trace.WriteLine(ex.Message);
return string.Empty;
}
finally
{
if(connection != null)
connection.Dispose();
}
}
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;
int i = SqlHelper.ExecuteNonQuery(
connection
, CommandType.Text
, SQL_DELETE_Sale
, aParms);
return true;
}
catch(Exception ex)
{
System.Diagnostics.Trace.WriteLine(ex.Message);
return false;
}
finally
{
if(connection != null)
connection.Dispose();
}
}
public void tcUpdateSale(Sale tobjSale)
{
SqlParameter[] aParms = GetParameters(tobjSale);
SetParameters(aParms, tobjSale);
using (SqlConnection conn = GetConnection(SQL_CONN_STRING))
{
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
SqlHelper.ExecuteNonQuery(
trans
, CommandType.Text
, SQL_UPDATE_Sale, aParms);
trans.Commit();
}
catch(System.Exception e)
{
trans.Rollback();
throw;
}
}
}
}
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))
{
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;
}
}
}
}
private SqlParameter[] GetParameters(Sale tobjSale)
{
SqlParameter[] objParamArray = SqlHelperParameterCache.GetCachedParameterSet(
SQL_CONN_STRING
, SQL_INSERT_Sale);
if (objParamArray == null)
{
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;
}
private void SetParameters(SqlParameter[] SaleParms,Sale tobjSale)
{
SaleParms[0].Value = tobjSale.saleid;
SaleParms[0].Value = tobjSale.description;
}
private SqlConnection GetConnection(string tstrConnectionString)
{
SqlConnection dbconnection = new SqlConnection(tstrConnectionString);
try
{
dbconnection.Open();
return dbconnection;
}
catch (System.InvalidOperationException e)
{
return null;
}
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.