Das Blog

Debugging one line at a time
$1*/ mo WordPress hosting! Get going with GoDaddy!
Menu
  • Home
  • Coding
    • Entity Framework
    • MVC
    • NHibernate
  • JavaScript
    • TypeScript
  • Hosting
  • SEO
  • Social
  • Database
    • MySql
    • SSIS
  • WP
  • Other
    • CakePHP
    • Error Messages
    • Google Maps
Home
Database
MySql
How to map DateTime and Boolean data types to MySql using NHibernate and Sharp CRUD Scaffolding.
MySql

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

Codex Discipulus February 24, 2010

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

    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

    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

    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

    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

    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

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

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

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

    Scaffolding DateTime declaration

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

    Core class with DateTime property

    Core class with DateTime property

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

Share
Tweet
Google+
Linkedin
Stumble
Email
Prev Article

Related Articles

MySql NHibernate and Sharp Architecture setup
The Nhibernate.config file comes setup to work with SQL Server …

Setup MySql to work with Sharp Architecture and Nhibernate

About The Author

Codex Discipulus

Hello, my name is David and I am a disciple of coding, always learning and always ready to help. Welcome to my blog, I hope you find it useful, please send your comments and share an article with your friends.

5 Comments

  1. K.Ly

    Thanks for the post. Not exactly the problem I was having but close enough to help me solve it.

    March 4, 2010
    • DaCoder

      I am glad it was of some help. Thanks for your comment.

      March 5, 2010
  2. ForerMedia

    This helped.

    1. I was having hassles between my S#arp project & mysql on both these issues.
    2. I added a datetime field afterwards (for audit logging) & got the problems you described.

    I only wish I saw this before wasting 3hrs.

    September 21, 2011
  3. Jeanren

    Usefull

    July 24, 2013
  4. Vijay

    Thanks for the post. I am trying to map a NULLABLE BIT to a bool?. For some reason, the mapping is not working as expected. Any reasons why NULLABLE BIT is not allowed in MySQL and EntityFramework?

    March 21, 2015

Leave a Reply

Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Recent Posts

  • Hosting ASP.Net Core 3.x Worker Service as …
    Using the Worker template now is possible to host an …
  • Calling an HTTPS WCF Service from Net …
    We all know how to call a WCF service from …
  • How to Add a VS Code Project …
    Let’s try to add a Visual Studio Code project to …
  • Step by Step Token based Authentication in …
    Token Authentication is a very popular method to secure a …
  • How to Ignore NuGet Packages in TFS …
    This should be a short post. It is really just …

Categories

  • CakePHP
  • Coding
  • Database
  • Entity Framework
  • Error Messages
  • General
  • Google Maps
  • Hosting
  • JavaScript
  • MVC
  • MySql
  • Net Core
  • NHibernate
  • SEO
  • SSIS
  • TypeScript
  • Wordpress

Tags

ActiveDirectory analytics apps aspnet CakePHP coding database projects design patterns entity framework error messages fatcow first steps ftp github gitlab godaddy google maps api hangfire Hosting iis7 java javascript membership MVC MySql NHibernate nuget nuget packages oracle owin repository SEO sharp ssis t4 tfs thesis token authentication typescript vs2010 vs2015 vs2017 web api Wordpress wp-pagenavi

Das Blog

Debugging one line at a time
Copyright © 2021 Das Blog
ScottsdaleWebStudio.com © All Rights Reserved 2017