When developing database applications, I usually start out with the database schema and have the necessary C# code (data classes, procedure calls, application-specific constants) generated by a couple of stored procedures. For my own projects, I create a batch file to build the various parts of the solution, all the way from code generation to publishing the application.
Maybe not everybody shares my preference for building-by-batch, so I was looking for an alternative way to invoke code generation in stored procedures, and I remembered working on a project a couple of years ago that used T4 Text Templates to generate C# code.
The first component of the T4 solution is MultipleOutputHelper.ttinclude which implements features such saving only changed files, handling check-out if connected to TFS, and much more.
Since I want to access the connection strings stored in web.config or app.config, I searched for sample code and found ConfigurationAccessor.tt.
The final T4 Template looks like this. The header includes both libraries and declares .Net namespaces:
<#@ template debug="True" hostspecific="True" language="C#" #><#@ output extension=".cs" #><#@ include file="MultipleOutputHelper.ttinclude" #><#@ include file="ConfigurationAccessor.ttinclude" #><#@ assembly name="System.Data" #><#@ import namespace="System.Data" #><#@ import namespace="System.Data.SqlClient" #><#
Next, we instantiate the objects declared in both libraries
var manager = Manager.Create(Host, GenerationEnvironment);
manager.StartNewFile("Classes.generated.cs");
var config = new ConfigurationAccessor((IServiceProvider)this.Host);
var connStrEntry = config.ConnectionStrings["default"];
Depending on your build process, you may want to cause database errors (connection string, connectivity, execution) to raise either warnings or compiler errors:
var warnings = true;
var errors = new List<string>();
if (connStrEntry == null)
{
errors.Add("Connection string named 'default' not found");
}
else
{
var connStr = connStrEntry.ConnectionString;
After checking the connection string, let’s connect to the database and execute the stored procedure.
The output of PRINT commands is retrieved using the InfoMessage event:
try
{
using (var conn = new SqlConnection(connStr))
{
conn.Open();
conn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
{
this.WriteLine(e.Message);
};
SqlCommand cmd = new SqlCommand("dev_Generate_DAL_Classes", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
}
catch(Exception ex)
{
errors.Add(ex.Message);
}
}
manager.EndBlock();
If no errors occurred, save the generated file. Otherwise, either raise warnings or errors.
if (errors.Count == 0)
{
manager.Process(true);
}
else
{
foreach(var error in errors)
{
if (warnings)
this.Warning(Host.TemplateFile + ": " + error);
else
this.Error(Host.TemplateFile + ": " + error);
}
}
#>
I originally tried to store warnings and errors using the Host.LogErrors method. However, no matter which Error Code I set in CompilerError, the result would always be errors rather than warnings. Using the TextTransformation‘s Warning() and Error() methods did the trick.
devioblog 