Olymars, full name as SQL Server Centric .NET Code Generator, is a beta software by Microsoft, which can generate .NET code based on SQL Server database. By default, it has five build-in templates. However, developers can easily extend it to fit your needs. Here is a example to extend the templates, which can generate data access layer for application by using Microsoft Application Blocks.
To extend the templates:
1. Add a new groups in repository
2. Add a reference to System.dll in repository
3. Add a dynamic variable as CompanyName
4. Add reference of System.dll, System.Window.Forms.dll to your new group
5. Add dynamic variables as CompanyName and DeveloperAlias to your new group
6. Add three elements to your new group.
7. Edit the templates.
[Code of Templates]
<*
// ----------------------------------------------------------------------------------------------------
// GetName function for element: 'Data Access Layer Interface'
// ----------------------------------------------------------------------------------------------------
public string IDataAccessLayer_GetName() {
// Choose whatever name you wish for this object
return (@"IDataAccessLayer.cs");
}
// ----------------------------------------------------------------------------------------------------
// Creation method for element: 'Data Access Layer Interface'
// ----------------------------------------------------------------------------------------------------
public void IDataAccessLayer() {
try {
Source.CurrentTemplate.ExecutionStatusMessage = "Code Generation succeed. ";
Response.WriteLine("// File Name: " + IDataAccessLayer_GetName());
Response.WriteLine("// Code Generated By: " + DynamicParameters.DeveloperAlias);
Response.WriteLine("// Code Generated On: " + System.DateTime.Now.ToShortDateString());
Response.WriteLine();
Response.WriteLine("using System;");
Response.WriteLine("using System.Data;");
Response.WriteLine();
Response.WriteLine("namespace " + DynamicParameters.CompanyName + ".Data");
Response.WriteLine("{");
Response.WriteLine("\tpublic interface IDataAccessLayer");
Response.WriteLine("\t{");
Response.WriteLine("\t\tstring ConnectionString {get;}");
Response.WriteLine("\t\tbool WithTransaction {get;}");
StoredProcedureCollection Procedures = Source.SelectedStoredProcedures;
foreach (StoredProcedure SP in Procedures)
{
string ReturnType;
if (SP.ReturnsData == true)
{
if (SP.Resultsets.Count > 1)
ReturnType = "DataSet";
else
ReturnType = "DataTable";
}
else
{
ReturnType = "void";
}
ParameterCollection Parameters = SP.Parameters;
string ParameterString = "";
foreach (Parameter P in Parameters)
{
string Direction = "";
if (P.Direction == ParameterDirection.Output)
{
Direction = "out ";
}
else if (P.Direction == ParameterDirection.ReturnValue)
{
continue;
}
if (ParameterString != "")
{
ParameterString += ",";
}
string DotNetName = P.Name;
if (DotNetName.Substring(0,1) == "@")
{
DotNetName = DotNetName.Remove(0,1);
}
ParameterString += Direction + P.DotNetType.CommonTypeSystemEquivalent + " " + DotNetName;
}
Response.WriteLine("\t\t" + ReturnType + " " + SP.Name.Replace(" ", "_") + "(" + ParameterString + ");");
}
Response.WriteLine("\t}");
Response.WriteLine("}");
}
catch (System.Exception GlobalException) {
Source.CurrentTemplate.ExecutionStatusCode = GenerationStatus.Error;
Source.CurrentTemplate.ExecutionStatusMessage = GlobalException.Message;
Response.WriteLine();
Response.WriteLine("*************************");
Response.WriteLine(" AN EXCEPTION WAS THROWN");
Response.WriteLine("*************************");
Response.WriteLine(GlobalException.Message);
Response.WriteLine();
Response.WriteLine("****************");
Response.WriteLine(" FULL EXCEPTION");
Response.WriteLine("****************");
Response.WriteLine(GlobalException.ToString());
if (Source.DesignMode) {
System.Windows.Forms.MessageBox.Show(GlobalException.ToString(), "An error has occured", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
else {
throw;
}
}
}
// ----------------------------------------------------------------------------------------------------
// GetName function for element: 'Data Access Layer Basic Implementation'
// ----------------------------------------------------------------------------------------------------
public string DataAccessLayer_GetName() {
// Choose whatever name you wish for this object
return (@"DataAccessLayer.cs");
}
// ----------------------------------------------------------------------------------------------------
// Creation method for element: 'Data Access Layer Basic Implementation'
// ----------------------------------------------------------------------------------------------------
public void DataAccessLayer() {
try {
Source.CurrentTemplate.ExecutionStatusMessage = "Code Generation succeed. ";
Response.WriteLine("// File Name: " + DataAccessLayer_GetName());
Response.WriteLine("// Code Generated By: " + DynamicParameters.DeveloperAlias);
Response.WriteLine("// Code Generated On: " + System.DateTime.Now.ToShortDateString());
Response.WriteLine();
Response.WriteLine("using System;");
Response.WriteLine("using System.Data;");
Response.WriteLine("using System.Data.SqlClient;");
Response.WriteLine("using Microsoft.ApplicationBlocks.Data;");
Response.WriteLine("using " + DynamicParameters.CompanyName + ".Configure;");
Response.WriteLine();
Response.WriteLine("namespace " + DynamicParameters.CompanyName + ".Data");
Response.WriteLine("{");
Response.WriteLine("\tpublic class DataAccessLayer : IDataAccessLayer");
Response.WriteLine("\t{");
Response.WriteLine("\t\t// connection string of SQL database");
Response.WriteLine("\t\tprivate string _ConnectionString = \"\";");
Response.WriteLine("\t\t// transaction option");
Response.WriteLine("\t\tprivate bool _WithTransaction = false;");
Response.WriteLine("\t\t// connection");
Response.WriteLine("\t\tprivate SqlConnection _Connection = null;");
Response.WriteLine("\t\t// transaction");
Response.WriteLine("\t\tprivate SqlTransaction _Transaction = null;");
Response.WriteLine();
Response.WriteLine("\t\t// get connection string");
Response.WriteLine("\t\tpublic string ConnectionString");
Response.WriteLine("\t\t{");
Response.WriteLine("\t\t\tget {return _ConnectionString;}");
Response.WriteLine("\t\t}");
Response.WriteLine();
Response.WriteLine("\t\t// get or set transaction option");
Response.WriteLine("\t\tpublic bool WithTransaction");
Response.WriteLine("\t\t{");
Response.WriteLine("\t\t\tget {return _WithTransaction;}");
Response.WriteLine("\t\t}");
Response.WriteLine();
Response.WriteLine("\t\t// construction with connection string");
Response.WriteLine("\t\tpublic DataAccessLayer(string Connection)");
Response.WriteLine("\t\t{");
Response.WriteLine("\t\t\t_ConnectionString = Connection;");
Response.WriteLine("\t\t}");
Response.WriteLine();
Response.WriteLine("\t\t// construction with configure");
Response.WriteLine("\t\tpublic DataAccessLayer(IConfigure Config)");
Response.WriteLine("\t\t{");
Response.WriteLine("\t\t\t_ConnectionString = Config.GetConnectionString();");
Response.WriteLine("\t\t}");
Response.WriteLine();
Response.WriteLine("\t\t// construction with connection, caller will manage the connection");
Response.WriteLine("\t\tpublic DataAccessLayer(SqlConnection Connection)");
Response.WriteLine("\t\t{");
Response.WriteLine("\t\t\tif (Connection == null)");
Response.WriteLine("\t\t\t\tthrow new Exception(\"Connection cannot be null.\");");
Response.WriteLine("\t\t\t_Connection = Connection;");
Response.WriteLine("\t\t}");
Response.WriteLine();
Response.WriteLine("\t\t// construction with transaction, caller will manage the connection and transaction");
Response.WriteLine("\t\tpublic DataAccessLayer(SqlTransaction Transaction)");
Response.WriteLine("\t\t{");
Response.WriteLine("\t\t\tif (Transaction == null)");
Response.WriteLine("\t\t\t\tthrow new Exception(\"Transaction cannot be null.\");");
Response.WriteLine("\t\t\t_Connection = Transaction.Connection;");
Response.WriteLine("\t\t\t_ConnectionString = _Connection.ConnectionString;");
Response.WriteLine("\t\t\t_Transaction = Transaction;");
Response.WriteLine("\t\t\t_WithTransaction = true;");
Response.WriteLine("\t\t}");
Response.WriteLine();
StoredProcedureCollection Procedures = Source.SelectedStoredProcedures;
foreach (StoredProcedure SP in Procedures)
{
string ReturnType;
if (SP.ReturnsData == true)
{
if (SP.Resultsets.Count > 1)
ReturnType = "DataSet";
else
ReturnType = "DataTable";
}
else
{
ReturnType = "void";
}
ParameterCollection Parameters = SP.Parameters;
string ParameterString = "";
string SqlDBParameterString = "";
int TrueParameter = 0;
foreach (Parameter P in Parameters)
{
string Direction = "";
if (P.Direction == ParameterDirection.Output)
{
Direction = "out ";
}
else if (P.Direction == ParameterDirection.ReturnValue)
{
continue;
}
if (ParameterString != "")
{
ParameterString += ",";
SqlDBParameterString += ",";
}
string DotNetName = P.Name;
if (DotNetName.Substring(0,1) == "@")
{
DotNetName = DotNetName.Remove(0,1);
}
ParameterString += Direction + P.DotNetType.CommonTypeSystemEquivalent + " " + DotNetName;
SqlDBParameterString += P.Name + " " + P.SqlFullType;
TrueParameter ++;
}
Response.WriteLine("\t\t// Execute stored procedure:");
Response.WriteLine("\t\t// " + SP.Name + "(" + SqlDBParameterString + ")");
Response.WriteLine("\t\tpublic " + ReturnType + " " + SP.Name.Replace(" ", "_") + "(" + ParameterString + ")");
Response.WriteLine("\t\t{");
if (SP.ReturnsData == true)
Response.WriteLine("\t\t\tDataSet ds = null;");
Response.WriteLine();
Response.WriteLine("\t\t\ttry");
Response.WriteLine("\t\t\t{");
if (TrueParameter > 0)
{
Response.WriteLine("\t\t\t\tSqlParameter[] Params = new SqlParameter[" + TrueParameter + "];");
Response.WriteLine();
int i = 0;
foreach (Parameter P in Parameters)
{
if (P.Direction == ParameterDirection.ReturnValue)
{
continue;
}
string DotNetName = P.Name;
if (DotNetName.Substring(0,1) == "@")
{
DotNetName = DotNetName.Remove(0,1);
}
Response.WriteLine("\t\t\t\tParams[" + i + "] = new SqlParameter(\"" + P.Name+ "\", "+ P.DotNetType.FullSqlDbTypeEquivalent + ", " + P.Length+ ");");
Response.WriteLine("\t\t\t\tParams[" + i + "].Value = " + DotNetName +";");
if (P.Direction == ParameterDirection.Output)
Response.WriteLine("\t\t\t\tParams[" + i + "].Direction = ParameterDirection.Output;");
Response.WriteLine();
i++;
}
}
string MethodName = "";
string ReturnDataSet = "";
if (SP.ReturnsData == true)
{
MethodName = "ExecuteDataset";
ReturnDataSet = "ds = ";
}
else
{
MethodName = "ExecuteNoQuery";
}
string ParamString = "";
if (TrueParameter > 0)
{
ParamString = ", Params";
}
Response.WriteLine("\t\t\t\tif (_Transaction != null)");
Response.WriteLine("\t\t\t\t{");
Response.WriteLine("\t\t\t\t\t" + ReturnDataSet+ "SqlHelper." + MethodName + "(_Transaction, CommandType.StoredProcedure, \"" + SP.Name + "\"" + ParamString + ");");
Response.WriteLine("\t\t\t\t}");
Response.WriteLine("\t\t\t\telse if (_Connection != null)");
Response.WriteLine("\t\t\t\t{");
Response.WriteLine("\t\t\t\t\t" + ReturnDataSet+ "SqlHelper." + MethodName + "(_Connection, CommandType.StoredProcedure, \"" + SP.Name + "\"" + ParamString + ");");
Response.WriteLine("\t\t\t\t}");
Response.WriteLine("\t\t\t\telse");
Response.WriteLine("\t\t\t\t{");
Response.WriteLine("\t\t\t\t\t" + ReturnDataSet+ "SqlHelper." + MethodName + "(_ConnectionString, CommandType.StoredProcedure, \"" + SP.Name + "\"" + ParamString + ");");
Response.WriteLine("\t\t\t\t}");
if (SP.ReturnsData == true)
{
if (SP.Resultsets.Count > 1)
Response.WriteLine("\t\t\t\treturn ds;");
else
Response.WriteLine("\t\t\t\treturn ds.Tables[0];");
}
else
Response.WriteLine("\t\t\t\treturn;");
Response.WriteLine("\t\t\t}");
Response.WriteLine("\t\t\tcatch(Exception ex)");
Response.WriteLine("\t\t\t{");
Response.WriteLine("\t\t\t\tthrow new Exception(\"Cannot execute the stored procedure.\", ex);");
Response.WriteLine("\t\t\t}");
Response.WriteLine("\t\t}");
Response.WriteLine();
}
Response.WriteLine("\t}");
Response.WriteLine("}");
}
catch (System.Exception GlobalException) {
Source.CurrentTemplate.ExecutionStatusCode = GenerationStatus.Error;
Source.CurrentTemplate.ExecutionStatusMessage = GlobalException.Message;
Response.WriteLine();
Response.WriteLine("*************************");
Response.WriteLine(" AN EXCEPTION WAS THROWN");
Response.WriteLine("*************************");
Response.WriteLine(GlobalException.Message);
Response.WriteLine();
Response.WriteLine("****************");
Response.WriteLine(" FULL EXCEPTION");
Response.WriteLine("****************");
Response.WriteLine(GlobalException.ToString());
if (Source.DesignMode) {
System.Windows.Forms.MessageBox.Show(GlobalException.ToString(), "An error has occured", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
else {
throw;
}
}
}
// ----------------------------------------------------------------------------------------------------
// GetName function for element: 'Configuration Interface'
// ----------------------------------------------------------------------------------------------------
public string IConfigure_GetName() {
// Choose whatever name you wish for this object
return (@"IConfigure.cs");
}
// ----------------------------------------------------------------------------------------------------
// Creation method for element: 'Configuration Interface'
// ----------------------------------------------------------------------------------------------------
public void IConfigure() {
try {
Source.CurrentTemplate.ExecutionStatusMessage = "Code Generated Succeed.";
Response.WriteLine("// File Name: " + IConfigure_GetName());
Response.WriteLine("// Code Generated By: " + DynamicParameters.DeveloperAlias);
Response.WriteLine("// Code Generated On: " + System.DateTime.Now.ToShortDateString());
Response.WriteLine();
Response.WriteLine("using System;");
Response.WriteLine();
Response.WriteLine("namespace " + DynamicParameters.CompanyName + ".Configure");
Response.WriteLine("{");
Response.WriteLine("\tpublic interface IConfigure");
Response.WriteLine("\t{");
Response.WriteLine("\t\tstring GetConnectionString();");
Response.WriteLine("\t}");
Response.WriteLine("}");
}
catch (System.Exception GlobalException) {
Source.CurrentTemplate.ExecutionStatusCode = GenerationStatus.Error;
Source.CurrentTemplate.ExecutionStatusMessage = GlobalException.Message;
Response.WriteLine();
Response.WriteLine("*************************");
Response.WriteLine(" AN EXCEPTION WAS THROWN");
Response.WriteLine("*************************");
Response.WriteLine(GlobalException.Message);
Response.WriteLine();
Response.WriteLine("****************");
Response.WriteLine(" FULL EXCEPTION");
Response.WriteLine("****************");
Response.WriteLine(GlobalException.ToString());
if (Source.DesignMode) {
System.Windows.Forms.MessageBox.Show(GlobalException.ToString(), "An error has occured", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
else {
throw;
}
}
}
*>
Sean Day's Project Blog 