Motivation
One common problem which arises when using SQL in Microsoft Application Data Block as well as in ADO and ADO.NET, is the absence of syntax checking. For example, if you pass a mistyped name of the stored procedure to the compiler - the error will be generated not at compile time but at run-time, thus making code developing more tiresome.
Introduction
This article will show how to auto generate Data Access Layer for your application using SQLDMO and Microsoft Application Data Block.
The SQLDMO object comes from the SQLDMO.dll that ships with SQL Server 2000. The DLL itself is a COM object and you must refer it from your .NET project as such.
The Data Access Block provides static methods located in the SqlHelper class that encapsulates the most common data access tasks performed with Microsoft SQL Server. It is very helpful but still does not eliminate hard coding of your stored procedures' names.
Using DataBlock :
DataSet oDs= SqlHelper.ExecuteDataset(ConnString,"GetClientInfo",ClientID);
Using DataBlock With Extender:
DataSet oDs= DataAccessLayer.GetClientInfo.ExecuteDataset(ConnString,ClientID);
This Windows application allows to generate complete Data Access Layer for your applications. Each stored procedure is represented as a separate class with static methods which are the same as static method of SQLHelper class of Data Block. When using Extended code generated by this tool, developer will have all stored procedures and their parameters in the Intellisense window.
Code Description
On the form load event, application reads from registry to get SQL Server information and all other Generator options to eliminate typing every time user wants to regenerate code. On form closing event, application saves information back to the registry.
private void frmMain_Load(object sender, System.EventArgs e)
{
cboModifier.Text=Settings.Get("MethodMod");
txtServer.Text = Settings.Get("Server");
txtDatabase.Text = Settings.Get("Database");
txtOutfile.Text = Settings.Get("Outfile");
txtNamespaceRoot.Text = Settings.Get("NamespaceRoot");
string s = Settings.Get("UseIntegratedSecurity");
cbIntegratedSecurity.Checked = bool.Parse( s == "" ? "false" : s );
txtUserid.Text = Settings.Get("Userid");
txtPassword.Text = Settings.Get("Password");
btnBrowse.Enabled=txtOutfile.Text.Trim().Length>0;
rbFilterYes.Checked=Settings.Get("Filtering").ToLower()=="true";
rbStartsWith.Checked=Settings.Get("FilterOption")=="StartsWith";
txtFilter.Text=Settings.Get("FilterExpression");
rbFilterNo.Checked=!rbFilterYes.Checked;
rbContains.Checked=!rbStartsWith.Checked;
}
private void frmMain_Closing(object sender, System.ComponentModel.CancelEventArgs e)
{
Settings.Set("MethodMod",cboModifier.Text);
Settings.Set("Server", txtServer.Text);
Settings.Set("Database", txtDatabase.Text);
Settings.Set("Outfile", txtOutfile.Text);
Settings.Set("NamespaceRoot", txtNamespaceRoot.Text);
Settings.Set("Userid", txtUserid.Text);
Settings.Set("Password", txtPassword.Text);
Settings.Set("UseIntegratedSecurity", cbIntegratedSecurity.Checked.ToString());
Settings.Set("Filtering",rbFilterYes.Checked.ToString());
Settings.Set("FilterOption",rbStartsWith.Checked?"StartsWith":"Contains");
Settings.Set("FilterExpression",txtFilter.Text.Trim());
}
Settings Class is just the helper class to work with registry. User can apply custom filter to generate classes for the stored procedures based on custom naming convention. After applying all settings and options user clicks Generate button to generate code.
private void btnGenerate_Click(object sender, System.EventArgs e)
{
try
{
this.Cursor=Cursors.WaitCursor;
Core.Generator objGenerator=new Core.Generator();
objGenerator.Generate(txtServer.Text, txtDatabase.Text, txtUserid.Text,
txtPassword.Text, txtOutfile.Text, txtNamespaceRoot.Text,
cboModifier.Text,rbFilterNo.Checked?
Core.FilterOption.None:rbStartsWith.Checked?
Core.FilterOption.StartsWith:Core.FilterOption.Contains,
txtFilter.Text.Trim() );
System.Diagnostics.Process.Start(txtOutfile.Text);
}
catch (Exception ex)
{
MessageBox.Show(this,ex.Message,"Error",MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
finally
{
this.Cursor=Cursors.Default;
}
}
Generator Class
SQLDMO used to connect to the database and define all non-system stored procedures.
using System;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.CodeDom;
using System.CodeDom.Compiler;
using Microsoft.CSharp;
using SQLDMO;
namespace DataBlockExtender.Core
{
public class Generator
{
public Generator()
{
}
internal void Generate (string serverName, string databaseName,
string userid, string password, string outputFileName,
string rootNamespace,string methodMod,
FilterOption filterOption,string filterExpression)
{
if (methodMod.Length==0) methodMod="public";
SQLServer server = new SQLServerClass();
if (userid == null || userid == "")
server.LoginSecure = true;
server.Connect(serverName, userid, password);
Database database = null;
foreach(SQLDMO.Database temp in server.Databases)
if (temp.Name.ToUpper() == databaseName.ToUpper())
{
database = temp;
break;
}
if (database == null)
throw new Exception(string.Format(
"Could not locate database {0} via SQLDMO.",
databaseName));
TextWriter tw =
new StreamWriter(new FileStream(outputFileName,
FileMode.Create));
IndentedTextWriter writer = new IndentedTextWriter(tw);
writer.WriteLine("/* Code Generated by Application DataBlock " +
"Extender Code Generator");
writer.WriteLine("/* Utillity created by Dmitry Shalimov to make " +
"Data Layer creation and reusabillity easier");
writer.WriteLine("/* For more information please " +
"visit DmitryConsulting.com");
writer.WriteLine("/* Software provided as is with no warranty");
writer.WriteLine("/* For questions, comments or to report a bug " +
"please email to info@dmitryConsulting.com");
writer.WriteLine("*/" );
writer.WriteLine("using System;");
writer.WriteLine("using System.Data;");
writer.WriteLine("using System.Data.SqlClient;");
writer.WriteLine("using Microsoft.ApplicationBlocks.Data;");
writer.WriteLine();
writer.WriteLine(string.Format("namespace {0}", rootNamespace));
OpenBrace(writer);
foreach (StoredProcedure sp in database.StoredProcedures)
{
if (sp.SystemObject == true)
continue;
StoredProcedureInfo proc = new StoredProcedureInfo(sp);
if (filterOption==FilterOption.Contains)
{
if( proc.CsName.ToUpper().IndexOf(filterExpression.ToUpper())>=0
||
proc.SqlName.ToUpper().IndexOf(filterExpression.ToUpper())>=0 )
{
GenerateCodeForProc(writer, proc,methodMod);
}
}
else if (filterOption==FilterOption.StartsWith)
{
if( proc.CsName.ToUpper().StartsWith(filterExpression.ToUpper())
|| proc.SqlName.ToUpper().StartsWith(filterExpression.ToUpper()))
{
GenerateCodeForProc(writer, proc,methodMod);
}
}
else {
GenerateCodeForProc(writer, proc,methodMod);
}
}
CloseBrace(writer);
writer.Close();
tw.Close();
writer=null;
tw=null;
server.Close();
}
private string ParameterListAndRightBracket(StoredProcedureInfo proc,
bool AddDataType)
{
if (proc.Parameters.Length==0) return " )";
int i=0;
int intParmNumber=proc.Parameters.Length-1;
string strRet=", ";
foreach (StoredProcedureInfo.ParameterInfo p in proc.Parameters)
{
strRet+= (AddDataType? p.CsDataType:"") + " " +
p.CsName + ((i++<intParmNumber)? ", ": " )");
}
return strRet.Replace(", ,",",");
}
public void GenerateCodeForProc (IndentedTextWriter writer,
StoredProcedureInfo proc,string methodMod)
{
string[] strCommandsDeclaration=
{
methodMod + " static DataSet ExecuteDataset(string strConnString ",
methodMod + " static DataSet ExecuteDataset(SqlTransaction tx ",
methodMod + " static int ExecuteNonQuery(string strConnString ",
methodMod + " static int ExecuteNonQuery(SqlTransaction tx ",
methodMod + " static SqlDataReader ExecuteReader(string strConnString ",
methodMod + " static SqlDataReader ExecuteReader(SqlTransaction tx ",
methodMod + " static object ExecuteScalar(string strConnString ",
methodMod + " static object ExecuteScalar(SqlTransaction tx "
};
string[] strCommandsReturn=
{
"return SqlHelper.ExecuteDataset(strConnString , ",
"return SqlHelper.ExecuteDataset(tx , ",
"return SqlHelper.ExecuteNonQuery(strConnString , ",
"return SqlHelper.ExecuteNonQuery(tx , ",
"return SqlHelper.ExecuteReader(strConnString , ",
"return SqlHelper.ExecuteReader(tx , ",
"return SqlHelper.ExecuteScalar(strConnString , ",
"return SqlHelper.ExecuteScalar(tx , "
};
writer.WriteLine();
writer.WriteLine();
writer.WriteLine(string.Format ("# region {0}", proc.CsName));
writer.WriteLine(string.Format(methodMod + " class {0} ", proc.CsName));
OpenBrace(writer);
for (int j=0;j<=strCommandsDeclaration.Length-1;j++)
{
writer.WriteLine(strCommandsDeclaration[j]
+ ParameterListAndRightBracket(proc,true));
OpenBrace(writer);
writer.WriteLine("try");
OpenBrace(writer);
writer.WriteLine(strCommandsReturn[j] +
"\"" + proc.SqlName +"\" " +
ParameterListAndRightBracket(proc,false) + ";" );
CloseBrace(writer);
writer.WriteLine("catch (Exception ex)");
OpenBrace(writer);
writer.WriteLine("//ToDo : Handle Exception");
writer.WriteLine("string strEx= ex.Message;");
writer.WriteLine("strEx+=\"\";");
writer.WriteLine("return " +
((strCommandsDeclaration[j].IndexOf("static int")>=0)?"-1;":"null;"));
CloseBrace(writer);
CloseBrace(writer);
}
CloseBrace(writer);
writer.WriteLine("# endregion");
}
Example of generated Code from NorthWind Database:
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
namespace test
{
# region CustOrderHist
public class CustOrderHist
{
public static DataSet ExecuteDataset(string strConnString ,
string CustomerID )
{
try
{
return SqlHelper.ExecuteDataset(strConnString , "CustOrderHist" ,
CustomerID );
}
catch (Exception ex)
{
string strEx= ex.Message;
strEx+="";
return null;
}
}
public static DataSet ExecuteDataset(SqlTransaction tx , string CustomerID )
{
try
{
return SqlHelper.ExecuteDataset(tx , "CustOrderHist" , CustomerID );
}
catch (Exception ex)
{
string strEx= ex.Message;
strEx+="";
return null;
}
}
public static int ExecuteNonQuery(string strConnString , string CustomerID )
{
try
{
return SqlHelper.ExecuteNonQuery(strConnString ,
"CustOrderHist" , CustomerID );
}
catch (Exception ex)
{
string strEx= ex.Message;
strEx+="";
return -1;
}
}
public static int ExecuteNonQuery(SqlTransaction tx , string CustomerID )
{
try
{
return SqlHelper.ExecuteNonQuery(tx , "CustOrderHist" , CustomerID );
}
catch (Exception ex)
{
string strEx= ex.Message;
strEx+="";
return -1;
}
}
public static SqlDataReader ExecuteReader(string strConnString ,
string CustomerID )
{
try
{
return SqlHelper.ExecuteReader(strConnString ,
"CustOrderHist" , CustomerID );
}
catch (Exception ex)
{
string strEx= ex.Message;
strEx+="";
return null;
}
}
public static SqlDataReader ExecuteReader(SqlTransaction tx ,
string CustomerID )
{
try
{
return SqlHelper.ExecuteReader(tx , "CustOrderHist" , CustomerID );
}
catch (Exception ex)
{
string strEx= ex.Message;
strEx+="";
return null;
}
}
public static object ExecuteScalar(string strConnString , string CustomerID )
{
try
{
return SqlHelper.ExecuteScalar(strConnString ,
"CustOrderHist" , CustomerID );
}
catch (Exception ex)
{
string strEx= ex.Message;
strEx+="";
return null;
}
}
public static object ExecuteScalar(SqlTransaction tx , string CustomerID )
{
try
{
return SqlHelper.ExecuteScalar(tx , "CustOrderHist" , CustomerID );
}
catch (Exception ex)
{
string strEx= ex.Message;
strEx+="";
return null;
}
}
}
# endregion
}
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin