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 Button
s. 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:
="1.0"="utf-8"
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
<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 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 choiceconnection.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:
="1.0"="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 usedclass
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 featuresproperty
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.