Introduction
Some time ago I talked a little about NHibernate. This is the .Net version of the Hibernate ORM. I’ve also made several failed attempts to make NHibernate work in Visual Studio with MS SQL server. I worked my way through this example several times: Your first NHibernate based application. Needless to say, NHibernate has a bit of a learning curve and I have finally achieved a successful setup of NHibernate with MS SQL server 2005. So here’s how I did it…
Setting up
First, create a new console application. Now add in the NHibernate dlls using NuGet. I don’t particularly like NuGet because I think it’s very bloated, but this is a start. I will be stripping out the NuGet package stuff before I upload the sample code to this website. So you won’t see any package stuff in the project. Instead, you’ll see a directory called “3rdparty” in the project where I’ll stick a copy of the necessary dll’s and xsd files.
Create a directory in your project called “mappings”, another directory called “domain” and you can optionally create a “design” directory if you want to create an object map (I’m not going to bother with this setup).
The Database Setup
For the database, I’ll be using MS SQL server 2005. I created a database called “sampledata” and I created one table in this database called “Product”. You can use this SQL script to generate the table inside your database:
USE [sampledata] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Product]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Category] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Discontinued] [bit] NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Time to get Serious
Create an xml file in the project named “hibernate.cfg.xml” and paste this code into it:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
<property name="connection.connection_string">Server=sqlserverdatabasename;Initial Catalog=sampledata;Integrated Security=True</property>
<property name="show_sql">true</property>
</session-factory>
</hibernate-configuration>
First of all, I like the idea of creating a separate file containing just the hibernate database configuration information. That keeps things tidy and grouped according to function. You can also put this xml code inside your web.config or app.config file.
Update:
Make sure you set the properties of your config file to “Copy Always”:
Table Mappings
In your mappings directory add another xml file and name it “product.hbm.xml”. This will be the xml mapping for one table called product and it will match the table defined earlier in the SQL server. Once you create this file, you must change its build-action property to make it an “Embedded Resource”. This is a critical step.
Copy this code into your xml file:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="NHibernateTestBlog" namespace="NHibernateTestBlog">
<class name="Product" table="Product">
<id name="Id" column="Id" type="Int32">
<generator class="identity" />
</id>
<property name="Name" column="Name" />
<property name="Category" column="Category" />
<property name="Discontinued" column="Discontinued" />
</class>
</hibernate-mapping>
Domain Stuff
In your domain folder, you’ll need to add a cs file with a class defining the table that we just created in xml above. Name your file the same name as the table “product.cs” and paste this code into it (there are no usings):
namespace NHibernateTestBlog
{
public class Product
{
public virtual int Id { get; set; }
public virtual string Name { get; set; }
public virtual string Category { get; set; }
public virtual bool Discontinued { get; set; }
}
}
There is also a repository file that is used for this sample. This file contains the basic CRUD type stuff and looks somewhat redundant. I will not deep-dive into this code in this post (especially since I haven’t completely analyzed it myself), but I’ll post it here so you can just copy and paste it. Create a cs file in your domain folder named “IProductRepository.cs” and copy this code into it:
using System.Collections.Generic;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Criterion;
namespace NHibernateTestBlog
{
public interface IProductRepository
{
void Add(Product product);
void Update(Product product);
void Remove(Product product);
Product GetById(int productId);
Product GetByName(string name);
}
public class NHibernateHelper
{
private static ISessionFactory _sessionFactory;
private static ISessionFactory SessionFactory
{
get
{
if (_sessionFactory == null)
{
var configuration = new Configuration();
configuration.Configure();
configuration.AddAssembly(typeof(Product).Assembly);
_sessionFactory = configuration.BuildSessionFactory();
}
return _sessionFactory;
}
}
public static ISession OpenSession()
{
return SessionFactory.OpenSession();
}
}
public class ProductRepository : IProductRepository
{
public void Add(Product product)
{
using (ISession session = NHibernateHelper.OpenSession())
using (ITransaction transaction = session.BeginTransaction())
{
session.Save(product);
transaction.Commit();
}
}
public void Update(Product product)
{
using (ISession session = NHibernateHelper.OpenSession())
using (ITransaction transaction = session.BeginTransaction())
{
session.Update(product);
transaction.Commit();
}
}
public void Remove(Product product)
{
using (ISession session = NHibernateHelper.OpenSession())
using (ITransaction transaction = session.BeginTransaction())
{
session.Delete(product);
transaction.Commit();
}
}
public Product GetById(int productId)
{
using (ISession session = NHibernateHelper.OpenSession())
{
return session.Get<Product>(productId);
}
}
public Product GetByName(string name)
{
using (ISession session = NHibernateHelper.OpenSession())
{
Product product = session
.CreateCriteria(typeof(Product))
.Add(Restrictions.Eq("Name", name))
.UniqueResult<Product>();
return product;
}
}
}
}
On with the Program
Now we’re going to create a CRUDTests class just to see how this all works. This is not a unit test, it’s just a class that I created in the main project. Create a cs file named “CRUDTests.cs” and paste this code into it:
using NHibernate;
using NHibernate.Cfg;
namespace NHibernateTestBlog
{
public class CRUDTests
{
private ISessionFactory _sessionFactory;
private Configuration _configuration;
private readonly Product[] _products = new[]
{
new Product
{
Name = "Melon",
Category = "Fruits"
},
new Product
{
Name = "Pear",
Category = "Fruits"
},
new Product
{
Name = "Milk",
Category = "Beverages"
},
new Product
{
Name = "Coca Cola",
Category = "Beverages"
},
new Product
{
Name = "Pepsi Cola",
Category = "Beverages"
},
};
public CRUDTests()
{
_configuration = new Configuration();
_configuration.Configure();
_configuration.AddAssembly(typeof (Product).Assembly);
_sessionFactory = _configuration.BuildSessionFactory();
}
public void CreateInitialData()
{
using (ISession session = _sessionFactory.OpenSession())
using (ITransaction transaction = session.BeginTransaction())
{
foreach (var product in _products)
{
session.Save(product);
}
transaction.Commit();
}
}
public void UpdateTest()
{
IProductRepository repository = new ProductRepository();
var product = repository.GetById(1);
product.Name = "Yellow Pear";
repository.Update(product);
// use session to try to load the product
using (ISession session = _sessionFactory.OpenSession())
{
var fromDb = session.Get<Product>(product.Id);
}
}
public void GetByIdTest()
{
IProductRepository repository = new ProductRepository();
var fromDb = repository.GetById(1);
}
public void GetByNameTest()
{
IProductRepository repository = new ProductRepository();
var fromDb = repository.GetByName("Yellow Pear");
}
}
}
Now it’s time to do something useful…
Open your Program.cs file and add the following crud test object calls to it:
namespace NHibernateTestBlog
{
class Program
{
static void Main(string[] args)
{
CRUDTests crudTests = new CRUDTests();
crudTests.CreateInitialData();
//crudTests.UpdateTest();
//crudTests.GetByNameTest();
}
}
}
You’ll notice that I commented out two lines of code. The first thing you’ll need to do is run this with the “CreateInitialData()” method first. You’ll have to fix any miscellaneous configuration stuff if an error occurs until you get a successful run. Once you have a successful run, you should see the data in your table.
Now comment the line “crudTests.CreateInitialData();” and uncomment the second and third lines. You can put a breakpoint inside the “GetByNameTest()” method and see the results that are read. If you are having difficulties in connecting to your database, examine your hibernate.cfg.xml settings. This is where I had the most difficulty in getting this thing to work.
Here’s the code if you would like to download the project and modify to your own needs: NHibernateTestBlog.zip
Summary
In this blog post I showed the minimal code necessary to get NHibernate up and running. I tried to keep this as simple as possible. If I missed something, I’ll update this article to make sure that it’s accurate. I found it frustratingly difficult to get this to work from the original article due to differences in the configuration file, so I hope this implementation helps others as an alternate reference point. So far, this is only the minimum CRUD functionality. In order to properly evaluate NHibernate over EF and Linq-to-SQL I’m going to have to attempt a few complex queries, some unit testing and mocking, plus another Smackdown! I intend to do a performance test between EF, Linq-to-SQL and NHibernate side-by-side. I might even do a more thorough performance test with larger sets of tables.
Until next time…