Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server
Article

Template based code generation

Rate me:
Please Sign up or sign in to vote.
4.48/5 (10 votes)
10 Mar 2005CPOL9 min read 70.2K   1.6K   56   11
An article about template based code generation and a demonstration of how to quickly generate a wrapper class for stored procedures.

Introduction

When working on client-server projects, there is always a good deal of repetitive coding required. For example, the use of a stored procedure requires the same lines of data access code over and over to be written again. Therefore, generating the data access code quickly with the help of a tool is very desirable. This article introduces a code generation tool. The idea behind it is not new. It is about processing templates as we know from the ASP, ASP.NET, or JSP technologies. Here is a familiar ASP page example:

VBScript
<%
var name = "John";
%>
Dear <%= name %>

The processing of this template is that the ASP engine converts it into a JScript document by simple stripping the tags <% and %> and wrapping the text '    Dear ' and the <%= name%> part into a Response.Write("    Dear " + name). The resultant JScript file, as shown below, is then passed to the scripting host engine for processing.

JavaScript
var name = "John";
Response.Write("    Dear " + name);

The technique here is simply one of converting a template to code which then, after compilation or interpretation, renders the target document. The ASP and ASP.NET technologies render HTML output but it is also possible to render any type of document including C# source code.

This article is about template based code generation. First, I would like to introduce the tool I developed and than show it off by generating a wrapper class for stored procedure access in SQL Server. Here is how this tool works.

The template-based code generator

To output a target document, the tool must do the following:

  1. Read the template file and parse it into its constituent sections.
  2. Use the parsed template sections and write a C# class into a temporary file.
  3. Compile the temporary C# code file into an assembly.
  4. Load the compiled assembly, create an instance of the compiled class, and invoke its rendering method.

The rendering method will generate the desired target document. That is all there is to it. Let's look at each of the four steps.

  1. Read the template file and parse it into its constituent sections:

    Parsing the template file should provide the tool with all the necessary information so that it can write a proper C# class, compile it into a proper assembly, and then load and execute the rendering function of the class. To make all of this happen with a minimum of fuss, I have invented a number of template section markers that need to be explained.

    Given the template text mentioned above, the tool would write a class like so:

    C#
    /* 
     Here is a C# version of the sample again
    
    <%
    string name = "John";
    %>
       Dear <%= name %>
    */
    
    using System;
    using System.IO;
    
    public class RenderClass
    {
        private TextWriter Writer;
        public void Render(TextWriter writer)
        {
            Writer = writer;
            
            string name = "John";
            Writer.Write("    Dear " + name);
        }
    }

    The tool can make a number of limited assumptions when writing the class code. But to be most effective, the template author should be able to specify class members, reference assemblies as well as used namespaces. We can accomplish all of it with template section tags. Consider this template:.

    <%-namespaces
        using DatabaseCatalogReader;
    %>
    <%-class
        DatabaseCatalog catalog;
        string GetDatabaseName()
        {
            if(catalog == null)
                catalog = new DatabaseCatalog()
            return catalog.Name;
        }
    %>
        The name of the database is <%=GetDatabaseName()%>

    There are two tags which divide the template into two different sections. The tags must not contain any white-space.

    1. <%-namespaces - a list of the namespaces the render class requires.
    2. <%-class - the render class' member variables and methods.

    The tool will convert this template into the following compilable class:

    C#
    using System;
    using System.IO;
    
    using DatabaseCatalogReader;
    
    public class RenderClass
    {
       
       DatabaseCatalog catalog;
       string GetDatabaseName()
       {
           if(catalog == null)
               catalog = new DatabaseCatalog()
           return catalog.Name;
       }
    
       private TextWriter Writer;
       public void Render(TextWriter writer)
       {
           Writer = writer;
           
           Writer.Write("    The name of the database is ");
           Writer.Write(GetDatabaseName());
       }
    }
    

    Here we see that the tagged template section assists the tool to write some code inside and outside of the class definition. All the non-tagged template sections are converted to the implementation of the Render method.

  2. Use the parsed template sections and write a C# class into a temporary file:

    The tool parses the entire template before writing a class. You can structure the tagged and non-tagged template sections in a mixed order. But it is most advisable to do it in the order shown here:

    <%-namespaces
        // the using statements inside this section
        // will be written outside the class definition
    %>
    
    <%-class
        // this is a C# code section
        // all methods and class member variables must show up here
    %>
    
    <%
        // this is a no-tagged template section
        // and all C# code will be written as part of the 
        // implementation of the class's'Render' method.
    %>
    
        This is one line of template text 
            and will ultimately end up in the target document.
        
    <%
        // this again is a non-tagged template section
    %> 
    
        This is another line of <%= "template text." %>

    Any template can be expected to have multiple non-tagged template sections that alternate with the template text. There can also be multiple tagged template sections, appearing in any order. For example:

    <%-class
        // this is a C# code section
        // all methods and class member variables must show up here
    %>
    
    <%-namespaces
        // the using statements inside this section
        // will be written outside the class definition
    %>
    
    <%
        // this is a no-tagged template section
        // and all C# code will be written as part of the 
        // implementation of the class's 'Render' method.
    %>
    
        This is one line of template text 
          and will ultimately end up in the target document.
        
    <%
        // this again is a non-tagged template section
    %> 
    
        This is another line of <%= "template text." %>
                               
    <%-class
        // this is a C# code section
        // all methods and class member variables must show up here
    %>

    The tool will collect and consolidate all template sections before writing the render class. But, for reasons of style and readability, do keep the tagged template sections in one place.

    The tool writes the class into a C# file named after the template file plus the extension '.cs'. A template file named 'Template.txt' would have its render class written to 'Template.txt.cs'.

  3. Compile the temporary C# file into an assembly:

    The namespace Microsoft.CSharp provides a managed wrapper to the C# compiler which is used to compile the C# file. Inspecting the file after creation, you will find a series of #line numbers. These numbers correspond to the code sections in the template file. That is one helpful way to identify the faulty code in the template file in case the compiler reports an error. The error messages are similar to the ones emitted by the C# compiler. The line number should help locate where about in the template file the compiler encountered an error.

  4. Load the compiled assembly, create an instance of the compiled class and invoke its rendering method:

    Once the C# file has been compiled into an assembly, it is loaded and the RenderClass.Render(TextWriter writer) method is invoked to render the output, with a StreamWriter opened on the TargetFile, and passed to the Render method. You can redirect the writer to anything you want.

    <% 
        // this will write the target document to the console
        // use it while developing the template
        Writer = Console.Out;
    %>

Configuration

The template based code generator relies upon input provided by a config file. Start the tool without any command line arguments. You will see this dialog box. Press the button "Edit Config File ..." to bring up a small text editor with the config data. Edit and save it if you want to.

Sample Image

There are three interesting sections in the config file for you to edit, compiler-option, regular-expressions, and references.

You have the ability to craft a template that makes full use of all .NET Framework class libraries as long as you provide the necessary list of assemblies. Edit the references section by listing the necessary assemblies. Just write the filenames with a '.dll' extension like System.Data.

You may also want to edit the compiler-options section in case you include a private assembly. The tool needs to know where to find your assemblies. Just provide the library paths as lib options, like so:

The value must be a semi-colon separated list of full path names,
e.g. "C:\MyPrivateAssemblies; D:\"

<option name="lib" value="" type="string" />

There is also one other way to add assembly references and library paths. Using the template section tags (introduced now) <%-references %> and <%-libpaths %>. So, here is what it looks like:

<%-references
    System.Data.dll 
    DatabaseCatalogReader.dll
%>

<%-libpaths
    C:\MyPrivateAssemblies
    D:\
%>

There is one more section in the configuration file to be explained, <regular-expressions>. If you specify the reference assemblies in the template file, the tool will use a regular expression evaluator to parse the information out of the template file. The config file lists two regular expressions, one to parse the listed assemblies and the other to parse the library paths. If necessary, you can substitute the ones provided with your own and better ones.

Target document formatting

The output document will look just like the template specifies. That includes all of the white spaces. For example:

<% string name="Joe";%>
My Name is <%= name%>.
My Name is <%= name%>.
My Name is <%= name%>.

This template will be transformed into this:

My name is Joe.
My name is Joe.
My name is Joe.

But coding the template differently, like this:

C#
<%
    string 
name=
    "Joe"; int
    i=0; while(i++
    < 3) {
    %>
My
    Name is <%= name%>.
<%   
    }
%>

produces this target document:

My name is Joe.

My name is Joe.

My name is Joe.

The additional and unexpected blank lines are the cause of extra "\r\n" characters that precede the string, e.g., "\r\n My name is Joe.". The extra "\r\n" follows the closing tag %>. To eliminate this extra "\r\n", you can add one more > character to the closing tag, like this %>>. Here is how the template should be written to achieve the desired formatting:

C#
<%
    string
        
name=
    "Joe"; int
    i=0; while(i++
    < 3) {
    %>>
My
    Name is <%= name%>.
<%   
    }
%>

The comment tag and the template marker

The last template section tag to be introduced serves to annotate the template with comments.

<%!

    Anything
    between these two tags is considered to be a comment. A
    comment should be used to annotate the template with explanations. The
    parser will strip it out.
%>  
<%! ignore "\r\n" at the end of this comment %>>
<%! 
    One last remark. Template sections cannot be nested.
    Don't do this: <%! Nested comment %>
%>

There is nothing to prevent you from running any text file as a template. However, the tool will read the first line of the text and check if it was meant to be a proper template file. This is the marker that the parser expects: @@Template@@.

Here is a template structure as a final summary:

@@Template@@ <%! indicates this to be a proper template %>>        
<%! Note how all the '\r\n' characters are stripped from
the
target document %>> <%! list all your references here, e.g. %>> <%-references
MyAssembly.dll
%>> <%! help the tool find the assembly
%>>
<%-libpaths
C:\MyLibraries
%>> <%! add all the namespaces that the
render
    class
expects
%>> <%-namespaces using System.Data; using MyAssembly; %>> <%! all the
code
that
    may be
    part of
the
class's definition %>> <%-class // this is a code section so comments are
allowed
    
%>>
<%! all the render code from here on %>>

The Stored Procedure Wrapper

The sample application uses the template based code generation tool. It was designed to retrieve the metadata of stored procedures in SQL Server and then to produce a wrapper class.

If you install the MSI package, you can run it from the desktop. With the initial dialog box, you can navigate to the 'Template' directory to load the 'StoreProcs.txt' template. Here is the sequence of dialog boxes you should see:

Sample Image

Specify the template file as well as the target file.

Sample Image

Select the database server and the database.

Sample Image

Pick the stored procedures of interest.

You can also start the tool from the command line. Developing a template with Visual Studio, I have configured the code generator as an external tool.

Sample Image

The command line arguments are simply this:

tcg -template:<filename> -target:<filename>

You can copy the following macro into the 'External Tools' argument edit control.

-template:$(ItemPath)   -target:$(ItemDir)$(ItemFileName).cs

Conclusion

I hope you can put this tool to some good use. I decided on developing after some time of working on an alternate approach. I first believed that XSL transforms would offer me a quick way to accomplish the same. But I eventually realized that creating the required XML input was not always easy and quick. Also, the writing of XSL documents was a much more laborious task than I first thought it would be. For example, if the target document itself contained the reserved characters '<', '>', and '&', I found extra difficulties in writing special XSL templates to print these reserved characters.

The template based approach here could well be integrated with our normal development process. Every project I have worked on has a lot of cookie cutter code to be written.

Regrettably, debugging a template is not as easy as debugging an ASP.NET page. I cannot find the time to figure out a way to add debugging support to this tool. If anyone out there has an idea, then please share it with me. I would like to learn about it.

Epilog

I was motivated to build the template based code generator when I found myself getting tired of coding database access code. It is cookie cutter stuff. In the meantime, I learned about CLR hosting in SQL Server 2005. Hoping for some real relief, I went to 'MSDN' to learn all about it. I was rather disappointed when I saw the programming model proposed. I hoped that Microsoft would invent a dialect of C# or VB.NET to accept embedded SQL statements. Think of T-SQL as a special dialect of the procedural language 'T'. So, I wished for C#-SQL or VB.NET-SQL. What I saw, via the sample code, looked very much like code you would write as data access code in the application tier. I have also wondered about how to call the stored procedures written in a .NET language and stored in the database. It appears that the conventional data access methods are still required. Again, I wished that a common interface assembly would be a better way to interface with the database. Here is the model that I had wished for:

C#
// database stored procedures to get certain customers

// shared between client and server
public class Customer
{
    string Name;
}
// shared between client and server
public interface IMyDatabase
{
    SqlResultset GetCertainCustomers(string cityName);
    Customer[] GetCertainCustomers(string cityName);
}

// defined as part of the database
class MyDatabase
{
    // one way to get a customer list
    public SqlResultset GetCertainCustomers(string cityName)
    {
        // note the embedded sql statement
        return select Name from Customer where City = cityName;
    }
    // another way to get a customer list
    public Customer[] GetCertainCustomers(string cityName)
    {
        // note the embedded sql statement
        return select Name from Customer where City = cityName;
    }
}

// client application use of stored procedure

// get a transparent proxy
string connectionString =
  "Integrated Security=SSPI; Server=MyServer; Initial Catalog=MyDatabase";
IMyDatabase db = (IMyDatabase)SqlServer.Connect(connectionString);

// call the stored procedure
SqlResultset resultSet = db.GetCertainCustomers("Arlington");
while(resultSet.MoveNext())
{
    Console.WriteLine("Name: {0}", resultSet["Name"]);
}

// do it again but more elegantly
Customer[] customers = db.GetCertainCustomers("Newton");
foreach(Customer customer in customers)
{
    Console.WriteLine("Name: {0}", customer.Name);
}

As you can see, there is no messing about with SqlConnection and SqlCommand objects. What do you people think about it?

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States United States
I am a consultant, trainer, software archtect/engineer, since the early 1980s, working in the greater area of Boston, MA, USA.

My work comprises the entire spectrum of software, shrink-wrapped applications, IT client-server, systems and protocol related work, compilers and operating systems, and more ....

I am currently focused on platform development for distributed computing in service oriented data centers.

Comments and Discussions

 
GeneralRe: ehh Pin
Bahadir Cambel18-Mar-05 10:59
Bahadir Cambel18-Mar-05 10:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.