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 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.