Geeks With Blogs
Marc Lehmann blog

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';
This is pretty much a simplyfied version of the Cat-Table used in the NHibernate quickstart.

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.
Now the green bar appears - Jippee!

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();
		}
Once the stuff is set up right, NHibernate is a really great tool to work with, I think. Even if it's not as mature as it's big sister Hibernate, of which it is a port.

Posted on Wednesday, July 26, 2006 7:02 PM C# and .Net 2 , Software Development | Back to top


Comments on this post: Using NHibernate with MySQL

# re: Using NHibernate with MySQL
Requesting Gravatar...
Nice stuff.
Left by caocao on Oct 30, 2007 4:44 AM

# re: Using NHibernate with MySQL
Requesting Gravatar...
Please help me use procedure connect database by Nhibernate with ASP.Net
Left by suri on Feb 21, 2008 1:42 AM

# re: Using NHibernate with MySQL
Requesting Gravatar...
Hi,
Can anyone help me to find the "mysql.data.dll"?
I tried it on mysql site but We can not download that?
Any help will welcome.
Left by samonem on Mar 25, 2008 1:09 PM

# re: Using NHibernate with MySQL
Requesting Gravatar...
I cant connect to mysql using conector. There is not a mono folder in zip file anymore, and mysql.data is full of pinvokes to kernel32, so is unusable with mono/linux. Any sugestions?
cubaman
Left by cubaman on Oct 01, 2008 2:17 AM

# re: Using NHibernate with MySQL
Requesting Gravatar...
Hey, Dude,

you should double ckeck not to post your database credentials!
Left by the guy on Nov 21, 2008 6:07 AM

# re: Using NHibernate with MySQL
Requesting Gravatar...
Hey guys...

There a really nice tutorial at http://darioquintana.com.ar/articles/tutorial-de-nhibernate-primeros-pasos

As you can see it is written in Spanish and the code provided for that tutorial it is also commented in Spanish.

I downloaded the code just to play around with NHibernate and I found it really simple to use, I could use the mysql connector just by changing the driver string in hibernate.cfg.xml and adding the reference to MySql.Data.dll.

Everything worked just fine.

I'll try to translate it and give you the url where you can read it.

Nice coding.
Left by Saul Martínez on Feb 22, 2009 11:25 AM

# re: Using NHibernate with MySQL
Requesting Gravatar...
By the way...

Does anybody has implemented NHibernate in a Web Service? (C#) or anybody knows where can I find documentation to do so?

Thanks in advance.
Left by Saul Martínez on Feb 22, 2009 11:27 AM

# re: Using NHibernate with MySQL
Requesting Gravatar...
Thanks, really useful to get my hands on nhibernate and mySql.
Left by Lucia Bentivoglio on Mar 24, 2009 6:15 AM

# re: Using NHibernate with MySQL
Requesting Gravatar...
to Saul Martínez
http://periodnet.blogspot.com/
Left by Semyon Safonov on Oct 12, 2010 12:02 PM

# useful
Requesting Gravatar...
what tha fuking fuck!!
Left by Using NHibernate on Jul 13, 2012 4:23 PM

# re: Using NHibernate with MySQL
Requesting Gravatar...
Hi,

i have one optimization for your code:

Instead of doing a List().Count(), its better to use a "projection", since Count() can be executed only if a full List was generated before - so you may save some some cycles and you will be rewarded with a better performance
Left by Lelala on Jul 22, 2012 8:06 AM

Your comment:
 (will show your gravatar)


Copyright © Marc Lehmann | Powered by: GeeksWithBlogs.net | Join free