My client has been insisting that using “uint” in code and in MS SQL is a good idea… well, so be it.
MS SQL however does not have a native “unit” datatype, therefore NHibernate does (quite reasonebly) not offer such a mapping.
There are two ways around it: Using a user type, or patch Hibernate’s MSSQL dialect class.
Via IUserType
This solution is described here and works really well. It stores an C# UInt32 in a MSSQL INT column.
Downside: Large unsigned numbers appear as negative value in the database, and in the mapping you must specify that type.
Via Patched Dialect
The other solution involves a patched SQL Dialect and also a patch SQL Driver:
public class ExtendedMsSql2008Dialect : MsSql2008Dialect
{
public ExtendedMsSql2008Dialect()
{
// Tell NH that we can handle the ADO DbTypes
RegisterColumnType(DbType.UInt16, "INT");
RegisterColumnType(DbType.UInt32, "BIGINT");
RegisterColumnType(DbType.UInt64, "DECIMAL(28)");
}
}
public class ExtendedSql2008ClientDriver : Sql2008ClientDriver
{
protected override void InitializeParameter(System.Data.IDbDataParameter dbParam, string name, NHibernate.SqlTypes.SqlType sqlType)
{
if (sqlType == SqlTypeFactory.UInt16) sqlType = SqlTypeFactory.Int32;
if (sqlType == SqlTypeFactory.UInt32) sqlType = SqlTypeFactory.Int64;
if (sqlType == SqlTypeFactory.UInt64) sqlType = SqlTypeFactory.Decimal;
base.InitializeParameter(dbParam, name, sqlType);
}
}
And of course these two classes must be added to the NHibernate config file:
<property name="connection.driver_class">MyLib.ExtendedSql2008ClientDriver, MyLib</property>
<property name="dialect">MyLib.ExtendedMsSql2008Dialect, MyLib</property>
This solution makes it easier for inexperienced users, but it comes at a price: The SQL data type is wider than the C# datatype, therefore wasting some space/performance.
Conclusion
Pretty obvious – NHibernate is quite flexible, and there is no perfect solution 🙂