Oleg Sych

This article demonstrates how to use SMO and T4 templates to generate CRUD stored procedures in Visual Studio.

Overview

Stored procedures can be used to implement CRUD (Create, Read, Update and Delete) database operations. As Andrew Novick explains in his article "Implementing CRUD Operations Using Stored Procedures" (Part 1 and Part 2), using stored procedures helps to improve application performance, encapsulate SQL code in data access layer, reduce SQL injection vulnerability and prevent casual table browsing and modifications. The idea is to have a "Create" stored procedure that encapsulates INSERT SQL statement, one or more "Read" procedures that encapsulate SELECT statements, "Update" procedure that encapsulates UPDATE statement and "Delete" procedure that encapsulates DELETE statement.

Typically, CRUD stored procedures contain repetitive code and can be generated using information about database schema. Microsoft SQL Server Management Objects (SMO) is one of .NET APIs that can be used for this purpose. The following code demonstrates how to access schema of the Products table in Northwind database on the local computer.

using Microsoft.SqlServer.Management.Smo;
// …
Server server = new Server();
Database database = new Database(server, "Northwind");
Table table = new Table(database, "Products");
table.Refresh();
// Here we can access:
//  - table.Columns
//  - table.Indexes
//  …

Having access to table schema via SMO, building a T4 template to generate a stored procedure for it is straightforward:

create procedure [dbo].[<#= table.Name #>_Delete]
<#
    foreach(Column column in table.Columns)
    {
        if (column.InPrimaryKey)
            // …
    }
#>
as
    delete from [dbo].[<#= table.Name #>]
    where <# // … #>

While it is possible to generate CRUD stored procedures in standalone SQL scripts, it would be more useful to integrate code generation into existing database development process. Attached source code demonstrates how T4 code generation can be used in database projects of Visual Studio Team Edition for Database Professionals.

One of the challenges of integrating code generation with DB Pro edition is that it places each database object in a separate SQL file. In other words, "Create" and "Update" and "Delete" procedures need to be in their own, separate SQL files. On the other hand, T4 engine supports only one output file per template. This makes generation of "Read" stored procedures based on table indexes more difficult as the number of SQL files needs to change depending on the number of indexes.

Another challenge of integrating T4 with DB Pro edition is that T4 engine integrates into Visual Studio as a TextTemplatingFileGenerator custom tool. Unfortunately, database projects in VS 2005 DB Pro don’t support custom tools. As a workaround, we will use a separate C# project for T4 templates and code generation.

Implementation

In order to run this code, you will need Visual Studio 2005 Standard (or higher) Edition, SQL Server 2005, Northwind sample database and DSL Tools installed on your computer. You may also want to install T4 Editor by Clarius Consulting, which adds IntelliSense and syntax highlighting to Visual Studio text editor for T4 templates. Although having Visual Studio 2005 Team Edition for Database Professionals is optional, it will allow you to automatically verify and run generated SQL scripts.

Attached source code contains the following items.

CrudPot.sln

This solution includes two projects - Northwind and Templates.

Northwind

This folder contains a database project created using Visual Studio Team Edition for Database Professionals. This project was generated by importing Northwind database script.

Templates

This folder contains a C# class library project. This project contains no C# code, only T4 templates. Stored procedure templates are physically located in the "Northwind\Schema Objects\Stored Procedures" folder and added as links to the root of the C# project.

• DeleteProcedure.tt. This is a T4 template that generates a DELETE stored procedure. Generated procedure defines parameters for the primary key and timestamp columns of the target table.
• InsertProcedure.tt. This is a T4 template that generates an INSERT stored procedure. Generated procedure defines parameters for all columns of the target table. Parameters for IDENTITY and TIMESTAMP columns are defined in the end of the list as OUTPUT parameters. Generated stored procedure returns actual IDENTITY and TIMESTAMP values generated by the SQL server.
• UpdateProcedure.tt. This is a T4 template that generates an UPDATE stored procedure. Generated procedure defines parameters for all columns of the target table. Parameter for TIMESTAMP is defined as OUTPUT parameter. Generated stored procedure returns new TIMESTAMP value generated by the SQL server.
• CommonCrud.tt. This is a T4 template that contains common code "included" by other templates and is not intended to be used directly.

Each template has the following parameters:

• TableName (required, name of the database table for which templates will generate stored procedures)
• ConnectionString (required, used to specify database used by templates to find the target table)
• SchemaName (optional, used to specify name of the schema which contains the target table and will contain the generated stored procedures)

Generated stored procedures implement optimistic concurrency control using TIMESTAMP (a.k.a. ROWVERSION) columns.

Usage

In order to use the attached T4 templates in your own project, follow the steps below.

• Create an empty C# (or VB) project. This project will contain only T4 templates and will be used for code generation. In the attached example, this project is called Templates.
• Save copies of CommonCrud.tt, DeleteProcedure.tt, InsertProcedure.tt and UpdateProcedure.tt in the folder where you want to generate SQL script files for CRUD stored procedures. In the attached example, these files are located in the "Northwind\Schema Objects\Stored Procedures" folder because that’s where Northwind database project saves SQL scripts for stored procedures.
• For each DELETE stored procedure, add a new text file with .tt extension to your template project. Name of this file will determine name of the SQL file it will generate. Modify this file to use DeleteProcedure.tt template and specify ConnectionString, TableName and SchemaName parameters. For example, dbo.Products_Insert.proc.tt contains the following text:

  <#
      this.ConnectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=True";
      this.TableName = "Products";
  #>
  <#@ include file="DeleteProcedure.tt" #>

  This template generates the following code in dbo.Products_Insert.proc.sql:

  – <autogenerated>
  –   This file is generated from T4 template dbo.Products_Delete.proc.tt.
  –   Any changes made manually will be lost next time this file is regenerated.
  – </autogenerated> create procedure [dbo].[Products_Delete]
      @ProductID int,
      @TimeStamp timestamp
  as
  begin
      delete from [dbo].[Products]
      where
          [ProductID] = @ProductID and
          [TimeStamp] = @TimeStamp   
  end

  If you are using Visual Studio Team Edition for Database Professionals, add the generated SQL file your database project. Note that this requires having all template files in the "Schema Objects\Stored Procedures" folder of the database project.

• Repeat the previous step for INSERT stored procedure. Use InsertProcedure.tt template and see dbo.Products_Insert.proc.tt for example.
• Repeat the previous step for UPDATE stored procedure. Use UpdateProcedure.tt template and see dbo.Products_Update.proc.tt for example

After changing database schema you can regenerate all stored procedures by selecting your template project in Solution Explorer and clicking "Transform All Templates" button in its toolbar.

If you plan to customize any of the T4 templates, consider adding them to your template project. If the template project and templates are located in different folders, make sure that template files are added to the project as linked items and not copies.

Consider modifying CommonCrud.tt to specify actual connection string and avoid having to do it in the individual .tt files.

If you don’t use and don’t plan to use Visual Studio Team Edition for Database Professionals, consider combining CommonCrud.tt, DeleteProcedure.tt, InsertProcedure.tt and UpdateProcedure.tt to generate a single SQL script file with all three stored procedures.

Known Issues

Current version of the attached templates has the following limitations.

• Column names with special characters are not supported.
• Generation of SELECT stored procedures is not supported.
• Changes made in the database project are not detected until they are applied to the database.

About T4

T4 (Text Template Transformation Toolkit) is a template-based code generation engine. It is available in Visual Studio 2008 and as a download in DSL and GAT toolkits for Visual Studio 2005. T4 engine allows you to use ASP.NET-like template syntax to generate C#, T-SQL, XML or any other text files.

For more information about T4, check out my previous article.

Download

T4 templates and source code used in this article are available for download here.