SQL Server Timestamp with NHibernate 2.0 RC2

.NET Musings

Wandering thoughts of a developer, architect, speaker, and trainer

NAVIGATION - SEARCH

SQL Server Timestamp with NHibernate 2.0 RC2

I saw two posts on creating a custom type in NHibernate to map to the SQL Server TimeStamp column by implementing the IUserVersionType interface (reference here and here), but that didn't tell the whole story. First, there is the bug in Beta 1 that held me up longer than I wanted it to (see the bug and the fix here). With that out of the way, using the Timestamp SQL Server column mapped to a byte[] worked. (Note: bug is resolved as of Beta 2)

However, when using NHibernate to generate the table, the field got created as a varbinary(8000).

The solution seemed simple. I created a new class that derived from the MsSql2005Dialect class. I then added DbType.Object to the registered columns:

using System.Data;
using NHibernate.Dialect;
namespace MyUtilityClass.NHibernate
{
public class MsSql2005TSDialect:MsSql2005Dialect
{
public MsSql2005TSDialect()
{
RegisterColumnType(DbType.Object, "TimeStamp");
}
}
}

This maps the DbType.Object to the TimeStamp field in SQL. I picked the Object type since it wasn't being used, and not likely to be used. If your
implementation uses this type, then you will have to pick another type.

I then modified my custom type to return DbType.Object as the sole SqlType returned from the property:

public SqlType[] SqlTypes
{
get
{
SqlType[] types = {new SqlType(DbType.Object)};
return types;
}
}

Then I changed my configuration to use my custom type instead of the NHibernate MsSql2005Dialect. So, the config line looks like this:

MyUtilityClass.NHibernate.MsSql2005TSDialect, MyUtilityClass

When I ran the unit test to create my table, it worked like a champ. Then I ran the rest of my unit tests, and all of the updates failed. ADO.Net was reading DbType.Object as a Variant, setting the parameter type to "variant", and then the generated SQL was failing. Back to the drawing board.

The answer I came up with is admittedly a hack, but it solved my problem for now. Here is my (hacky) solution:

When the SqlTypes(IMapping mapping) method gets called in the CustomType
class for table creation and other DDL tasks, the container for the mapping parameter is NHibernate.Cfg.Configuration. When that method gets called for non-table creation (ie CRUD) operations, the container is not.

So, I defined two SqlTypes in my custom class, one for CRUD operations (SqlType.Binary) and one for DDL operations (SqlType.Object).

public SqlType[] SqlTypes
{
get
{
SqlType[] types = new SqlType[2];
types[(int) SqlTypeIndexer.CRUD] = new SqlType(DbType.Binary);
types[(int) SqlTypeIndexer.DDL] = new SqlType(DbType.Object);
return types;
}
}

I then changed the SqlTypes method in the CustomType class in the NHibernate source to check for the declaring type, and return the correct SqlType.

public override SqlType[] SqlTypes(IMapping mapping)
{
if (mapping.GetType().DeclaringType != null
&& mapping.GetType().DeclaringType.FullName == typeof(Cfg.Configuration).ToString())
{
return new[] {base.SqlTypes(mapping)[(int)SqlTypeIndexer.DDL]};
}
return new[] { base.SqlTypes(mapping)[(int)SqlTypeIndexer.CRUD] };
}

Not the most graceful, admittedly, but it solved my issue, and now all of my unit tests are green, I don't have to use triggers to check for database data updates outside of the NHibernate universe, and I can continue on in my coding of the POC.

Note: I created another class in NHibernate called CustomType2 (I know, real original) that inherits from CustomType. Code shown here:

[Serializable]
public class CustomType2 : CustomType
{
public CustomType2(System.Type userTypeClass, IDictionary parameters)
: base(userTypeClass, parameters) {}
public override SqlType[] SqlTypes(IMapping mapping)
{
if (base.SqlTypes(mapping).Length != 1 && this.Name
== "UserTypeTimestamp")
{
if (mapping.GetType().DeclaringType != null &&
mapping.GetType().DeclaringType.FullName ==
typeof (Cfg.Configuration).ToString())
{
return new[] {base.SqlTypes(mapping)[(int) SqlTypeIndexer.DDL]};
}
return new[] {base.SqlTypes(mapping)[(int) SqlTypeIndexer.CRUD]};
}
return base.SqlTypes(mapping);
}
public override int GetColumnSpan(IMapping session)
{
return SqlTypes(session).Length;
}
}

To this file I added the SqlTypeIndexer and my new CustomType

Comments, suggestions are more than welcome to take this to the next level.

(Note: Updated 14-Jul for Beta 2, which fixed the bug that interfered with this technique)

Happy Coding!

Comments (2) -

Philip Japikse, MVP, MCSD, MCDBA, CSM

For two way generation, yes, you need to change NH core code.  However, I don't ever do two way in real life (I always generate my c# from the db).

I do like your approach, thought.  Thanks for the comment and the detail.

It looks like you actually had to change NHibernate code to get that to work, am I correct?

I didn't feel like doing all of this, so I just wrote a SQL script that would fix everything for me, and I run the script after I do the NHibernate SchemaExport:

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

Comments are closed
Managed Windows Shared Hosting by OrcsWeb