Is it possible to set a SQL server columns' description using a fluent nhibernate map?

Question

I am using my fluent nhibernate mappings to generate my MS SQL Server database.

I would like to be able to set a columns' description as part of this generation.

Answer 1

No, it isn't possible. Description is a Microsoft specific meta-data attribute, and both NHibernate and Fluent NHibernate try to remain as database agnostic as possible.

You might be able to do it using a SqlInsert mapping, but it won't be very nice.

Answer 2

It is not impossible, but there is no such API provided by Fluent NHibernate nor NHibernate itself.

You'd have to write some code to examine the resulting database structure after NHibernate created your database and then write good old SQL statements to set the descriptions yourself.

Perhaps you could create a Fluent NHibernate "convention" which logs what tables are created and what columns are there and then you could easily write the code that sets the descriptions manually.
(I'll write some code for you, if you require.)

Answer 3

If you really need this, you could map the columns descriptions to a "holder" table which would store the descriptions for saving/loading. This holder table will need a column for SchemaName, TableName, ColumnName, and Description. Add a TRIGGER on this table, which will execute SQL Server's sp_addextendedproperty (Transact-SQL) or sp_dropextendedproperty (Transact-SQL) command to add/drop the description to/from the proper schema.table.column.

the table would be like:

DescriptionHolder
SchemaName   sysname
TableName    sysname
ColumnName   sysname
Description  varchar(7500) --or nvarchar(3750)

the trigger would be like:

CREATE TRIGGER trigger_DescriptionHolder ON DescriptionHolder
   INSTEAD OF INSERT,UPDATE,DELETE
AS 
SET NOCOUNT ON


IF EXISTS (SELECT * FROM INSERTED)
BEGIN
    --handles INSERTS and UPDATEs
    --loop begin here over INSERTED
        EXECUTE sp_addextendedproperty N'MS_Description', <INSERTED.Description>
            ,N'SCHEMA' , <INSERTED.SchemaName>
            ,N'TABLE'  , <INSERTED.TableName>
            ,N'COLUMN' , <INSERTED.ColumnName>
    --loop end here


END
ELSE IF EXISTS(SELECT * FROM DELETED)
BEGIN
    --handles DELETEs
    --loop begin here over DELETED
    EXECUTE sp_dropextendedproperty ...
    --loop end here

END

If you're worried about getting the table out of sync with the actual column descriptions, you could add this onto the end of the trigger:

IF EXISTS (SELECT 1 AS x --h.SchemaName,h.TableName,h.ColumnName
               FROM DescriptionHolder h 
               WHERE NOT EXISTS (SELECT 1  
                                     from sys.extended_properties p
                                         inner join sys.objects   o ON p.major_id=o.object_id
                                         inner join sys.schemas   s ON o.schema_id=s.schema_id
                                         inner join sys.columns   c ON o.object_id=c.object_id and p.minor_id=c.column_id
                                     where h.SchemaName=s.name AND h.TableName=o.name AND h.ColumnName=c.name)
           UNION ALL
           select 2 AS x --s.name AS SchemaName,o.name AS TableName,c.name AS ColumnName
               from sys.extended_properties p
                   inner join sys.objects   o ON p.major_id=o.object_id
                   inner join sys.schemas   s ON o.schema_id=s.schema_id
                   inner join sys.columns   c ON o.object_id=c.object_id and p.minor_id=c.column_id
               where p.class=1 and p.Name='MS_Description'
                   AND not exists (SELECT 1 FROM DescriptionHolder h WHERE s.name=h.SchemaName AND o.name=h.TableName AND c.name=h.ColumnName)
          )
    BEGIN
        RAISERROR('sys.extended_properties and DescriptionHolder do not match',16,1)
        ROLLBACK
        RETURN
    END

this code will rollback and abort the trigger if the DescriptionHolder is not 100% in sync with the actual column descriptions in the database.