Using T4 Templates with GDR Database Projects
For this blog I’ve developed an add-in for General Distribution Release (GDR) for Visual Studio Team System 2008 Database Edition projects (better known as “Data Dude”) which will generate Create, Read, Update and Delete (CRUD) stored procedures for a particular table in your model. I wanted to provide the ability to affect the content of those procedures without recompiling my add-in so I’m using Visual Studio’s built in, template based, code generation tool - known as T4. It’s not well known but extremely powerful. There’s a good MSDN article here. I’ll go through the key pieces of the code, but there’s nothing like running it for yourself, so I’ve provided the entire source in this zip.
Overview of the Add-In
The zip reference above contains an MSI called InstallCRUDGenerator.msi. Once you install, make sure the add-in is loaded by selecting the “Tools.Add-in Manager” menu button:
Once loaded any scripts containing tables will display a “CRUDGenerator” button in your Solution Explorer context menu:
If you select this the CRUD Generation Dialog will appear:
This dialog allows you to select your source table script and then view the generated CRUD stored procedures. If you’re happy with the generated procedures select “Save Procedures to Project” and 4 new stored procedures will be created in your project.
Now for the details! The text box in the middle of the CRUD Generation Dialog contains your T4 template. At the start of this blog I gave you a good T4 link so I won’t go into details on the scripting language itself, but let’s adjust the Create procedure seen in the image above. First let’s add my company name to the comments for this stored procedure. The comment is generated on lines 10 through 13 of the T4 Template:
1: /*
2: CREATE procedure for <#= tableFullName #>
3: Generated <#= DateTime.Now.ToString() #>
4: */
Let’s change this to
1: /*
2: Copyright (c) Microsoft Corporation. All rights reserved.
3: CREATE procedure for <#= tableFullName #>
4: Generated <#= DateTime.Now.ToString() #>
5: */
.. and hit the “Generate” button:
Notice now that our copyright appears in the generated stored procedure T-SQL and we did this without modifying our add-in. Very cool!
Feel free to experiment and modify the T4 templates I provide in this add-in. There are 5 templates installed into “%ProgramFiles%\Microsoft Visual Studio 9.0 CRUD Template AddIn”:
· CrudCreate.tt
o Responsible for generated the ‘Create’ stored procedure
· CrudDelete.tt
o Responsible for generated the ‘Delete’ stored procedure
· CrudRead.tt
o Responsible for generated the ‘Read’ stored procedure
· CrudUpdate.tt
o Responsible for generated the ‘Update’ stored procedure
· CrudToolbox.tt
o Useful utilities for walking the Data Dude model. This is where the columns are analyzed to determine how they are used in the templates above.
CrudToolbox.tt contains the most code. It’s here where I walk the Data Dude object model to determine the column and column types for the selected table. If you wish to change this file remember that it too can be altered without having to restart Visual Studio. Just open it in a text editor or another Visual Studio instance, edit and then hit the ‘Generate’ button as we did before.
Overview of the Add-In Code
In this section I’ll provide you with an overview of the CRUD Generator Add-in code. For anyone that’s ever generated a Visual Studio Add-in project you’ll know that Connect.cs is where the add-in initializes. It’s there where I add the ‘CRUD Generator’ button to the Solution Explorer context menu and handle the button click. My handling is a simple pass-off to a class I call the CRUDEngine:
1: public void Exec(string commandName, vsCommandExecOption executeOption, ref object varIn, ref object varOut, ref bool handled)
2: {
3: handled = false;
4: if(executeOption == vsCommandExecOption.vsCommandExecOptionDoDefault)
5: {
6: if(commandName == "CRUDGenerator.Connect.CRUDGenerator")
7: {
8: CRUDEngine engine = new CRUDEngine(ProjectFinder.GetFirstDatabaseProject(_applicationObject));
9: engine.Run();
10: handled = true;
11: return;
12: }
13: }
14: }
This engine creates an instance of my WinForms dialog and provides that dialog with a class I call the CRUDGenerator:
1: public void Run()
2: {
3: CRUDDialog dialog = new CRUDDialog();
4: dialog.Generator = new CRUDGenerator(_project);
5: dialog.ShowDialog();
6: }
I won’t go over my CRUDDialog since most of it is simple WinForms programming. The CRUDGenerator is where we’ll focus next. This class is responsible for executing the T4 scripting engine and returning the results. For more information on executing T4 programmatically see this msdn page.
The most important thing to note when walking the Data Dude object model is that it is not Serializable (although it is thread-safe). This means you cannot access the object model from another process (i.e. through out of process DTE) or from another AppDomain. Fortunately during the process of executing the T4 template engine you have the opportunity to provide a class which must derive from ITextTemplatingEngineHost. My instance of that host is called VSDBCustomHost and it it I provide the current AppDomain
1: public AppDomain ProvideTemplatingAppDomain(string content)
2: {
3: return AppDomain.CurrentDomain;
4: }
The core of this class is it’s RunTemplate method. In that method I create an instance of a VSDBCustomHost and process the template:
1: public string RunTemplate(ISqlTable selectedTable,
2: string templateFullPath,
3: out CompilerErrorCollection errors)
4: {
5: string tableScript = GetTableText(selectedTable);
6: errors = null;
7: if (string.IsNullOrEmpty(templateFullPath))
8: return string.Empty;
9:
10: VSDBCustomHost host = new VSDBCustomHost();
11: Microsoft.VisualStudio.TextTemplating.Engine engine = new Microsoft.VisualStudio.TextTemplating.Engine();
12: host.TemplateFile = templateFullPath;
13: host.IncludePath = Path.Combine(VsUtils.GetVSTSDBDirectory(), @"Extensions\SqlServer\");
14: host.VsUtils = _vsUtils;
15: AppDomain domain = host.ProvideTemplatingAppDomain(string.Empty);
16: string output = string.Empty;
17: try
18: {
19: // Set the table to be generated into the app domain
20: domain.SetData("TT_TABLE", selectedTable);
21: //Read the text template.
22: string input = File.ReadAllText(templateFullPath);
23: //Transform the text template.
24: output = engine.ProcessTemplate(input, host);
25: errors = host.Errors;
26: }
27: finally
28: {
29: domain.SetData("TT_TABLE", null);
30: }
31: return output;
32: }
At the beginning of this blog is the link to the zip file containing all the source for this add-in. From here I’ll leave you to paruse the source.
Conclusion
Hopefully you’ve now got an appreciation of the power of T4 templates! Remember that if you want to use these templates with Data Dude projects you’ll have to provide your own templating engine host since the Data Dude model cannot cross process or AppDomain boundaries since it is not serializable. Feel free to ping me if you’ve got questions! Have a good 2009!
Patrick Sirr
Data Dude Programmer
Comments
Anonymous
January 21, 2009
When accessing an ISqlProcedure element, I can enumerate all dependant columns (proc.BodyDependencies), but if I have two or more resultsets then I get a flat enumeration of both resultset 1 columns + resultset 2 columns. How could I get the resultset index belonging to a given column?Anonymous
January 21, 2009
The relationships between elements are mostly meant for dependencies. So you can't get what you're asking consistently. For example, if the same column is referred to twice from a proc, we collapse, and you'll only have one column reference. You might want to get the AST of the proc body and walk that instead. You'd have to walk the statement list until you find the select statement you're looking for.Anonymous
January 22, 2009
The comment has been removedAnonymous
January 24, 2009
The comment has been removedAnonymous
January 25, 2009
For complex select statements, it may be hard for you to analyze the Ast yourself. You can try to create a view for these complex ones (Temp tables may be a problem still) and walk the columns of the view.Anonymous
March 10, 2010
Does this only work as a VS addin? I can't seem to access the schema objects when I run the templates from a separate project.Anonymous
March 11, 2010
Yes, it only works as a VS addin. When Dev10 Ships we'll have a public API allowing you to access the model from another projects. For a preview see http://msdn.microsoft.com/en-us/library/microsoft.visualstudio.data.schema.package.project.idatabaseprojectnode_members(VS.100).aspxAnonymous
March 11, 2010
Thanks for the quick reply, will VS2010 RC support T4 templates directly from the database project? Or is the solution to use either an Addin/separate-project approach? I can understand why template processing is disabled and some of the issues around generating parts of the database model with templates that query that same model. However if there were any way to run t4 templates from the database project that would be very cool indeed! (If not the separate project approach is also good). Looking forward to the release. Keep up the good work!Anonymous
March 11, 2010
Indeed that would be a great addition to the Database Project. Unfortunately for now this AddIn is the only mechanism for using T4 in a Database Project. We were not able to fit this into the Dev10 schedule. To give you some details T4 runs inside of it's own AppDomain so anything that crosses that boundary must be serializable. Our model of your code is not, so accessing model elements from T4 within a Database Project would take quite a lot of work (or something clever which I'd have to research). Work I'd love to do! But it just didn't make it into Dev10.Anonymous
May 12, 2010
Hi Patrick, is it possible to use this add-in in VS 2010 too ??? I can see it only in vs2008 add-in manager and I would like to have it available in VS 2010 add-in manager too. Could You kindly provide a VS 2010 version or maybe is there a smartest solution to make it vs 2010 compliant ??? ThanksAnonymous
May 12, 2010
Sorry, I'm swamped at work right now! I'll see if I can free up some time to upload a VS 2010 version. For now you can certainly download the source and recompile. Remember that you'll need the VS SDK installed for 2010 in order to successfully resolve all the symbols.Anonymous
May 13, 2010
Yesterday I downloaded vs2008 version and recompiled it in vs2010, without any problems, but i don't see it in my vs2010 add-in manager yet. I wait your vs2010 version to try again. ThanksAnonymous
June 18, 2010
Hi Patrick, Did you consider enabling T4 templates without having access to the model? So I mean for simple standalone .TT files that generate .SQL. At least that would already solve our 'problem' of automatically generating scripts for filling a table with predefined contents... ThanksAnonymous
August 22, 2010
I second tjipke's comment - just being able to run a t4 template in a database project, in order to generate sql scripts, without any access to the model would be great. At the moment I'm stuck generating the scripts in a separate project, then mindlessly copying them/linking them into the database project...Anonymous
August 23, 2010
Guys, if I understand you correctly you need T4Toolbox which allows you to inject sql files into a vs db project. You then build a "Db Model" c# dll project which has tt files that get the model from somewhere (xml, db etc) and produce sql files on demand. There is a bit of a learning curve and I'd advise using pre-processed tt's so most of the tempates are in real compiled code. The current release is a bit slow for one tt producing hundreds of files, but the recent beta solves that. Having said that I am still waiting for the vs2010 version of this code though so I can take the basic model from one place using the above approach and "layer over" the metadata from a db project that already has datatypes etc in rather than duplicate it all in the model