StringTemplate is a powerful tool that can be used to generate source code, web pages, emails, or any other formatted text output. Although StringTemplate was originally written in Java it has been ported to C#, Python and Ruby. My first encounter with StringTemplate was a few years ago when I needed to build a complex deployment script for setting up new SQL Server instances. The script needed to configure operators, alerts, SQL mail and also create a few basic jobs. The most important requirement was that the template could be easily changed without recompiling any code or needing any advanced knowledge of programming. Luckily, StringTemplate met all these requirements.
In order to get familiar with the functionality lets start by looking at a few sample templates. Here is a classic example which I found on the antlr website. This is a template for generating a simple SQL statement.
StringTemplate query = new StringTemplate("SELECT $column$ FROM $table$;");
query.SetAttribute("column", "name");
query.SetAttribute("table", "User");
In this example you probably immediately noticed the dollar signs. When we surround areas of our template with dollar signs we are telling StringTemplate that some work needs to be done. In particular when you execute this code you replace the variable $column$ with the value “name” and $table$ with “User”. The end result is the string
“Select name from User”.
Now previously I mentioned that I was using StringTemplate to build a deployment script. In my environment I have many different versions of SQL Server. Since I did not want to write a special template for each SQL version I needed a way to put conditional logic in my template. Fortunately, this is easily accomplished with StringTemplate.
$if(supports_sql_mail)$
...
$endif$
Finally, I also needed a section of my template which created all the various operators in the SQL Instance. Since there are 7 DBAs in my team I needed some sort of loop. Again, StringTemplate to the rescue:
$operators:{
IF EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = N'$it.name$') BEGIN
EXECUTE msdb.dbo.sp_delete_operator @name = '$it.name$'
END
print 'Creating operator: $it.name$';
--create the operator
EXECUTE msdb.dbo.sp_add_operator @name = '$it.name$', @enabled = 1, @email_address = '$it.emailaddress$', @netsend_address = '$it.netsendaddress$', @category_name = N'[Uncategorized]', @weekday_pager_start_time = 80000, @weekday_pager_end_time = 180000, @saturday_pager_start_time = 80000, @saturday_pager_end_time = 180000, @sunday_pager_start_time = 80000, @sunday_pager_end_time = 180000, @pager_days = 0
GO
--notifications
EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Full msdb log', @operator_name = '$it.name$', @notification_method = $notification_value$
... YOU GET THE POINT!
GO
}$
Breaking it down, we define a loop on Line 1. As we iterate over the array, the individual objects are referenced by the keyword
it. The properties of each object are read by using dot notation. For example, when I want the operator’s name I use $it.name$.
So by now you have a basic understanding behind the power of StringTemplate. If you want to use StringTemplate in your application then here are the steps:
- Download the binaries (234 kb) from antlr.org.
- Add a reference in your project to the antl.runtime.dll and the StringTemplate.dll.
- Add a using statement for Antlr.StringTemplate.
- Write the code to load the template, and replace the variables.
Now it’s a simple matter of loading the template, substituting some variables and returning the output. Here is the code I used to build my deployment script:
if ((i.MajorVersion ?? 9 ) >= 9) {
template.SetAttribute("supports_sql_mail", true);
template.SetAttribute("notification_value", 5);
}
else {
template.SetAttribute("supports_sql_mail", true);
template.SetAttribute("notification_value", 4);
}
template.SetAttribute("smtp_server", smtpServer);
template.SetAttribute("mail_account_name", smtpServer + " Administrator");
template.SetAttribute("email_address", "dba@" + instanceName + ".com");
template.SetAttribute("delay_between_responses", delayBetweenResponses * 60);
template.SetAttribute("operators", operators);
return template.ToString();
To summarize, on line 1 we define a StringTemplate group. This is basically telling the StringTemplate engine where the templates are stored. Since I am using StringTemplate in web application I make a call to Server.MapPath to get the full directory path. On line 2, we tell StringTemplate that we are going to use the template named “SQLDeploymentScript”. Just to warn you, the filename on disk is actually named SQLDeploymentScript.st but we leave off the extension in our code. This is a convention that StringTemplate uses so just go along for ride. Next, on lines 4-18 we substitute the variables in the template. Finally we call template.ToString() to get the results.
So hopefully this article gave you a good idea of how powerful StringTemplate can be. You can use it to generate HTML, emails, code or whatever else you can dream up.
