Invoking Stored Procedures generating C# Code using T4 Templates

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

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); 

  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");
    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:

      using (var conn = new SqlConnection(connStr))
        conn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)

        SqlCommand cmd = new SqlCommand("dev_Generate_DAL_Classes", conn);
        cmd.CommandType = CommandType.StoredProcedure;
    catch(Exception ex)


If no errors occurred, save the generated file. Otherwise, either raise warnings or errors.

  if (errors.Count == 0)
    foreach(var error in errors)
      if (warnings)
        this.Warning(Host.TemplateFile + ": " + error);
        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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: