Download demo project - 50 Kb
Download source With NETXP - 1269 Kb
Download source Without NETXP - 553 Kb
Download sample Application - 309 Kb
Introduction
Every one has shortage of time , every one wants short cuts. Why don't we have (being developers) short cuts to developing database applications. This is what Dot NET Code Generator tries to give you. It generates full object oriented source code and even all the forms for you using 3 layer architechture .
Background
When I started development, changing customer requirements caused me a lot of trouble.
Whenever a major change occurred I’ve to do a lot of coding to accommodate that change .After being worried for some time I decided to automate the coding process, so that I Don’t have to write repetitive coding tasks again and again.
code
This Application is not developed using specific design in mind, it is developed using old style structured programming, but the code it generates is complete Object oriented which takes benefits of inheritance, polymorphism and encapsulation. All code is generated using 3 layer architecture. I’ve used meta data provided by SQL Server’s Master Database to Generate all the code . if you open master database’s sotred procedure section you’ll see a lot of stored procedures prefixed by sp_ these procedures provide you much information sufficient for our code generator .
I’ve used following stored procdures .
sp_databases<BR>sp_tables<BR>sp_Columns<BR>sp_primary_keys_Rowset<BR>sp_fkeys
sp_databases
returns all databases attached with current instance of Sql server all these stored procedures are self explanatory , I’ll use simple “Sample “ database (to explain the code) which only has two tables one tblMaster and other tblDetail .
Database is intentionally kept simple for simplicity.
Once we have our database we can use sp_tables
and pass our database name as argument this will return all the tables with in our database in our case tblMaster and tblDetail respectively we’ll store these tables temporarily , so that we can loop through all the tables and generate the code . Following Code snippet shows you what actually happens .
<P><FONT color=#0000ff>for</FONT>(<FONT color=#0000ff>int</FONT> i=0 ; i < lstSelectedTables.Length ; i++)
{
<FONT color=#0000ff>string</FONT> tblName = lstSelectedTables[i][2].ToString();
<FONT color=#0000ff> <FONT color=#0000ff>if</FONT></FONT>(cbolanguage.Text == "CSharp")
GenerateCSharpCode(tblName);
<FONT color=#0000ff> <FONT color=#0000ff>else</FONT></FONT>
GenerateVBCode(tblName);
<FONT color=#0000ff> <FONT color=#0000ff>if</FONT></FONT>((chkgui.Checked ==<FONT color=#000000><FONT color=#0000ff>false</FONT></FONT>) && (chkdata.Checked ==<FONT color=#0000ff>false</FONT>)&& (chkinterface.Checked
==<FONT color=#0000ff>false</FONT>)&& (chkprocedures.Checked ==<FONT color=#0000ff>false</FONT>))
{
MessageBox.Show("Please select at least one code option");
<FONT color=#0000ff>break</FONT>;
}
}</P>
<P><FONT color=#0000ff>public void</FONT> GenerateDataClasses(string tblName,string tbldetail)
{
<FONT color=#000000>FileStream</FONT> file ;
file = <FONT color=#0000ff>new</FONT> FileStream(tempPath + "\\cls_d" + tblName+ ".cs",System.IO.FileMode.Create);
StreamWriter sw = <FONT color=#0000ff>new</FONT> StreamWriter(file);
sw.WriteLine("using System;\n");
sw.WriteLine("namespace " + txtprojectname.Text + ".DataClasses"
);
sw.WriteLine("{");
sw.WriteLine("\t/// <summary>");
sw.WriteLine("\t/// Summary description for cls_d" + tblName+ ".");
sw.WriteLine("\t/// </summary>\n");
sw.WriteLine("\tpublic class cls_d" + tblName);
sw.WriteLine("\t{");
GenerateAttributes(tblName,sw,tbldetail);
sw.WriteLine("\t}");
sw.WriteLine("}");
sw.Flush();
file.Close();
txtprogress.AppendText("cls_d" + tblName+ ".cs Created \n")
;</P>
<P> }
<FONT color=#0000ff>public void</FONT> GenerateAttributes(string TableName , StreamWriter sw,string tbldetail)
{
<FONT color=#0000ff>try</FONT>
{
<FONT color=#0000ff>string</FONT> type;
DataTable dtColumns ;
dbobj = new dboperation(constr);
dbobj.objcmd.CommandText = "exec sp_Columns " + TableName ;
dtColumns = dbobj.filldata().Tables[0];
<FONT color=#0000ff>for</FONT>(<FONT color=#0000ff>int</FONT> i = 0 ; i < dtColumns.Rows.Count ; i ++ )
{
type = GetType(dtColumns.Rows[i][5].ToString());
sw.WriteLine("\t\tprivate " + type + " m" + dtColumns.Rows[i][3].ToString()
+ ";");
}
<FONT color=#0000ff> <FONT color=#0000ff>if</FONT></FONT>(tbldetail != "")
sw.WriteLine("\t\tprivate string mDetail;");
sw.WriteLine("\n\t\t# <FONT color=#0000ff>region</FONT> Constructor");
GenerateConstructor(dtColumns,sw,tbldetail);
sw.WriteLine("\t\t#end <FONT color=#0000ff>region</FONT>\n");
sw.WriteLine("\t\t# <FONT color=#0000ff>region</FONT> Properties");
GenerateProperties(dtColumns,sw,tbldetail);
sw.WriteLine("\t\t#end <FONT color=#0000ff>region</FONT>\n");</P>
<P> }
<FONT color=#0000ff> <FONT color=#0000ff>catch</FONT></FONT>(Exception ex)
{
MessageBox.Show(ex.Message);
}</P>
<P> }
<FONT color=#0000ff>public void</FONT> GenerateConstructor(DataTable dtColumns,StreamWriter sw,string tbldetail)
{
<FONT color=#0000ff>string</FONT> tblname = dtColumns.Rows[0][2].ToString();
sw.WriteLine("\n\t\tpublic cls_d" + tblname + "()");
sw.WriteLine("\t\t{");
sw.WriteLine("\t\t\t //");
sw.WriteLine("\t\t\t // TODO: Add constructor logic here");
sw.WriteLine("\t\t\t //");
<FONT color=#0000ff>for</FONT>(<FONT color=#0000ff>int</FONT> i = 0 ; i < dtColumns.Rows.Count ; i++ )
{
<FONT color=#0000ff> <FONT color=#0000ff>if</FONT></FONT>(GetInitValue(dtColumns.Rows[i][5].ToString()) != "")
sw.WriteLine("\t\t\t" + dtColumns.Rows[i][3].ToString() + " =
" + GetInitValue(dtColumns.Rows[i][5].ToString()) + ";" );
<FONT color=#0000ff> <FONT color=#0000ff>else</FONT></FONT>
sw.WriteLine("\t\t\t" + dtColumns.Rows[i][3].ToString() + " =
\"\" ;" );</P>
<P> }
<FONT color=#0000ff> <FONT color=#0000ff>if</FONT></FONT>(tbldetail != "")
sw.WriteLine("\t\t\tmDetail = \"\" ;" );
sw.WriteLine("\t\t}");</P>
<P> }</P>
<P> <FONT color=#0000ff>public void</FONT> GenerateProperties(DataTable dtColumns,StreamWriter sw,string
tbldetail)
{
<FONT color=#0000ff> string</FONT> type;
<FONT color=#0000ff>for</FONT>(<FONT color=#0000ff>int</FONT> i = 0 ; i < dtColumns.Rows.Count ; i ++ )
{
type = GetType(dtColumns.Rows[i][5].ToString());
sw.WriteLine("\t\tpublic " + type + " " + dtColumns.Rows[i][3].ToString()
);
sw.WriteLine("\t\t{");
sw.WriteLine("\t\t\tget");
sw.WriteLine("\t\t\t{");
sw.WriteLine("\t\t\t\t <FONT color=#0000ff>return</FONT> m" + dtColumns.Rows[i][3].ToString()
+ ";");
sw.WriteLine("\t\t\t}");
sw.WriteLine("\t\t\tset");
sw.WriteLine("\t\t\t{");
sw.WriteLine("\t\t\t\tm" + dtColumns.Rows[i][3].ToString() + "
= value ;");
sw.WriteLine("\t\t\t}");
sw.WriteLine("\t\t}");
}
<FONT color=#0000ff> <FONT color=#0000ff>if</FONT></FONT>(tbldetail != "")
{
sw.WriteLine("\t\tpublic string Detail" );
sw.WriteLine("\t\t{");
sw.WriteLine("\t\t\tget");
sw.WriteLine("\t\t\t{");
sw.WriteLine("\t\t\t\t <FONT color=#0000ff>return</FONT> mDetail;");
sw.WriteLine("\t\t\t}");
sw.WriteLine("\t\t\tset");
sw.WriteLine("\t\t\t{");
sw.WriteLine("\t\t\t\tmDetail = value ;");
sw.WriteLine("\t\t\t}");
sw.WriteLine("\t\t}");
}</P>
<P>
}</P>
<P></P>
Generated Code
Here is the sample code generated by our code generator.
using System;<P></P>
<P>namespace Sample.DataClasses
{
<P> public class cls_dtblDetail
{
private int mDetailID;
private int mMasterID;
private string mDetailFieldOne;
private DateTime mDetailFieldTwo;
private string mDetailFieldThree;</P>
<P> # <FONT color=#0000ff>region</FONT> Constructor</P>
<P> public cls_dtblDetail()
{
DetailID = 0;
MasterID = 0;
DetailFieldOne = "" ;
DetailFieldTwo = System.DateTime.Now.Date;
DetailFieldThree = "" ;
}
#end <FONT color=#0000ff>region</FONT></P>
<P> # <FONT color=#0000ff>region</FONT> Properties
public int DetailID
{
get
{
<FONT color=#0000ff>return</FONT> mDetailID;
}
set
{
mDetailID = value ;
}
}
public int MasterID
{
get
{
<FONT color=#0000ff>return</FONT> mMasterID;
}
set
{
mMasterID = value ;
}
}
public string DetailFieldOne
{
get
{
<FONT color=#0000ff>return</FONT> mDetailFieldOne;
}
set
{
mDetailFieldOne = value ;
}
}
public DateTime DetailFieldTwo
{
get
{
<FONT color=#0000ff>return</FONT> mDetailFieldTwo;
}
set
{
mDetailFieldTwo = value ;
}
}
public string DetailFieldThree
{
get
{
<FONT color=#0000ff>return</FONT> mDetailFieldThree;
}
set
{
mDetailFieldThree = value ;
}
}
#end <FONT color=#0000ff>region</FONT></P>
<P> }
}</P>
<P>Interface class is as follow.</P>
<P>using System;
using System.Data;
using System.Data.SqlClient;
using Sample.DataClasses;
using Sample.Utility;</P>
<P>
namespace Sample.Interface
{
public class cls_itblDetail
{</P>
<P> private dboperation dbobj ;
private cls_dtblDetail mData;
public string m_Error;</P>
<P> # <FONT color=#0000ff>region</FONT> Constructor
public cls_itblDetail()
{
mData = new cls_dtblDetail();
}
#end <FONT color=#0000ff>region</FONT></P>
<P> # <FONT color=#0000ff>region</FONT> Methods
public bool AddtblDetail(cls_dtblDetail pData)
{
try
{
mData = pData;
dbobj = new dboperation();
dbobj.objcmd.CommandType = CommandType.StoredProcedure;
dbobj.objcmd.CommandText = "addtblDetail" ;
dbobj.objcmd.Parameters.Add("@MasterID" , mData.MasterID);
dbobj.objcmd.Parameters.Add("@DetailFieldOne" , mData.DetailFieldOne);
dbobj.objcmd.Parameters.Add("@DetailFieldTwo" , mData.DetailFieldTwo);
dbobj.objcmd.Parameters.Add("@DetailFieldThree" , mData.DetailFieldThree);
dbobj.executeQuery();
}
<FONT color=#0000ff>catch</FONT>(Exception ex)
{
m_Error = ex.Message;
<FONT color=#0000ff>return</FONT> <FONT color=#0000ff>false</FONT>;
}
<FONT color=#0000ff>return</FONT> <FONT color=#0000ff>true</FONT>;
}
public bool UpdatetblDetail(cls_dtblDetail pData )
{
try
{
mData = pData;
dbobj = new dboperation();
dbobj.objcmd.CommandType = CommandType.StoredProcedure;
dbobj.objcmd.CommandText = "updatetblDetail" ;
dbobj.objcmd.Parameters.Add("@DetailID" , mData.DetailID);
dbobj.objcmd.Parameters.Add("@MasterID" , mData.MasterID);
dbobj.objcmd.Parameters.Add("@DetailFieldOne" , mData.DetailFieldOne);
dbobj.objcmd.Parameters.Add("@DetailFieldTwo" , mData.DetailFieldTwo);
dbobj.objcmd.Parameters.Add("@DetailFieldThree" , mData.DetailFieldThree);
dbobj.executeQuery();
}
<FONT color=#0000ff>catch</FONT>(Exception ex)
{
m_Error = ex.Message;
<FONT color=#0000ff>return</FONT> <FONT color=#0000ff>false</FONT>;
}
<FONT color=#0000ff>return</FONT> <FONT color=#0000ff>true</FONT>;
}
public bool DeletetblDetail(cls_dtblDetail pData)
{
try
{
mData = pData;
dbobj = new dboperation();
dbobj.objcmd.CommandType = CommandType.StoredProcedure;
dbobj.objcmd.CommandText = "deletetblDetail" ;
dbobj.objcmd.Parameters.Add("@DetailID" , mData.DetailID);
dbobj.executeQuery();
}
<FONT color=#0000ff>catch</FONT>(Exception ex)
{
m_Error = ex.Message;
<FONT color=#0000ff>return</FONT> <FONT color=#0000ff>false</FONT>;
}
<FONT color=#0000ff>return</FONT> <FONT color=#0000ff>true</FONT>;
}
public DataSet GettblDetail()
{
DataSet ds = new DataSet();
try
{
dbobj = new dboperation();
dbobj.objcmd.CommandType = CommandType.StoredProcedure;
dbobj.objcmd.CommandText = "gettblDetail" ;
ds = dbobj.filldata();
}
<FONT color=#0000ff>catch</FONT>(Exception ex)
{
m_Error = ex.Message;
<FONT color=#0000ff>return</FONT> null;
}
<FONT color=#0000ff>return</FONT> ds;
}
public DataSet GettblDetailThroughQuery(string qry)
{
DataSet ds = new DataSet();
try
{
dbobj = new dboperation();
dbobj.objcmd.CommandType = CommandType.Text;
dbobj.objcmd.CommandText = qry ;
ds = dbobj.filldata();
}
<FONT color=#0000ff>catch</FONT>(Exception ex)
{
m_Error = ex.Message;
<FONT color=#0000ff>return</FONT> null;
}
<FONT color=#0000ff>return</FONT> ds;
}
<FONT color=#0000ff>#endregion</FONT></P>
<P> }
}</P>
I’ve also included sample application which demonstrates the usage of generated code.
References
1 - John storer II’s xDirectory.Copy() - Copy Folder, SubFolders & Files article to copy validation controls to target folder of your code. This may not work under some conditions
If this happens(Validation Control's directory doesn't get created ) then manually copy Validation Controls Folder from Code Generator's main Directory to where you've stored all the generated code.
2 - NETXP components , if there is any problem regarding these components I’ve also provided Source code without these controls.
My name is Muhammad Shahbaz Saleem. I am an enterprise application developer and a strong advocate of re-usability and cross-platform development. I am also an admirer of interactive UI technologies and love to play with AJAX, CSS, JQuery etc. Since 2005, I have been involved in solving architectural problems and evolving complex requirements into simple and robust solutions for desktop, mobile and the Web.
As a person, I am opinionated and always love a good debate; I have a lot to say about software engineering, design patterns, service-oriented architecture and mobile computing.
You can email me at mshahbazsaleem[at]gmail[dot]com
OR
If you like to talk here is my s@k@y@p@e id m[dot]shahbaz[dot]saleem