How to map DateTime and Boolean data types to MySql using NHibernate and Sharp CRUD Scaffolding.

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.

1. 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.
   

   Sharp scaffolding Boolean declaration

2. 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‘.
   

   Sharp MVC Core class with boolean property

3. MySql Category table is setup like this, where the data type for ‘IsActive‘ is tinyint not null.
   

   MySql boolean setup

4. And finally when I run the MVC application and try to add a new category, this are my field value:
   

   Web page values for MySql boolean datatype

5. And the newly created row in MySql. Notice how the boolean is stored as tinyint 1.
   

   MySql new row created

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”

Default value for datetime MySql

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”

Unable to Convert MySql date time error

One of the solutions is to add “Allow Zero Datetime=True” to the connection string in NHibernate.config

Allow Zero DateTime to True

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“.

1. This is my “ScaffoldingGeneratorCommand.tt” file for the DateTime property I want to add.
   

   Scaffolding DateTime declaration

2. That will create the following property in my Core class:
   

   Core class with DateTime property

Again, let me know if this information help you by leaving a comment, even something small. Thanks.