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).
Posted by devio 
