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"