Setting up NHibernate with SQLite using Visual Studio 2010 and NuGet


Here’s something that really took me some time to figure out. The problem was that I tried to set up the libraries all by myself, when there is this awesome tool called NuGet which does it for you. So here’s how I did it.

Installing NuGet

This is pretty straight forward. Download NuGet from the project’s homepage and follow the installation instructions.

Setting up your project

For this tutorial, I created a Visual C# Console Application called “NHExample”, and I suggest you do the same (including the name, because I will be using it later in the example files):

Installing the required libraries

Now, in the main Visual Studio window, go to “Tools > Library Package Manager > Manage NuGet Packages”. If you don’t have that menu option make sure you’ve restarted Visual Studio after installing NuGet. The following dialog should open:

Now install the following libraries (if you can’t find them, there’s a search field in the upper right corner):

  • NHibernate (installs Iesi.Collections as a dependency)
  • System.Data.SQLite (x86)

Configuring NHibernate for SQLite

Create a new XML file called hibernate.cfg.xml in your project’s root directory (Right click on your project > Add > New Item, then from the left menu choose “Data” and then “XML File”), and insert the following content:



  
    NHibernate.Driver.SQLite20Driver
    Data Source=nhibernate.db;Version=3
    NHibernate.Dialect.SQLiteDialect
    true=1;false=0
    true
  

Two notables things here. First, there is the Data Source in the property connection.connection_string which specifies which database file will be used (the files is created automatically if it does not exist). Second, there is the property show_sql. If you set that one to true, all SQL-Queries generated by NHibernate will be written to the console, which is very useful for debugging.

Important: You need to set this XML file’s Copy to output directory option to “Copy Always”, or your application will burst into flames almost immediately.

Creating the Entity Class and the Mapping

We’re going to create and map a very simple “Product” entity in this tutorial (there are plenty of sources if you want to create more complex stuff, see the Links section at the end of the article for some of them). To do this, we need two things:

  • The actual C# Class
  • The XML mapping file

The C# Class

The C# Class is pretty straight forward:

using System;

namespace NHExample.Domain
{
    public class Product
    {

        public virtual Guid Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string Category { get; set; }
        public virtual int Price { get; set; }

    }
}

Three important things in this short piece of code, though:

The NHibernate XML Mapping

I’ve created a new directory called “Mappings” for the XML file, which needs to be called Product.hbm.xml. The content has to be as follows:




  
    
      
    
    
    
    
    
  


This file tells NHibernate how the database scheme should look and which class properties should be persistent. You may have noticed that there are no type attributes for the properties, because NHibernates infers the types directly from the class file. The <generator class="guid" /> is used to automagically generate an Id for newly created entities.

Important: In order for NHibernate to be able to load your mapping file, you need to set it’s Build Action to “Embedded Resource” (it is above the “Copy to output directory” option we’ve seen in the “Configuring NHibernate for SQLite” section).

Actually using NHibernate

So now we’ve set everything up and we’re ready to write some actual code. And the first thing we need to do is the NHibernate configuration initialization and the creation of a session:

// Initialize NHibernate
var cfg = new Configuration();
cfg.Configure();
cfg.AddAssembly(typeof(Domain.Product).Assembly);

// Get ourselves an NHibernate Session
var sessions = cfg.BuildSessionFactory();
var sess = sessions.OpenSession();

The first part reads the XML configuration file we’ve created earlier, initializes the SQLite driver and also reads the mapping file for our Product entity.

// Create a Product...
var product = new Domain.Product
            {
                Name = "Some C# Book",
                Price = 500,
                Category = "Books"
            };

// And save it to the database
sess.Save(product);
sess.Flush();

Here we create a new Product instance and populate it with some data. Then we save it to the database. The sess.Flush(); makes sure that all pending actions are executed before going on.

// Note that we do not use the table name specified
// in the mapping, but the class name, which is a nice
// abstraction that comes with NHibernate
IQuery q = sess.CreateQuery("FROM Product");
var list = q.List();

// List all the entries' names
list.ToList().ForEach( p => Console.WriteLine( p.Name ));
</pre></div>

This last part creates a query to get our data back from the database, and just prints out the name of every *Product* entity in the database. 


# The full example project

The full example project's source code is available on [GitHub](https://github.com/CodingJournal/NHExample).


# Further reading

* [NHibernate Reference Documentation](http://nhforge.org/doc/nh/en/)
* [A much more complex example](http://nhforge.org/wikis/howtonh/your-first-nhibernate-based-application.aspx) for using NHibernate with MSSQL.
* [Creating relations using NHibernate](http://sdesmedt.wordpress.com/2006/10/21/nhibernate-part-5-mapping-techniques-for-aggregation-many-to-one-and-many-to-many-mapping/)


# The end...

If you have any suggestions, there is anything wrong with the code or you just want to say *"Hi"*, I'd appreciate if you'd leave a comment :)

Also, many thanks to [Mohamed Meligy](http://gurustop.net/blog/2011/05/19/sqlite-database-nuget-package-common-problems-solved/) who introduced me to NuGet, without which I'd have spent many more hours trying to figure out which version of SQLite works with which versions of NHibernate in which scenario... -_-"