How to get NHibernate SchemaExport to create SQL Server Timestamp columns?

Question

I'm using the NHibernate HBM2DDL SchemaExport tool to generate my database from my entity objects, and I want to use SQL Server Timestamp columns for optimisitic concurrency.

I've added properties to my entity object that look like this:

public virtual byte[] Timestamp { get; set; }

NHibernate will generate the Timestamp column, but the type is varbinary(8000). I would rather use the Timestamp type in SQL Server because that will increment itself if someone changes something in the database (outside of NHibernate). Anyone know if this possible and how I can make this work?

(FWIW, I have followed the instructions found here for getting timestamp columns to work with Fluent NHibernate, but it doesn't appear that this stuff would have anything to do with the SchemaExport.)

Answer 1

Answering my own question...

You can do it this way, which involves some hacks: http://japikse.blogspot.com/2008/06/sql-server-timestamp-with-nhibernate-20.html?showComment=1239132960000#c6605833213839346413

I just let NHibernate create the varbinary columns and then I run a SQL script afterwards to fix everything:

DECLARE @sql nvarchar(255)
DECLARE @sql2 nvarchar(255)
WHILE EXISTS
(
    select 1 from INFORMATION_SCHEMA.COLUMNS 
    where COLUMN_NAME = 'Timestamp'
    and DATA_TYPE = 'varbinary'
)
BEGIN
    select  @sql = 'ALTER TABLE [' + table_name + '] DROP COLUMN [Timestamp]',
      @sql2 = 'ALTER TABLE [' + table_name + '] ADD [Timestamp] timestamp not null'
    from    INFORMATION_SCHEMA.COLUMNS 
    where COLUMN_NAME = 'Timestamp'
    and  DATA_TYPE = 'varbinary'
    exec    sp_executesql @sql
    exec    sp_executesql @sql2
END
GO

Boo yah!