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:

clip_image002

Once loaded any scripts containing tables will display a “CRUDGenerator” button in your Solution Explorer context menu:

clip_image004

If you select this the CRUD Generation Dialog will appear:

clip_image006

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:

clip_image008

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