 |
Platforms, Frameworks & Libraries »
.NET Framework »
How To
Intermediate
License: The Code Project Open License (CPOL)
NHibernate and MySql - a simple example on how to use itBy Johan LundqvistA short article with an example on how you can use NHibernate and Mysql together |
C# (C# 2.0, C# 3.0), .NET (.NET 2.0, .NET 3.0, .NET 3.5), ASP.NET, Visual Studio (VS2008), Dev
|
||||||||||
|
Advanced Search Add to IE Search |
|
|
|
||||||||||||||||
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 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:
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, lets call it WebApp1.
WebApp1 consists only of one default page, Default.aspx to which I added one ordinary Label control Label1, a textbox named TextBox1 and two ordinary 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 controls text property, and one of the text values of that object in the textbox text property, and the two buttons are used for updating and deleting.
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:
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>
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.
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.
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:
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.
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]).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.
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 ID and Name properties and presents 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 the button Button1 is clicked I create a ISessionFactory as before and by using that I create an ISession session which I then uses 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 updates te retrieved object with the new Name and then issues the SaveOrUpdate command which instruces NHibrnate that the info have been updated. To persist the changes to the database it is essential to use he 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 datastore.
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.
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.
2008-05-15 First version.
2008-10-16 Updated the text with update and delete functionality.
| You must Sign In to use this message board. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
General 
News 
Question 
Answer 
Joke 
Rant 
Admin
|
PermaLink |
Privacy |
Terms of Use
Last Updated: 16 Oct 2008 Editor: Sean Ewington |
Copyright 2008 by Johan Lundqvist Everything else Copyright © CodeProject, 1999-2009 Web22 | Advertise on the Code Project |
