Marc Lehmann

I did not find a lot of information on using NHibernate in combination with a MySQL database. The reason I want to do this at all is quite simple, though: My ISP hosts ASP.NET 2 and MySQL databases. And finding an ISP that hosts SQL-Server 2005 and ASP.NET 2 at a price you can pay when you don't make money with your site is a hard task around here. Apart from that, I don't want my code to be dependent on any database system, and NHibernate provides an excellent way to achieve this goal.

This is not about object relational mapping concepts. It also does not explain too much about NHibernate. You can get a lot of information about NHibernate from the NHibernate website.

So let's do a Test-Case (I very much like writing so called learning tests to explore stuff). I'm using SharpDevelop V. 2.0.0 and NUnit for this example.

First, I create a dummz-table on my MySQL database:

CREATE TABLE `dummytable` (
  `id` char(32) collate latin1_german1_ci NOT NULL,
  `text` varchar(255) collate latin1_german1_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci COMMENT='Table for NHibernate example';

Then I write my Domain-Model class (bit a big word for something like this, but...), which look like this:

using System;

namespace LearningTests
{
	public class Dummy
	{
		private string id;
		private string text;

		public Dummy()
		{
		}
		
		public string Id {
			get{ return id;}
			set{ id = value;}
		}
		
		public string Text {
			get{return text;}
			set{text=value;}
		}
	}
}

I also need a mapping file that defines how a Dummy instance is populated with information from the database, and how an instance is persisted to the database in turn. I call this file Dummy.hbm.xml. In this first step, set the copy property to "Copy Always".

<?xml version="1.0"?>:
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
	<class name="LearningTests.Dummy, LearningTests" table="dummytable">
		<id name="Id">
			<column name="id" sql-type="char(32)" not-null="true"/>
			<generator class="uuid.hex" />
		</id>
	
		<property name="Text">
			<column name="text" length="255"/>
		</property>

	</class>
</hibernate-mapping>

Then I need the test case. I create one simple test that looks as follows. Since the configuration file caused me some troubles, I first configure Hibernate programmatically, which is not the preferred way to do things!

using System;
using System.Collections;

using NUnit.Framework;

using NHibernate;
using NHibernate.Cfg;

namespace LearningTests.Tests
{
	[TestFixture]
	public class Test1
	{
		[Test]
		public void TestMethod()
		{
			Configuration config = new Configuration();
			IDictionary props = new Hashtable();
			String connectionString = "Server=server;Database=dbName;User ID=user;Password=passwd";
			props.Add("hibernate.dialect", "NHibernate.Dialect.MySQLDialect");
			props.Add("hibernate.connection.provider", "NHibernate.Connection.DriverConnectionProvider");
			props.Add("hibernate.connection.driver_class", "NHibernate.Driver.MySqlDataDriver");
			props.Add("hibernate.connection.connection_string", connectionString);
			config.AddProperties(props);
			config.AddFile("Dummy.hbm.xml");
			ISessionFactory factory = config.BuildSessionFactory();
			ISession session = factory.OpenSession();
			
			int countBefore = getDummyTableSize(session);
			
			ITransaction tx = session.BeginTransaction();
			Dummy d = new Dummy();
			d.Text = "test text";
			session.Save(d);
			tx.Commit();
			
			Assert.AreEqual(countBefore+1, getDummyTableSize(session));
			session.Close();
		}
		
		private int getDummyTableSize(ISession session) {
			ITransaction readTx = session.BeginTransaction();
			int size = session.CreateQuery("from Dummy").List().Count;
			readTx.Rollback();
			return size;

		}
	}
}

In order to run this code, I need to match a few requirements:

• I need the NHibernate library, downlodable here
• I also need the MySQL Connector Net library which you can get here. Notice that the installer-download contains an msi that installs the libraries into the GAC. Notice also that the no-installer download contains the binaries for Mono, .Net 1.0 and 1.1, but not for .NET 2.

But as I said, configuring Hibernate in the code is not really the preferred way to do things. It's much better to create a configuration file for Hibernate. I call it hibernate.cfg.xml and add it to the root of your test project. I also set it's copy property to "Copy Always".

<?xml version="1.0" encoding="latin1"?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.0">
	<session-factory>
		<property name="dialect">NHibernate.Dialect.MySQLDialect
		<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider
		<property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver
		<property name="connection.connection_string">Server=server;Database=dbName;User ID=user;Password=passwd
	</session-factory>
</hibernate-configuration>

This makes TestMethod shrink to the following:

		[Test]
		public void TestMethod()
		{
			Configuration config = new Configuration();
			config.Configure();
			config.AddFile("Dummy.hbm.xml");
			ISessionFactory factory = config.BuildSessionFactory();
			ISession session = factory.OpenSession();
			
			int countBefore = getDummyTableSize(session);
			
			ITransaction tx = session.BeginTransaction();
			Dummy d = new Dummy();
			d.Text = "test text";
			session.Save(d);
			tx.Commit();
			
			Assert.AreEqual(countBefore+1, getDummyTableSize(session));
			session.Close();
		}

Now it's going to get even better. I currently still add the mapping file programmatically to our configuration. I can do this in the hibernate.cfg.xml file as well. For this I set the mapping file's build action to "Embedded Resource" and then change the hibernate config like this:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.0">
	<session-factory>
		<property name="dialect">NHibernate.Dialect.MySQLDialect
		<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider
		<property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver
		<property name="connection.connection_string">Server=mysql4.webland.ch;Database=blueDB12;User ID=wl19www79;Password=746218
	
		<mapping assembly="LearningTests" />
	</session-factory>
</hibernate-configuration>

Like this I let NHibernate know that the LearningTests assembly contains mapped classes. Since I named the mapping and class file similarly and placed them next to each other, NHibernate now finds the mappings itself. This means another line in my TestMethod has gone:

		[Test]
		public void TestMethod()
		{
			Configuration config = new Configuration();
			config.Configure();
			ISessionFactory factory = config.BuildSessionFactory();
			ISession session = factory.OpenSession();
			
			int countBefore = getDummyTableSize(session);
			
			ITransaction tx = session.BeginTransaction();
			Dummy d = new Dummy();
			d.Text = "test text";
			session.Save(d);
			tx.Commit();
			
			Assert.AreEqual(countBefore+1, getDummyTableSize(session));
			session.Close();
		}