Generating NHibernate Classes for MSSQL in TSQL

This series describes the generation of C# classes and database access for NHibernate 2.1.2GA and FluentNHibernate 1.0. These are not the most current versions, but I need to document my existing code to prepare support of current versions of these libraries. I have previously written about NHibernate accessing Oracle in various post.

We generate the C# classes from the meta model contained in SQL Server system catalog views. However, generation does not directly access the catalog views, but caches information in meta tables that can hold additional information on tables and columns:

CREATE TABLE [dbo].[Meta_Table](
    [OID] [int] IDENTITY(1,1) NOT NULL,
    [ID] [nvarchar](50) NOT NULL,
    [TableType] [varchar](1) NOT NULL,
    [UseDAL] [bit] NOT NULL,
    CONSTRAINT [PK_Meta_Table] PRIMARY KEY CLUSTERED 
    ( [OID] ASC )
)
CREATE TABLE [dbo].[Meta_Column](
    [OID] [int] IDENTITY(1,1) NOT NULL,
    [Tbl_OID] [int] NOT NULL,
    [IsActive] [bit] NOT NULL,
    [Seq] [int] NOT NULL,
    [ID] [nvarchar](50) NOT NULL,
    [DataType] [varchar](50) NOT NULL,
    [Length] [int] NULL,
    [IsNullable] [bit] NOT NULL,
    [IsIdentity] [bit] NOT NULL,
    [LookupTable] [nvarchar](50) NULL,
    CONSTRAINT [PK_Meta_Column] PRIMARY KEY CLUSTERED 
    ( [OID] ASC )
) 

These tables are filled from the system catalog views using the following stored procedure

CREATE PROCEDURE [dbo].[dev_Retrieve_Meta] AS

  SET NOCOUNT ON;

  INSERT INTO Meta_Table (ID, TableType)
  SELECT o.name, o.type
  FROM sys.objects o
  LEFT OUTER JOIN Meta_Table t ON o.name = t.ID
  WHERE o.type IN ('V', 'U')
  AND t.OID IS NULL  ;

  UPDATE Meta_Column SET IsActive = 0;

  INSERT INTO Meta_Column (Tbl_OID, Seq, ID, DataType)  
  SELECT t.OID, c.clumn_id, c.name, ty.name 
  FROM sys.objects o
  INNER JOIN Meta_Table t ON o.name = t.ID
  INNER JOIN sys.columns c ON o.object_id = c.object_id
  INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
  LEFT OUTER JOIN Meta_Column mc 
    ON mc.Tbl_OID = t.OID AND mc.ID = c.name
  WHERE mc.OID IS NULL;

  UPDATE Meta_Column
  SET Seq = c.column_id, 
    DataType = ty.name, 
    Length = 
      CASE WHEN ty.name IN ('varchar', 'nvarchar') 
          AND c.max_length > -1 
        THEN c.max_length 
        ELSE NULL 
      END,
    IsNullable = c.is_nullable, 
    IsIdentity = c.is_identity,
    LookupTable = lt.name,
    IsActive = 1
  FROM Meta_Column mc
  INNER JOIN meta_table t ON mc.Tbl_OID = t.OID 
  INNER JOIN sys.objects o ON o.name = t.ID
  INNER JOIN sys.columns c 
    ON o.object_id = c.object_id AND mc.ID = c.name
  INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
  LEFT OUTER JOIN sys.foreign_key_columns fkc 
    ON fkc.parent_object_id = o.object_id 
    AND fkc.parent_column_id = c.column_id
  LEFT OUTER JOIN sys.objects lt 
    ON fkc.referenced_object_id = lt.object_id

The first INSERT statement retrieves all tables and views which are not already stored in the Meta_Table table.

Next, the new column names are retrieved from the sys.columns catalog view, and the columns’ IsActive field is set to false.

Finally, the UPDATE Meta_Column retrieves the column attributes from the catalog view, and sets the IsActive field to true for all columns still present in the catalog view (this is necessary to deal with changes in the data model).

2 Responses to Generating NHibernate Classes for MSSQL in TSQL

  1. […] retrieving the necessary meta data on tables and columns, we can now generate the C# classes for NHibernate and the necessary mapping […]

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: