Invoking Stored Procedures generating C# Code using T4 Templates

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

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

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

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

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

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

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 '}'

Generating NHibernate Classes for MSSQL in TSQL (2)

After retrieving the necessary meta data on tables and columns, we can now generate the C# classes for NHibernate and the necessary mapping information.

CREATE PROCEDURE [dbo].[dev_Generate_DAL_Classes] AS

  SET NOCOUNT ON

  PRINT 'using System;'
  PRINT 'using System.Collections.Generic;'
  PRINT 'using System.Linq;'
  PRINT 'using System.Text;'
  PRINT 'using System.ComponentModel;'
  PRINT 'using System.Runtime.Serialization;'
  PRINT 'using NHibernate.Validator.Constraints;'
  PRINT ''
  PRINT 'namespace My.Project.DAL'
  PRINT '{'

  DECLARE @oid INT, @id NVARCHAR(50), @tabletype NVARCHAR(1)

  DECLARE cT CURSOR FOR
  SELECT OID, ID, TableType
  FROM Meta_Table
  WHERE UseDAL = 1
  ORDER BY ID

  OPEN cT
  FETCH cT INTO @oid, @id, @tabletype

  WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT '    public partial class ' + @id
    PRINT '    {'

    DECLARE @cid NVARCHAR(50), @datatype NVARCHAR(50), @length INT,
      @isnullable BIT, @isidentity BIT, @lookup NVARCHAR(50)

    DECLARE cC CURSOR FOR
    SELECT ID, DataType, Length, IsNullable, IsIdentity, LookupTable
    FROM   Meta_Column
    WHERE  Tbl_OID = @oid
    AND    IsActive = 1
    ORDER BY Seq

    OPEN cC
    FETCH cC INTO @cid, @datatype, @length, @isnullable,
      @isidentity, @lookup

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar'
        SET @datatype = 'string'
      ELSE IF @datatype = 'varchar'
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime'
      ELSE IF @datatype = 'datetime'
        SET @datatype = 'DateTime'
      ELSE IF @datatype = 'bit'
        SET @datatype = 'bool'
      ELSE IF @datatype = 'varbinary' BEGIN
        SET @datatype = 'byte[]'
        SET @isnullable = 0
      END

      IF @isidentity = 1 OR @cid = 'OID'
        PRINT '        public virtual ' + @datatype + ' '
          + @cid + ' { get; private set; }'
      ELSE IF @lookup IS NOT NULL BEGIN
        IF @isnullable = 0
          PRINT '        [NotNull]'
        PRINT '        public virtual ' + @lookup + ' '
          + REPLACE(@cid, '_OID', '') + ' { get; set; }'
      END ELSE BEGIN
        IF @datatype = 'string' BEGIN
          IF @isnullable = 0
            PRINT '        [NotNullNotEmpty]'
          IF @length IS NOT NULL
            PRINT '        [Length('
              + CONVERT(NVARCHAR, @length) + ')]'
        END ELSE
          IF @isnullable = 1
            SET @datatype = @datatype + '?'

        PRINT '        public virtual ' + @datatype + ' '
          + @cid + ' { get; set; }'
      END
      FETCH cC INTO @cid, @datatype, @length, @isnullable,
        @isidentity, @lookup
    END

    CLOSE cC
    DEALLOCATE cC

    PRINT '    }'

    FETCH cT INTO @oid, @id, @tabletype
  END

  CLOSE cT
  DEALLOCATE cT

  PRINT '}'

This stored procedure generates a C# class for each of the tables with the UseDAL flag set to true by the developer. For the columns, the SQL Server data types need to be translated into C# (i.e. .Net data types)

And now for the Class Maps

CREATE PROCEDURE [dbo].[dev_Generate_DAL_ClassMaps] AS

  SET NOCOUNT ON

  PRINT 'using System;'
  PRINT 'using System.Collections.Generic;'
  PRINT 'using System.Linq;'
  PRINT 'using System.Text;'
  PRINT 'using FluentNHibernate.Mapping;'
  PRINT 'using NHibernate.Validator.Constraints;'
  PRINT ''
  PRINT 'namespace My.Project.DAL'
  PRINT '{'

  DECLARE @oid INT, @id NVARCHAR(50), @tabletype NVARCHAR(1)

  DECLARE cT CURSOR FOR
  SELECT OID, ID, TableType
  FROM Meta_Table
  WHERE UseDAL = 1
  ORDER BY ID

  OPEN cT
  FETCH cT INTO @oid, @id, @tabletype

  WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT '    public partial class ' + @id + '_Map: ClassMap<' + @id + '>'
    PRINT '    {'
    PRINT '        public ' + @id + '_Map()'
    PRINT '        {'
    PRINT '            Table("' + @id + '");'

    DECLARE @cid NVARCHAR(50), @datatype NVARCHAR(50), @length INT,
      @isnullable BIT, @isidentity BIT, @lookup NVARCHAR(50)

    DECLARE cC CURSOR FOR
    SELECT ID, DataType, Length, IsNullable, IsIdentity, LookupTable
    FROM  Meta_Column
    WHERE  Tbl_OID = @oid
    AND    IsActive = 1
    ORDER BY Seq

    OPEN cC
    FETCH cC INTO @cid, @datatype, @length, @isnullable,
      @isidentity, @lookup

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar'
        SET @datatype = 'string'
      ELSE IF @datatype = 'varchar'
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime'
      ELSE IF @datatype = 'datetime'
        SET @datatype = 'DateTime'
      ELSE IF @datatype = 'bit'
        SET @datatype = 'bool'
      ELSE IF @datatype = 'varbinary'
        SET @datatype = 'byte[]'

      IF @isidentity = 1
        PRINT '            Id(x => x.' + @cid + ').GeneratedBy.Identity();'
      ELSE IF @cid = 'OID'
        PRINT '            Id(x => x.' + @cid + ');'
      ELSE IF @cid = 'DateCreated'
        PRINT '            Map(x => x.' + @cid + ').Generated.Insert();'
      ELSE BEGIN
        IF @lookup IS NOT NULL BEGIN
          PRINT '            References(x => x.' +
              REPLACE(@cid, '_OID', '') + ', "' + @cid + '")' +
            CASE WHEN @isnullable = 0 THEN '.Not.Nullable()' ELSE '' END +
            '.LazyLoad();'
        END ELSE BEGIN
          PRINT '            Map(x => x.' + @cid + ')' +
            CASE WHEN @isnullable = 0 THEN '.Not.Nullable()' ELSE '' END +
            ';'
        END
      END
      FETCH cC INTO @cid, @datatype, @length, @isnullable,
        @isidentity, @lookup
    END

    CLOSE cC
    DEALLOCATE cC

    PRINT '        }'
    PRINT '    }'

    FETCH cT INTO @oid, @id, @tabletype
  END

  CLOSE cT
  DEALLOCATE cT

  PRINT '}'

These stored procedures can be invoked from the command line using the SQL Server osql tool

set osql=osql -S localhost -U [user] -P [pwd] -d [database] -n -w 1000
%osql% -Q "exec dev_Generate_DAL_Classes" -o "d:\path\to\Classes.cs"
%osql% -Q "exec dev_Generate_DAL_ClassMaps" -o "d:\path\to\ClassMaps.cs"

SMOscript 0.19

There has been a lot of feedback on SMOscript 0.18, and some feature requests are now implemented.

The “script objects” command adds the options “-use” to add a USE [database] command to the generated file, and -fa option to append to existing files.

The “list database” command adds filters for the status of databases: system database, read-only, offline.

Instead of passing the login information using the switches -s, -d, -u, -p, you can now pass a connection string literal (such as used in web.config or app.config) using the -c switch.

New commands  have been added as well:

  • List object dependencies
  • Find string literals
  • Find strings

If an error or exception occurs, the ERRORLEVEL variable will be set (1 for parser errors, 2 to execution errors).

The complete help screen displays like this:

smoscript 0.19.4194.20191 (c) by devio.at 2008-2011

    list and script databases and database objects.

    usage: smoscript [options (leading '-' or '/')] [command]

    connection options:

    -s server       server name
    -d database     database name
    -u username     username (default: integrated authentication)
    -p password     password (if -u is missing, password for sa)
    -c connection   connection string (sets -s, -d, -u, -p)

    scripting options:

    -o [schema.]object  object name

    -r              generate DROP statements
    -i              include IF NOT EXISTS statements
    -use            generate USE [database]

    -f filename     output to file
    -fa             append to file if exists
    -F directory    output to directory

    listing options:

    -l[x]a          list [in]accessible databases
    -l[x]ro         list databases [not] read-only
    -l[x]sys        list [non-] system databases
    -ls             list database status

    dependency options:

    -depth          max. recursion depth
    -dxt            exclude tables in result

    find options:

    -fx string      exclude this string literal (multi)
    -fxs string     exclude strings starting with this literal (multi)
    -fi             find string case-insensitive
    -fc             find string and display code

    output options:

    -A              current ANSI codepage
    -O              ASCII
    -T              Unicode
    -U              UTF8

    commands:

    l           list databases on server (implied by -s)
                list objects in database (implied by -s -d)
    s           script all objects (implied by -s -d -F/-f)
                script single object (implied by -s -d -o)
    db          list database properties
    dep         list object dependencies (-o)
    fs          find string literals
    f string    find string in modules

The latest version of SMOscript is available for download here.