I’ve started some new development on the Generator Studio tool, and one of our big features that we felt is a real necessity is the ability to point to different database engines. With that in mind, I started off trying to get Castle ActiveRecord connecting to MySQL as part of the learning curve. Here are some pointers on getting it to work smoothly.
Firstly, what' you’ll need (in addition to Castle ActiveRecord):
MySQL Connector/Net – Version 5.2 is the latest version – I’ve can confirm that it successfully connects to MySQL server 6.0 as well.
MySQL GUI tools – These are nice to have to confirm your data/table structures, especially if you’re used to using SQL Enterprise manager.
Configuring Castle ActiveRecord to use MySQL is relatively straightforward. I’ve included an example below as a quick reference.
<activerecord>
<config>
<add key="connection.provider" value="NHibernate.Connection.DriverConnectionProvider"/>
<add key="dialect" value="NHibernate.Dialect.MySQLDialect"/>
<add key="connection.driver_class" value="NHibernate.Driver.MySqlDataDriver"/>
<add key="connection.connection_string" value="Server=Server; Database=ActiveRecordTest; User Id=root; Password=password"/>
<add key="show_sql" value="true"/>
</config>
</activerecord>
What you may get when calling any of the ActiveRecord methods is the following exception:
NHibernate.HibernateException: Could not create the driver from NHibernate.Driver.MySqlDataDriver. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> NHibernate.HibernateException: 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.
There are two ways of fixing this, as is implied in the exception:
- Copy the file locally (to your bin folder)
- Use <qualifyAssembly/> in your config file
The actual section that needs to be added to your config file looks like this:
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<qualifyAssembly partialName="MySql.Data" fullName="MySql.Data, Version=5.2.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
</assemblyBinding>
</runtime>
If you’re not sure of the values for Version and PublicKeyToken, you can browse to “C:\WINDOWS\assembly”, find the “MySQL.Data” entry, and it will have both the values there.
That’s it – you should be connecting to MySQL using NHibernate/ActiveRecord by now :)