NHibernate and MySql - a simple example on how to use it

• Download WebApp1 - 1.26 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 2005 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 that where used and not which version of MySql it 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 VS2005, 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, lets call it WebApp1.

WebApp1 consists only of one default page, Default.aspx to which I added one ordinary Label control Label1. My intention was to retrieve a single value from the database through NHibernate when the page loads and show that value in the label controls text property.

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 where 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 choose to use the last approach as it seemed nice to have this separated from the other configuration stuff so lets 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 that should be used by NHibernate to connect to the database.
• connection.driver_class sets which driver that should be used and in this case when using MySql the 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 quite 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 the 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 later will show correlates to the columns in an 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. 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 that is used
  • the column attribute tells which column in the database table which 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 that 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 make 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 when we have the configuration and mapping parts in place for the application. As earlier described I only have on 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 the Page_Load I have the following code to retrieve a list of sites 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]).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.

An 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. e the database and really wraps an ADO.Net connection. To get an instance of an ISession you asks 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 a instance of the criteria from the session object and also tell the session which type of object we would like to get back.

The most simple way to get a 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 retrieves the first occurrence of a Site object from the list and gets the Name property and presents that in my Label1.Text property.

Summary

This short article describes an example of how you can use NHibernate and MySql to present information from an 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 uses in our applications.

Points of Interest

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

History

2008-05-15 First version.