For one reason or another I always have problems when mapping NHibernate to MySql Boolean and DateTime properties, including the scaffolding mapping in Sharp Architecture. So I decided to add this bit of information to my blog, I know I am probably the only one but anyways maybe I can help even one soul. If you find this post useful you better comment, that way I will know I did the right thing.
Let’s start with the Boolean
So, I am first going to tackle the Boolean data type and then the DateTime data type.
- On the scaffolding file (ScaffoldingGeneratorCommand.tt) You should have the Boolean property (in my case ‘IsActive‘) to “bool” and either “true” or “false” as your assigned value for testing.
- Once you saved the file. and the code generation completes, among the controller, tests, view there should be the Core class (‘Category.cs‘) with the property ‘IsActive‘ set to ‘bool‘.
- MySql Category table is setup like this, where the data type for ‘IsActive‘ is tinyint not null.
- And finally when I run the MVC application and try to add a new category, this are my field value:
- And the newly created row in MySql. Notice how the boolean is stored as tinyint 1.
Now for DateTime.
The only problem I always find when dealing with DateTime (date time data types) and MySql is that when the addition of such column is an afterthought (happens after the table has data already) the value for the existing rows corresponding to DateTime column will default to “0000-00-00 00:00:00”
So when running the application and .Net is trying to display the default datetime, you will most likely get this error message: “Unable to convert MySQL date/time value to System.DateTime”
One of the solutions is to add “Allow Zero Datetime=True” to the connection string in NHibernate.config
The problem with that is that the error is happening on the mapping and this change will most likely not help. Of course, the easy solution, will be to manually update the default values to a date time like “01-01-01” so that the conversion can take place, after all the values should be updated to something meaningful.
The other solution is to allow NULLS in the DateTime column. The default “0000-00-00 00:00:00” only appears when there is no prior value and NULLS are not allowed. If you change to column to allow NULLS and update the rows with no values to NULL then the conversion can take place, the DateTime represented in .NET will be the MINimum value of DateTime, which is: “1/1/0001 12:00:00 AM“.
- This is my “ScaffoldingGeneratorCommand.tt” file for the DateTime property I want to add.
- That will create the following property in my Core class:
Again, let me know if this information help you by leaving a comment, even something small. Thanks.