Dan Watsons Coding Zen

Currently I am in the process of building (yet another) open source blog engine (public repository will be setup soon – I am just getting the core written first!). I really want this application remain database agnostic, so I am using NHibernate to satisfy my data access needs. I ran info a few issue’s when setting up NHibernate to work with MySql and thought it best to share my findings from the last 2 hours of torment. :-)

Firstly there are a few prerequisites when using MySql with NHibernate:-

1) You have MySql Installed (if you are running your database locally) :- http://dev.mysql.com/downloads/mysql/
2) You have the MySql Connector for .NET installed :- http://dev.mysql.com/downloads/connector/net/
3) You have the GUI tools installed (this really is not a prerequisite – I am just trying to do you all a massive favour) http://dev.mysql.com/downloads/gui-tools/5.0.html

When setting up the NHibernate configuration file (nhibernate.config) you will need to set the NHibernate dialect to use MySQLDialect and the connection driver to use the MySqlDataDriver.

  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
                 <session-factory name="Tribe.Blog">
            
                  <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
                  <property name="dialect">NHibernate.Dialect.MySQLDialect</property>
                  <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
                  <property name="use_proxy_validator">true</property>
                  <property name="connection.connection_string">
                          Server=localhost;Database=Blog;User ID=root;Password=***
      </property>
            
                  <property name="adonet.batch_size">16</property>
                  <property name="generate_statistics">true</property>
                  <property name="current_session_context_class">web</property>
                  <property name="proxyfactory.factory_class">
                          NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle
      </property>
            
                </session-factory>
              </hibernate-configuration>

NHibernate is great because it only takes a few changes in the configuration file and you can switch to using MySql from Sql Server and vice versa (plus many other database platforms!).

The Gotcha!

There is a good chance you will run into this error at runtime once all the NHibernate configuration is setup. The IDbCommand and IDbConnection implementation in the assembly MySql.Data could not be found. Ensure that the assembly MySql.Data is located in the application directory or in the Global Assembly Cache. If the assembly is in the GAC, use <qualifyAssembly/> element in the application configuration file to specify the full name of the assembly.

When you installed the MySql Connector it put the MySql.Data assembly into the GAC for you. All you need to do is add the following information to your applications configuration file (in my case the web.config).

  <runtime>
                <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
                  <qualifyAssembly partialName="MySql.Data" 
                       fullName="MySql.Data, Version=6.2.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d">
     </qualifyAssembly>
                </assemblyBinding>
              </runtime>

You can find out the Version Number and PublicKeyToken of the MySql.Data Assembly by using the Visual Studio command line and typing :- gacutil /l MySql.Data