Invoking Stored Procedures generating C# Code using T4 Templates

February 5, 2013

When developing database applications, I usually start out with the database schema and have the necessary C# code (data classes, procedure calls, application-specific constants) generated by a couple of stored procedures. For my own projects, I create a batch file to build the various parts of the solution, all the way from code generation to publishing the application.

Maybe not everybody shares my preference for building-by-batch, so I was looking for an alternative way to invoke code generation in stored procedures, and I remembered working on a project a couple of years ago that used T4 Text Templates to generate C# code.

The first component of the T4 solution is MultipleOutputHelper.ttinclude which implements features such saving only changed files, handling check-out if connected to TFS, and much more.

Since I want to access the connection strings stored in web.config or app.config, I searched for sample code and found ConfigurationAccessor.tt.

The final T4 Template looks like this. The header includes both libraries and declares .Net namespaces:

<#@ template debug="True" hostspecific="True" language="C#" 
#><#@ output extension=".cs"
#><#@ include file="MultipleOutputHelper.ttinclude"
#><#@ include file="ConfigurationAccessor.ttinclude"
#><#@ assembly name="System.Data"
#><#@ import namespace="System.Data"
#><#@ import namespace="System.Data.SqlClient"
#><#

Next, we instantiate the objects declared in both libraries

  var manager = Manager.Create(Host, GenerationEnvironment); 
  manager.StartNewFile("Classes.generated.cs"); 

  var config = new ConfigurationAccessor((IServiceProvider)this.Host);
  var connStrEntry = config.ConnectionStrings["default"];

Depending on your build process, you may want to cause database errors (connection string, connectivity, execution) to raise either warnings or compiler errors:

  var warnings = true;
  var errors = new List<string>();

  if (connStrEntry == null)
  {
    errors.Add("Connection string named 'default' not found");
  }
  else
  {
    var connStr = connStrEntry.ConnectionString;

After checking the connection string, let’s connect to the database and execute the stored procedure.

The output of PRINT commands is retrieved using the InfoMessage event:

    try
    { 
      using (var conn = new SqlConnection(connStr))
      {
        conn.Open();
        conn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
        {                                    
            this.WriteLine(e.Message);
        };

        SqlCommand cmd = new SqlCommand("dev_Generate_DAL_Classes", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
      }
    }
    catch(Exception ex)
    {
      errors.Add(ex.Message);
    }
  }

  manager.EndBlock();

If no errors occurred, save the generated file. Otherwise, either raise warnings or errors.

  if (errors.Count == 0)
  {
    manager.Process(true); 
  }
  else 
  {
    foreach(var error in errors)
    {
      if (warnings)
        this.Warning(Host.TemplateFile + ": " + error);
      else
        this.Error(Host.TemplateFile + ": " + error);
    }
  }
#>

I originally tried to store warnings and errors using the Host.LogErrors method. However, no matter which Error Code I set in CompilerError, the result would always be errors rather than warnings. Using the TextTransformation‘s Warning() and Error() methods did the trick.


Limitations of the T-SQL PRINT Command

October 21, 2012

One of the main functions of dbscript is to allow the user to select a set of tables and columns. This selection is used to generate T-SQL code (either a set of statements or the definition of a stored procedure) which can then be executed on a live database to generate INSERT/UPDATE statements reproducing the values of the selected tables and columns. An earlier post illustrated this functionality.

While this works in principle (at least for MS SQL Server), there are restrictions in the T-SQL command that make live harder than necessary if you want to script long text or binary values (NVARCHAR(MAX), VARBINARY(MAX):

During research I found a couple of work-arounds for the 4000/8000 byte limit:

  • stored procedure LongPrint on ask.sqlservercentral
  • stored procedure LongPrint on Adam Anderson’s blog

Both procedures try to find a line break in the string to be output, split the string at the line breaks and prints the substrings accordingly. However, from looking at the code, the procedures only work correctly if there is a line break found within 4000 characters, which is a problem if

  • you have a list of column values without line breaks (in case of dbscript: INSERT and UPDATE values)
  • you want to output binary values whose hexadecimal representation is longer than 4000 characters

With all these restrictions in place, I considered some other solutions:

The sp_executesql solution is necessary because scripts should be generated without any requirements in the target databases. The idea is to encapsulate a version of LongPrint in a TSQL variable, and invoke this code using sp_executesql and passing the string to be output.

Preparing this blog I came to the solution that the current implementation is good enough for most cases. In cases where output length exceeds certain limits, or for databases that enforce similar limits or do not support outputting text at all, a generic C# implementation in the dbscript backend may be the best solution.

To be continued… ūüėČ


dbscript 1.04 released

October 21, 2012

The code for the latest version of dbscript has been sitting around for a while, and I did not find the time to release it until a recent bug report forced encouraged me to finally publish it. What’s more, I promised to provide an update!

So what’s new in dbscript 1.04?

Architectural Cleanup

Up to dbscript 1.03, every new supported database engine and every new object type stored their values in either base columns or object type-specific columns. The object type-specific columns are now mapped onto the same columns in the Object table (using some meta data tables, and an internal code generator which also creates the NHibernate classes).

New functionality

Dependency Diagrams (based on T-SQL analysis of views, stored procedures and functions)

namespace parameter for C# and VB.Net value scripts

Internal Cleanup

Recognize sql server 2008 and 2008R2

Multi-comparison is performed in C# rather than TSQL, reducing the chance of a TSQL timeout, and allows adding/removing project versions after a calculation has been performed.

XmlSchemaCollections are treated as types instead of objects

UI Cleanup

A couple of UI inconsistences have been fixed

Compare Object HTML-escapes correctly

Administrators can delete projects

MediaWiki login

dbscript user Mark made pointed out that dbscript does not work with current versions of MediaWiki anymore. A bit of research found that the login form adds a new hidden parameter wpLoginToken which needs to be submitted in the login POST.

dbscript 1.04 has been modified to provide this parameter, and hopefully this fix solves the issue for everybody having problems generating MediaWiki documentation of their databases.

As always, the latest version of dbscript is available for download here.


Generating Views for MS SQL Server Constants

August 28, 2012

Nearly 3 years ago I described how to store application constants in SQL Server views so that T-SQL code can be checked against these constants be referring view columns.

Now a user on SO asks how to generate this view definition on the fly.

Well, I am not sure whether a view defined as SELECT PIVOT (sample) can be used to verify depending T-SQL code.

What you can do, however, is to re-create the view once the content of the base table changes.

For example, take a table defining the visibility status of a blog post:

OID ProgID
1 Published
2 Draft
3 Deleted

We can now define a CTE statement that generates the CREATE VIEW statement for this table like this:

WITH vv AS (
SELECT ROW_NUMBER() OVER (ORDER BY OID) AS r, OID, ProgID
  FROM [Visibility]
), 
v AS (
SELECT 1 AS order1, NULL AS order2, 'CREATE VIEW V_Visibility AS' AS code
UNION ALL
SELECT 2, r, 
    CASE WHEN r = 1 THEN N'SELECT ' ELSE ', ' END 
    + CONVERT(NVARCHAR, OID) + N' AS ' + ProgID FROM vv
UNION ALL
SELECT 3, NULL, 'GO'
)
SELECT code FROM v
ORDER BY order1, order2

which returns the following result set:

CREATE VIEW V_Visibility AS
SELECT 1 AS Published
, 2 AS Draft
, 3 AS Deleted
GO

Using aggregate string concatenation, we can modify the final SELECT statement to read like this:

DECLARE @SQL NVARCHAR(MAX) = ''

; WITH vv AS ( 
... [as above]
)
SELECT @sql = @sql + CHAR(13) + code FROM v
ORDER BY order1, order2

PRINT @sql

resulting in

CREATE VIEW V_Visibility AS
SELECT 1 AS Published
, 2 AS Draft
, 3 AS Deleted
GO

Database Changes Done Right

March 12, 2012

I love reading The Daily WTF dealing with programmers’ stories, experiences, and the occasional code nightmare. While it’s fun to read, it also makes you aware of how easily you can mistakes yourself, especially in an unfamiliar programming language, even after years of programming experience.

A recent article was titled Database Changes Done Right, and I was surprised that it was not fun or horror story, but rather a serious article on database change management, along with a couple of rules to watch when implementing database schema changes (yes, those ugly dev – test – production stages).

I also felt connected with the topic of the article, because for a couple of years now, I develop software that should help other developers ease this burden of managing database changes:

dbscript (blog, website, download) is a web application that stores database versions in repository (i.e. MSSQL database), calculates schema change scripts, stores definitions of table value initialization scripts and C# constant declarations, and keeps track of product version at customer installations. dbscript focuses on MS SQL Server databases in these areas, but also supports Oracle and PostgreSql databases for documentation purposes: document your database schema in a wiki or in a Word HTML file, or generate a data diagram with just a couple of mouse clicks.

SMOscript (blog, website, download) is a command-line tool that generates SQL scripts for all objects in a MSSQL database (tables, views, stored procedures, etc) to a single file, or to one file per object. These generates scripts can be kept in directories covered by source control systems (SVN, TFS, etc), and thus SMOscript simplifies storing the scripts for each database version in a source code repository.

oraddlscript (blog, website, download) is a command-line tool similar to SMOscript, but covering Oracle databases.

checktsql (blog, website, download) is a Windows tool that verifies all objects in a database (views, functions, stored procedures) by executing them or selecting from them (each in a transaction that will be rolled back), and reporting any errors that occurred during their invocation.

This product summary does not cover the complete functionality of each program. As programs evolve, they gain a lot of functionality, as users (including me!) add requirements and contribute ideas. Feel free to add yours!


MSSQL Legacy Data Types in NHibernate.Mapping.ByCode.Conformist

February 24, 2012

Trying to migrate the code generation of NHibernate data classes and mappings from 2.1.2 to 3.2 and from Fluent to Mapping ByCode Conformist, I noticed a couple of problems relating mostly to non-Unicode string fields and legacy data types such as image and ntext.

Typically, column mapping uses the Property() method for columns like to NVARCHAR column:

public partial class Application_Map: ClassMapping<Application>
{
  public Application_Map()
  {
    Property(x => x.ID, map =>
      {
        map.Column("ID");
        map.NotNullable(true);
        map.Length(100);
      });
  }
}

This kind of declaration raised a HibernateException for VARCHAR columns  in configuration.BuildSessionFactory()

Wrong column type in MyDatabase.dbo.Foo for column Bar. Found: varchar, Expected NVARCHAR(256)

To fix this exception, the varchar column needs to be declared like this:

  Property(x => x.VarcharFoo, map =>
    {
      map.Column(c =>
        {
          c.Name("VarcharFoo");
          c.SqlType("varchar");
          c.Length(50);
        });
    });

Similarly, we can declare the native SQL data type for image and ntext columns:

  Property(x => x.FooImage, map =>
    {
      map.Column(c =>
        {
          c.Name("FooImage");
          c.SqlType("image");
        });
      map.Lazy(true);
    });
  Property(x => x.FooNText, map =>
    {
      map.Column(c =>
        {
          c.Name("FooNText");
          c.SqlType("ntext");
        });
    });


Generating NHibernate Access to MSSQL Stored Procedures

February 23, 2012

This series describes the generation of C# classes and database access for NHibernate 2.1.2GA.

To store the information on which Stored Procedures to script out, we need a table Meta_Procedure, similar to Meta_Table presented earlier.

CREATE TABLE [dbo].[Meta_Procedure](
  [OID] [int] IDENTITY(1,1) NOT NULL,
  [ID] [nvarchar](50) NOT NULL,
  [UseDAL] [bit] NOT NULL,
  CONSTRAINT [PK_Meta_Procedure] PRIMARY KEY CLUSTERED
  ( [OID] ASC )
)

We fill this table from the list of stored procedures stored in sys.objects:

INSERT INTO Meta_Procedure (ID)
SELECT o.name
FROM sys.objects o
LEFT OUTER JOIN Meta_Procedure t ON o.name = t.id
WHERE o.type IN ('P')
AND t.OID IS NULL;

The developer now only needs to set the UseDAL field to true (1) for each procedure to be scripted.

Declaration of stored procedures for NHibernate consists of two parts: a .hbm.xml containing named queries with a TSQL statement to invoke each stored procedure, which is compiled as Embedded Resource:

CREATE PROCEDURE [dbo].[dev_Generate_DAL_HBM_XML] AS

  PRINT '<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    assembly="My.Project" namespace="My.Project.DAL">'
  
  DECLARE @oid INT, @id NVARCHAR(50)

  DECLARE cT CURSOR FOR
  SELECT object_id, name
  FROM sys.procedures p
  INNER JOIN Meta_Procedure mp ON p.name = mp.ID
  WHERE mp.UseDAL = 1
  ORDER BY name

  OPEN cT
  FETCH cT INTO @oid, @id

  WHILE @@FETCH_STATUS = 0 BEGIN
    DECLARE @cid NVARCHAR(50), @datatype NVARCHAR(50), 
      @isoutput BIT, @parameterid INT
    
    DECLARE cP cursor FOR
    SELECT  REPLACE(p.name, '@', ''), t.name, p.is_output, p.parameter_id
    FROM  sys.parameters p
    INNER JOIN sys.types t ON p.user_type_id = t.user_type_id
    WHERE   p.object_id = @oid
    ORDER BY p.parameter_id

    PRINT '    <sql-query name="' + @id + '" callable="true">'

    OPEN cP
    FETCH cP INTO @cid, @datatype, @isoutput, @parameterid

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar' 
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime?'
      ELSE
        SET @datatype = @datatype + '?'

      PRINT '        <query-param name="' + @cid + '" type="' + @datatype + '" />'
      
      FETCH cP INTO @cid, @datatype, @isoutput, @parameterid
    END

    CLOSE cP

    PRINT 'begin'
    PRINT '    execute ' + @id 
    
    OPEN cP
    FETCH cP INTO @cid, @datatype, @isoutput, @parameterid

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar' 
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime?'
      ELSE
        SET @datatype = @datatype + '?'

      IF @parameterid = 1
        PRINT '        :' + @cid
      ELSE BEGIN
        PRINT '        , :' + @cid
      END
      
      FETCH cP INTO @cid, @datatype, @isoutput, @parameterid
    END
    CLOSE cP

    DEALLOCATE cP
    
    PRINT 'end'
    PRINT '    </sql-query>'
    
    FETCH cT into @oid, @id
  END

  CLOSE cT
  DEALLOCATE cT

  PRINT '</hibernate-mapping>'

For each stored procedure, we need a C# method which calls the corresponding named query. The SP’s parameters are retrieved from the sys.parameters catalog view, and their data types translated just as we handled table columns.

CREATE PROCEDURE [dbo].[dev_Generate_DAL_Procedures] AS

  PRINT 'using System;'
  PRINT 'using System.Collections;'
  PRINT 'using NHibernate;'
  PRINT ''
  PRINT 'namespace My.Project.DAL'
  PRINT '{'
  PRINT '    public static class Procedures'
  PRINT '    {'
  
  DECLARE @oid INT, @id NVARCHAR(50)

  DECLARE cT CURSOR FOR
  SELECT object_id, name
  FROM sys.procedures p
  INNER JOIN Meta_Procedure mp ON p.name = mp.ID
  WHERE mp.UseDAL = 1
  ORDER BY name

  OPEN cT
  FETCH cT INTO @oid, @id

  WHILE @@FETCH_STATUS = 0 BEGIN
    DECLARE @cid NVARCHAR(50), @datatype NVARCHAR(50), @isoutput BIT
    
    DECLARE cP cursor FOR
    SELECT  REPLACE(p.name, '@', ''), t.name, p.is_output
    FROM  sys.parameters p
    INNER JOIN sys.types t ON p.user_type_id = t.user_type_id
    WHERE   p.object_id = @oid
    ORDER BY p.parameter_id

    PRINT '        public static IList ' + @id + '(ISession session'

    OPEN cP
    FETCH cP INTO @cid, @datatype, @isoutput

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar' 
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime?'
      ELSE
        SET @datatype = @datatype + '?'

      PRINT '            , ' + 
        CASE WHEN @isoutput = 1 THEN 'out ' ELSE '' END + 
        @datatype + ' ' + @cid
      
      FETCH cP INTO @cid, @datatype, @isoutput
    END

    PRINT '            )'
    PRINT '        {'
    PRINT '            IQuery sp = session.GetNamedQuery("' + @id + '");'

    CLOSE cP

    OPEN cP
    FETCH cP INTO @cid, @datatype, @isoutput

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar' 
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime?'
      ELSE
        SET @datatype = @datatype + '?'

      IF @datatype = 'string'
        PRINT '            sp.SetParameter<' + @datatype + 
          '>("' + @cid + '", ' + @cid + ');'
      ELSE BEGIN
        PRINT '            sp.SetParameter<' + @datatype + 
          '>("' + @cid + '", ' + @cid + ');'
      END
      
      FETCH cP INTO @cid, @datatype, @isoutput
    END
    CLOSE cP

    DEALLOCATE cP
    
    PRINT '            return sp.List();'
    PRINT '        }'
    
    FETCH cT INTO @oid, @id
  END

  CLOSE cT
  DEALLOCATE cT

  PRINT '    }'
  PRINT '}'