I was working on a system which had implemented NHibernate, primarily for its ability to support multiple DBMS, such as Oracle and MS SQL Server. The problem came up when trying to insert, specifically in regards to the ID data type.
On Oracle the ID data type was NHUMBER, while on MS SQL Server the data type was INT32.
The NHibernate ID generator was custom which overrode the Generate() method of the TableGenerator class. The ID generator return a DECIMAL for Oracle and an INT32 for MS SQL Server.
Without making any changes I pointed the compatible MS SQL Server program to an Oracle database and received this error:
The type System.Decimal can not be assigned to a property of type System.Int32 setter of Namespace.Class.Id – InnerExcpetion: Das Objekt mit dem Typ “System.Decimal” kann nicht in den Typ “System.Int32” konvertiert werden.
The type System.Decimal can not be assigned to a property of type System.Int32 setter of Namespace.Class.Id – InnerExcpetion: Object of type “System.Int32” cannot be converted to type “System.Decimal”.
Based on the error I assumed the error was because of the differences between the NUMBER and the INT32 database data types. So I began to implement a custom ID data type using the NHibernate.UserTyes.IUserType interface. After a number of hours I got it to work, but man was it complicated. The combination of the custom id generator and the custom ID data type resulted in some real “sophisticated “ inheriting and overriding.
Having spent so much time in support I began to think about the maintenance of enhancement requirements and decided that there must be a better…simpler solution.
After some investigation I realized that, originally, the id type which was in the .hbm.xml mapping file was int. Flags went up and I realized that as the custom Id generator was returning a DECIMAL but the mapping file was expecting an INT32, that this was where the error was coming from and not the database data types.
Therefore, the simple solution was to convert the DECIMAL returned from the custom Id generator to an INT32, as show below.
CustomOracleDBId = Convert.ToInt32(query.List()[0]);
And kaboom, insert worked as expected and inserted the row.
The positive is that I learned how to implement a custom data type using the IUserType and support will get a more maintainable system. Negative, final solution took longer than expected. Was it worth it? I mean, is any solution just as good as any other? No.