NHibernate and MySQL - A simple example

• Download sample (with update/delete) - 1.25 MB
• Download sample - 1.25 MB

Introduction

As I usually like to try things out right away, I did the same when starting to learn about NHibernate, so I fired up my Visual Studio 2008 and created a new web application to be able to explore this new and awesome technique.

I had read (as always not so thorough perhaps) the documentation and had also searched around for some simple examples to get me started, and as I am a fan of MySQL, I wanted to use it in my first test application. Unfortunately, I stumbled across some problems right away since all the examples I had seen and all the documentation samples didn't really state which version of NHibernate was used and not which version of MySQL had been tried on, so it took me a while to get everything up and running.

Due to these problems, I decided to write a short text about how you can use NHibernate and MySQL, and specifically for NHibernate 2.0.0.1001, MySQL 5.0.45, and by using the MySql ADO.Net driver 5.1.5.0. I don't intend this text to be all that detailed, but the intention is to give an example of how it can be done in the simplest way.

I assume that you are familiar with the techniques and names that I'm using in this text, but if you would like to get an update, you can read about them and download the latest versions here:

• NHibernate, which handles persisting plain .NET objects to and from a relational database.
• MySQL, a popular Open Source database engine (now owned by Sun).

Using the Code

To get started, I wanted a new clean project in VS2008, so I created a new ASP.NET Web Application by selecting the New/Project menu item under the File menu, and I created a new application from the Visual C#/Web section; let's call it WebApp1.

WebApp1 consists only of one default page, Default.aspx to which I added a Label control Label1, a TextBox named TextBox1, and two Buttons. My intention was to retrieve a single object from the database through NHibernate when the page loads and show the ID of that object in the Label control's Text property, and one of the text values of that object in the TextBox's Text property, and the two buttons are used for updating and deleting.

NHibernate

For NHibernate to work, I had to add some references to my project, and as I discovered, it wasn't only the NHibernate.dll which was needed but also the following:

• Castle.Core
• Castle.DynamicProxy2
• Iesi.Collections
• log4net
• Lucene.Net
• Rhino.Mocks

According to the documentation and the samples I looked at, you can configure NHibernate mainly in three ways: through the web.config, by code, or by a separate configuration file just for NHibernate called hibernate.cfg.xml. I chose to use the last approach as it seemed nice to have this separated from the other configuration stuff, so let's get going and take a look at the configuration file.

The hibernate.cfg.xml file consists of a few rows that are quite simple to understand, and here some of them are explained. The file that I started with looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
 <!-- an ISessionFactory instance -->
 <session-factory>
  <!-- properties -->
  <property name="connection.provider">
    NHibernate.Connection.DriverConnectionProvider
  </property> 
  <property name="connection.driver_class">
    NHibernate.Driver.MySqlDataDriver
  </property> 
  <property name="connection.connection_string">
    Server=localhost;Database=test;User ID=test;Password=test;
  </property> 
  <property name="dialect">
    NHibernate.Dialect.MySQL5Dialect
  </property> 
  <!-- mapping files -->
  <mapping resource="WebApp1.Site.hbm.xml" assembly="WebApp1" />
 </session-factory>
</hibernate-configuration>

• connection.provider sets which connection provider should be used by NHibernate to connect to the database.
• connection.driver_class sets which driver should be used and in this case, when using MySQL, MySqlDataDriver is a logical choice
• connection.connection_string is the connection string to the database.
• dialect states the NHibernate class name that enables certain platform dependent features, and in this case, since I'm using MySQL 5, the obvious choice is MySQL5Dialect

The mapping part of the configuration file tells NHibernate which mapping files to use for the object/relational mapping, and I will describe that a bit later in this article.

The Persistent Class

NHibernate can use ordinary classes when persisting objects in a relational database, and uses a mapping technique to connect the persistent class properties to the columns in the relational database tables. For my simple example, I have created a simple class called Site, and the code for that class is as follows:

namespace WebApp1 
{
  public class Site 
  { 
    private int id; 
    public virtual int Id 
    { 
      get { return id; } 
      set { id = value; } 
    } 
    private string name; 
    public virtual string Name 
    { 
      get { return name; }
      set { name = value; }
    } 
    public Site() 
    { 
    } 
  } 
}

Site.cs contains two properties that I will show later tht correlates to the columns in a database table that I'm using. NHibernate isn't really restricted to use only property types, all .NET types and primitives can be mapped, including classes from the System.Collections namespace, but in this simple example, I'm only using int and string.

The ID property is quite important since this will correlate to the primary key in the database table; even if it is not mandatory to use this for NHibernate (which can handle identifiers internally), it feels like a natural architectural approach for me.

As you can see, all the public properties are declared virtual and this is because NHibernate utilises this for some runtime enhancements which otherwise won't work according to the documentation, and it is also recommended that you provide a default constructor for the class.

The Mapping

To be able to use the Site class, it is necessary to create a mapping file that contains the metadata that NHibernate uses for the object/relational mapping, i.e., connecting the class declaration, the properties to columns, and keys in the database tables. The mapping file, which is named Site.hbm.xml, is in my example declared as:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="WebApp1.Site, WebApp1" table="site">
   <id name="Id" column="ID" type="int">
    <generator class="native"></generator>
   </id> 
   <property name="Name" column="name" 
             type="String"></property> 
  </class>
</hibernate-mapping>

The database table that this mapping file correlates to is declared as:

CREATE TABLE 'site' ( 

  'ID' int(5) unsigned NOT NULL auto_increment, 

  'name' varchar(100) NOT NULL, 

PRIMARY KEY ('ID') 

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The mapping file contains of a set of nodes and the short version of an explanation for them is:

• hibernate-mapping states that this is a hibernate mapping file and the xmlns attribute declares the XML namespace that should be used
• class states the persistent class that this mapping is connected to:
• the name attribute must state the fully qualified .Net class name and must also include the assembly name
• the table attribute names the table name in the database
• id is the node that describes the primary key column in the database table
• the name attribute tells NHibernate which property in the persistent class is used
• the column attribute tells which column in the database table is the primary key
• the type attribute tells NHibernate the database type, which in most cases should be automatically retrieved, but I found this to be a problem and therefore specifies the type
• generator is a required child element for the id node, and the class attribute states which .NET class that should be used to generate unique identifiers for instances of the persistent classes, and this class may be a specific implementation for the application or it can be one of the built-in implementations that NHibernate provides, which in my case is the native class that depends on the capabilities of the underlying database, and for MySQL, uses the identity column capability with auto increment features
• property is one or many elements that describes the persistent class properties that corresponds to the columns in the database table, which in my case only consists of one column and as such is mapped accordingly
• The name attribute tells which property in the class is used
• The column attribute corresponds to the column name in the database
• The type attribute tells NHibernate the database column type, which in most cases should be automatically detected, but as previously described, can be of some nuisance

This mapping file is added to my project root and as I discovered, the Build Action for the XML file should be set to "Embedded Resource" as this makes it possible for NHibernate to parse this at runtime, and thus simplifies the coding that is needed when using the mapping functionality.

The Coding

OK, let's get back to the coding now as we have the configuration and mapping parts in place for the application. As earlier described, I only have one page which contains a Label control, and my intention is to be able to retrieve a single value from the database by using the object/relational mapping in NHibernate to do this. So let's get right in to the code part of Default.aspx.cs.

In Page_Load, I have the following code to retrieve a list of site names from the database:

System.Collections.IList siteList; 
ISessionFactory factory = 
new NHibernate.Cfg.Configuration().Configure().BuildSessionFactory(); 

using (ISession session = factory.OpenSession()) 
{ 
  ICriteria sc = session.CreateCriteria(typeof(Site)); 
  siteList = sc.List();
  session.Close(); 
} 
factory.Close(); 
Label1.Text = ((Site)siteList[0]).Id.ToString();
TextBox1.Text = ((Site)siteList[0]).Name 

The first row is my declaration of an ordinary IList that will contain the list of Site objects that I presume to get back from NHibernate.

ISessionFactory is, according to the NHibernate documentation, a thread safe cache of compiled mappings for a singe database, and in the case when we have set the mapping files to be Embedded Resources, this is the only code that is needed to obtain such a factory.

The next part uses an ISession instance which is described as a short lived object representing a conversation between the application and the persistent store, i.e., the database, and really wraps an ADO.NET connection. To get an instance of an ISession, you ask the ISessionFactory instance to open and return such an object through the function OpenSession().

The next part uses an ICriteria instance which actually is a query API provided by NHibernate that enables us to build queries dynamically by using a more object oriented approach and represents a query against a particular persistent class. In my example, I'm using an ICriteria to retrieve a list of Site instances from NHibernate, and this is done by attaining an instance of the criteria from the session object, and also tells the session which type of object we would like to get back.

The most simple way to get an array of objects back is to use the List() function from the Criteria instance which actually returns an IList instance containing an array of instances of the specific persistent class we have defined for the criteria.

To release the resources that we have used, I call Close() on both the session object and the factory object.

And at last, I assume that I have got some results back from the database and retrieve the first occurrence of a Site object from the list and gets the ID and Name properties and present that in my Label1.Text and TextBox1.Text properties.

Since I also wanted to update the Name property, I added the following event handler to the first button I created:

protected void Button1_Click(object sender, EventArgs e)
{
    ISessionFactory factory = 
      new NHibernate.Cfg.Configuration().Configure().BuildSessionFactory();
    Site s;
    using (ISession session = factory.OpenSession())
    {
        s = (Site)session.Load(typeof(Site), int.Parse(Label1.Text)); 
        s.Name = TextBox1.Text;
        session.SaveOrUpdate(s);
        session.Flush();
        session.Close();
    }
    factory.Close();
}

What this code implements is that when Button1 is clicked, I create a ISessionFactory as before, and by using that, I create an ISession session which I then use to retrieve the corresponding Site object with the Id retrieved from the Label1.Text property, by using the Load method from the session instance, instructing the session to retrieve a Site object. I then update the retrieved object with the new Name and then issue the SaveOrUpdate command which instructs NHibrnate that the info has been updated. To persist the changes to the database, it is essential to use the Flush command, otherwise the changes won't be propagated back to the database (not entirely true if you use transactions, but that is for another article).

The second button, Button2, is used to delete the specified object, and the event connected to that is very similar to the update part:

protected void Button2_Click(object sender, EventArgs e)
{
    ISessionFactory factory = 
      new NHibernate.Cfg.Configuration().Configure().BuildSessionFactory();
    using (ISession session = factory.OpenSession())
    {
        s = (Site)session.Load(typeof(Site), int.Parse(Label1.Text));
        session.Delete(s);
        session.Flush();
        session.Close();
    }
    factory.Close();
}

Like the update code, the first step is to retrieve the object that we are interested in, and then by using the session instance, invoke the Delete command on that object, and finally, flush the changes back to the data store.

Summary

This short article describes an example of how you can use NHibernate and MySQL to present information from a relational database in a more object orientated way; it is not complete in many ways as I have left out exception handling and more, but it shows how these techniques can be used and perhaps gives someone an urge to look more into this since it is a very interesting approach and simplifies the database connectivity that we almost always use in our applications.

Points of Interest

While working with this piece of code and configurations, I stumbled across certain problems almost always related to product versioning and compatibility problems, which in most cases also were connected to the problem of having updated documentation regarding Open Source development projects.

History

• 2008-05-15: First version.
• 2008-10-16: Updated the text with update and delete functionality.