Developer Journey

In the forthcoming book that Jason Bock and I are working on called Metaprogramming in .NET, we cover a lot of code generation techniques. Unfortunately, we weren’t able to fit a full chapter into the first version of the book concerning Microsoft’s Text Template Transformation Toolkit (T4) so I’ll dedicate some blog posts to the topic instead. In this post, I’ll show you how to use T4 to generate MERGE scripts in T-SQL.

The following T4 script requires that the Microsoft SQL Server 2008 (or 2005) SQL Management Objects (SMO) assemblies are installed in the Global Assembly Cache (GAC). It also assumes that I have the Northwind database installed in my “(local)” instance of SQL server and that there’s another instances called ReplicatedNorthwind. The idea here is that I’m going to use a T4 script to generate T-SQL code that uses the MERGE statement to “upsert” data from the Northwind Products table to the ReplicatedNorthwind Products table. Changed records will be updated. New records will be inserted and those records deleted from the source will also be deleted from the target. T-SQL’s MERGE statement wraps all this functionality up into a one logical operation:

First, let’s look at the T4 script. If you're reading this directly on my blog (not via syndication), you can double-click the code and copy it with Ctrl-C:

<#@ template language="C#" #>
<#@ output extension="SQL"#>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.Sfc" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#
  string serverName = "(local)";
  string sourceDbName = "Northwind";
  string targetDbName = "ReplicatedNorthwind";
  string schemaName = "dbo";
  string entityName = "Products";

  Server server = new Server(serverName);
  Database targetDatabase = new Database(server, targetDbName);
  Table targetTable = new Table(targetDatabase, entityName, schemaName);
  targetTable.Refresh();

  int index = 0;
#>
BEGIN TRANSACTION;
BEGIN TRY
MERGE [<#= targetDbName #>].[<#= schemaName #>].[<#= entityName #>] AS [Target]
  USING [<#= sourceDbName #>].[<#= schemaName #>].[<#= entityName #>] AS [Source]
    ON
    (
      /* join on the primary key column(s) */
<#
  PushIndent("\t\t\t");
  index = 0;
  foreach (Column column in targetTable.Columns)
  {
    if (column.InPrimaryKey)
    {
      if (index > 0)
        Write("AND ");
      WriteLine("[Target].[" + column.Name + "] = [Source].[" + column.Name + "]");
      index++;
    }
  }
  PopIndent();
#>
    )
  WHEN MATCHED
    AND
    (
      /* and compare all non-key column(s) for inequality */
<#
  PushIndent("\t\t\t");
  // This compares all the values to look for update-ability
  // which can slow down the merge. If you have an update date
  // or something like it in your schema, you can check for its
  // existence here and use that to make the merge efficient.
  index = 0;
  foreach (Column column in targetTable.Columns)
  {
    if (!column.InPrimaryKey)
    {
      if (index > 0)
        Write("OR ");
      WriteLine("[Target].[" + column.Name + "] <> [Source].[" + column.Name + "]");
      index++;
    }
  }
  PopIndent();
#>
    )
    THEN UPDATE SET
<#
  PushIndent("\t\t\t");
  index = 0;
  foreach (Column column in targetTable.Columns)
  {
    if (!column.InPrimaryKey)
    {
      if (index > 0)
        Write(", ");
      WriteLine("[Target].[" + column.Name + "] = [Source].[" + column.Name + "]");
      index++;
    }
  }
  PopIndent();
#>
  WHEN NOT MATCHED BY TARGET
    THEN INSERT
    (
<#
  PushIndent("\t\t\t");
  index = 0;
  foreach (Column column in targetTable.Columns)
  {
    if (index > 0)
      Write(", ");
    WriteLine("[" + column.Name + "]");
    index++;
  }
  PopIndent();
#>
    )
    VALUES
    (
<#
  PushIndent("\t\t\t");
  index = 0;
  foreach (Column column in targetTable.Columns)
  {
    if (index > 0)
      Write(", ");
    WriteLine("[Source].[" + column.Name + "]");
    index++;
  }
  PopIndent();
#>
    )
  WHEN NOT MATCHED BY SOURCE
    /* are you sure you want to delete? */
    /* this could be converted to a logical delete */
    /* by using an update on the target instead */
    THEN DELETE
  OUTPUT
    $action AS [Operation]
<#
  PushIndent("\t\t");
  foreach (Column column in targetTable.Columns)
  {
    if (column.InPrimaryKey)
      WriteLine(", Inserted.[" + column.Name + "] AS [Merged" + column.Name + "]");
  }
  PopIndent();
#>
<#
  PushIndent("\t\t");
  index = 0;
  foreach (Column column in targetTable.Columns)
  {
    if (index > 0)
      WriteLine("");
    if (column.InPrimaryKey)
      Write(", Deleted.[" + column.Name + "] AS [Deleted" + column.Name + "]");
  }
  WriteLine(";");
  PopIndent();
#>
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
  BEGIN
    ROLLBACK TRANSACTION;
    PRINT N'The transaction was rolled back because the merge failed.';
  END
  PRINT N'Error number = ' + CONVERT(NCHAR(10),ERROR_NUMBER());
  PRINT N'Error severity = ' + CONVERT(NCHAR(10),ERROR_SEVERITY());
  PRINT N'Error state = ' + CONVERT(NCHAR(10),ERROR_STATE());
  PRINT N'Error procedure = ' + ERROR_PROCEDURE();
  PRINT N'Error line = ' + CONVERT(NCHAR(10),ERROR_LINE());
  PRINT N'Error message = ' + ERROR_MESSAGE();
END CATCH

IF @@TRANCOUNT > 0
BEGIN
  COMMIT TRANSACTION;
  PRINT N'The transaction was committed with no errors.';
END

In the pre-amble, I specified that C# will be the scripting language and that we’re going to emit SQL code from it. Next, some assembly directives are used to load referenced assemblies into the script generator’s context. The three (3) SMO assemblies referenced here are expected to be in the GAC but they could optionally be placed in the script engine’s private assembly path, too. In a future post, I’ll show you how to resolve assemblies in a much more predictable way. For now, let’s depend on the GAC.

Next comes an import directive. This is akin to the Import keyword in Visual Basic or the using declaration in C#. Notice that I said using declaration, not using statement because in C# the using keyword can be used in both ways. A using declaration in C# is used to import the objects from a specified namespace. And that’s what I’m asking T4’s scripting engine to do here. I want to import the Microsoft.SqlServer.Management.Smo namespace for use throughout the script that follows.

The remainder of the script is T-SQL code with bits of C# script sprinkled about using <# (or <#=) and #> tags. T4’s scripting engine will compile and execute the script from top to bottom, injecting literal T-SQL text and evaluating the C# expressions and injecting their output as well. The output, which shows up under the TT file in Visual Studio’s Solution Explorer looks like this:

BEGIN TRANSACTION;
BEGIN TRY
MERGE [ReplicatedNorthwind].[dbo].[Products] AS [Target]
  USING [Northwind].[dbo].[Products] AS [Source]
    ON
    (
      /* join on the primary key column(s) */
      [Target].[ProductID] = [Source].[ProductID]
    )
  WHEN MATCHED
    AND
    (
      /* and compare all non-key column(s) for inequality */
      [Target].[ProductName] <> [Source].[ProductName]
      OR [Target].[SupplierID] <> [Source].[SupplierID]
      OR [Target].[CategoryID] <> [Source].[CategoryID]
      OR [Target].[QuantityPerUnit] <> [Source].[QuantityPerUnit]
      OR [Target].[UnitPrice] <> [Source].[UnitPrice]
      OR [Target].[UnitsInStock] <> [Source].[UnitsInStock]
      OR [Target].[UnitsOnOrder] <> [Source].[UnitsOnOrder]
      OR [Target].[ReorderLevel] <> [Source].[ReorderLevel]
      OR [Target].[Discontinued] <> [Source].[Discontinued]
    )
    THEN UPDATE SET
      [Target].[ProductName] = [Source].[ProductName]
      , [Target].[SupplierID] = [Source].[SupplierID]
      , [Target].[CategoryID] = [Source].[CategoryID]
      , [Target].[QuantityPerUnit] = [Source].[QuantityPerUnit]
      , [Target].[UnitPrice] = [Source].[UnitPrice]
      , [Target].[UnitsInStock] = [Source].[UnitsInStock]
      , [Target].[UnitsOnOrder] = [Source].[UnitsOnOrder]
      , [Target].[ReorderLevel] = [Source].[ReorderLevel]
      , [Target].[Discontinued] = [Source].[Discontinued]
  WHEN NOT MATCHED BY TARGET
    THEN INSERT
    (
      [ProductID]
      , [ProductName]
      , [SupplierID]
      , [CategoryID]
      , [QuantityPerUnit]
      , [UnitPrice]
      , [UnitsInStock]
      , [UnitsOnOrder]
      , [ReorderLevel]
      , [Discontinued]
    )
    VALUES
    (
      [Source].[ProductID]
      , [Source].[ProductName]
      , [Source].[SupplierID]
      , [Source].[CategoryID]
      , [Source].[QuantityPerUnit]
      , [Source].[UnitPrice]
      , [Source].[UnitsInStock]
      , [Source].[UnitsOnOrder]
      , [Source].[ReorderLevel]
      , [Source].[Discontinued]
    )
  WHEN NOT MATCHED BY SOURCE
    /* are you sure you want to delete? */
    /* this could be converted to a logical delete */
    /* by using an update on the target instead */
    THEN DELETE
  OUTPUT
    $action AS [Operation]
    , Inserted.[ProductID] AS [MergedProductID]
    , Deleted.[ProductID] AS [DeletedProductID];
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
  BEGIN
    ROLLBACK TRANSACTION;
    PRINT N'The transaction was rolled back because the merge failed.';
  END
  PRINT N'Error number = ' + CONVERT(NCHAR(10),ERROR_NUMBER());
  PRINT N'Error severity = ' + CONVERT(NCHAR(10),ERROR_SEVERITY());
  PRINT N'Error state = ' + CONVERT(NCHAR(10),ERROR_STATE());
  PRINT N'Error procedure = ' + ERROR_PROCEDURE();
  PRINT N'Error line = ' + CONVERT(NCHAR(10),ERROR_LINE());
  PRINT N'Error message = ' + ERROR_MESSAGE();
END CATCH

IF @@TRANCOUNT > 0
BEGIN
  COMMIT TRANSACTION;
  PRINT N'The transaction was committed with no errors.';
END

If you have a Northwind database locally and another database locally named ReplicatedNorthwind that contains a Products table with the correct schema, the resulting SQL script will merge changes that occur in the Northwind Products table to the other table. Try inserting a product, updating a product and deleting a product in the source Products table and running the merge script. You’ll see that all three (3) changes are detected and executed for you to synchronize the tables. If you don’t have Northwind databases handy, try modifying the T4 script to work with two other databases of your choosing instead. The only real requirement is that the target table’s schema has to be a subset of the source table’s schema to function correctly.

I won’t dig into all the particulars of how T-SQL MERGE works. It’s a fairly clean syntax and there are plenty of resources on the net for learning about that. What I wanted to make clear in this post is how simple the code generation process is when using T4. You can see how simple it is to iterate over the targetTable.Columns collection and evaluate each Column object’s InPrimaryKey property to build up the MERGE statement.

In my next post concerning the T4 code generator, I’ll show you how to pass parameters to the T4 script by implementing the ITextTemplatingEngineHost and ITextTemplatingSessionHost interfaces. In that post, I'll build one metaprogramming technique on another to create a customized script generator that can generically MERGE one entire database to another.