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

Question

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

I added properties to the entity object, which looks like this:

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

NHibernate will generate a Timestamp column, but type varbinary (8000). I would prefer to use the Timestamp type in SQL Server, because it will increase if someone changes something in the database (outside NHibernate). Does anyone know if this is possible and how can I do this work?

(FWIW, I followed the instructions found here to get timestamp columns for working with Fluent NHibernate, but it does not seem that this material will have anything to do with SchemaExport.)

Answer 1

Answering my own question ...

You can do it in a way that is related to 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 the SQL script 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 me!

Answer 2

FluentNHibernate, : ( , .)

public partial class PetStore //: IPetStore
{

    public PetStore()
    { /*Constructor*/      }

    public virtual Guid? PetStoreUUID { get; set; }
    public virtual byte[] TheVersionProperty { get; set; }
    public virtual string PetStoreName { get; set; }
    public virtual DateTime CreateDate { get; set; }        


}


public class PetStoreMap : ClassMap<PetStore>
{
    public PetStoreMap()
    {
        Id(x => x.PetStoreUUID).GeneratedBy.GuidComb();

        OptimisticLock.Version();
        Version(x => x.TheVersionProperty)
            .Column("MyVersionColumn")
            .Not.Nullable()
            .CustomSqlType("timestamp")
            .Generated.Always();


            Map(x => x.PetStoreName);
            Map(x => x.CreateDate);
    }
}

, " " " .

DDL :

CREATE TABLE [dbo].[PetStore](
    [PetStoreUUID] [uniqueidentifier] NOT NULL,
    [MyVersionColumn] [timestamp] NOT NULL,
    [PetStoreName] [nvarchar](255) NULL,
    [CreateDate] [datetime] NULL)