3

We are building a query service that needs to parse the user's search term and generate a form of SQL (not T-SQL but a proprietary SQL-like query language) out of it. To generate the SQL, we are looking into T4 templates.

I looked into another question here at Creating T4 templates at runtime (build-time), and I understand the basic idea; however what we need is not a physical file output but a simple in memory string containing the final SQL statement. Is that possible?

The second question I have, which almost more important: how fast is this T4 stuff when taking into account the rather complex logic we need to generate the SQL inside a T4 template file. Thanks.

Community
  • 1
  • 1
John
  • 3,591
  • 8
  • 44
  • 72

1 Answers1

2

What I think you want to take a look at preprocessed templates (aka runtime templates). You create them in visual studio 2010 by using the template Preprocess Text Template.

I created this very simple template (I named it MyTemplate.tt):

<#@ template language="C#"#>
<#
    for (var iter = 0; iter < HowManyCommentsToGenerate; ++iter)
    {
#>
    // <#=iter#>
<#
    }
#>

I added this partial class that extends the generated class with HowManyCommentsToGenerate field:

partial class MyTemplate
{
    int HowManyCommentsToGenerate;

    public MyTemplate (int count)
    {
        HowManyCommentsToGenerate = count;
    }
}

Finally I use it like this (note that the output is string not a file):

class Program
{
    static void Main(string[] args)
    {
        var str = new MyTemplate(32);
        Console.Write(str.TransformText());
    }
}

As far performance I honestly don't have enough experience with run-time templates to advice you on this. I recommend reading the generated code and profiling it.

Just another metaprogrammer
  • 11,499
  • 2
  • 35
  • 54
  • Thanks! That works! It also showed me that T4 templates are not really suitable for what we are trying to achive. Do you think it is feasable at all to generate SQL if we don't need files? To me it seems like we have to put the SQL-generating logic in a seperate class anyway, and the T4 is only cumbersome to deal with. Just use a StringBuilder or something. – John Sep 21 '11 at 11:56
  • I love T4 but I am not sure that it's the correct tool for generating highly dynamic SQL from an expression. If you think it seems cumbersome and string builder is preferable then go for StringBuilder. No need to use T4 just for the sake of it. However in certain cases runtime templates can be very useful. For instance if you like to mix static content with dynamic content. – Just another metaprogrammer Sep 21 '11 at 12:34
  • Agreed with FuleSnabel that the mix of dynamic and static is the sweet spot. If almost every element in your sql-ish language is variable depending on the input search term, then really you're just translating a single ADT from one concrete syntax to another. on the other hand, if there's significant boilerplate in the output sql and it's really just parameterized by the query terms, then T4 is a good choice. – GarethJ Sep 23 '11 at 06:35