Simple Example of NHibernate and SQL Server 2008

Posted: April 1, 2010 in C#, Database, Programming
Tags: , , ,

In this article, i will create a simple application that which implements NHibernate, in database side, i will use SQL Server 2008. What is NHibernate ? I will not explain it in there,  there are lot of explanations in the internet, like in here.

You can download latest NHibernate version here.

First, i will create a simple database that have just one table (and i added some record in there):

This is the DDL (Data Definition Language)

create table ACCOUNT (
   ACCID                int         identity(1,1)       not null,
   USERNAME             varchar(50)                     null,
   PASS                 varchar(50)                     null,
   constraint PK_ACCOUNT primary key nonclustered (ACCID)
)

Hmm.. what does this Identity(1,1)  mean ??? It is mean we will set ACCID attribute, the primary key, to become autonumbered, the value will be added automatically incrementing from 1, 2, 3, … and so on.

Next step, we will create simple solution in Visual Studio 2008 SP1 (remember … to connect to SQL Server 2008, your Visual Studio 2008 must be Service Pack 1 ).

Add some references

  1. NHibernate.dll (NHibernate library)
  2. NHibernate.ByteCode.LinFu.dll (for lazy loading)
  3. System.configuration

Test your connection with database using Server Explorer

Do not forget to copy the connection string…

For my example, my database connection string is :  ]

Data Source=WIRTH;Initial Catalog=TestNHibernate;User ID=azer89;Password=azer89

Next,  create an App.config file in your project (Just Right click your project, Add, New Item, and Application Configuration File)

Set your App.config like this :

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />
  </configSections>
  <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.SqlClientDriver</property>
      <property name="connection.connection_string">Data Source=WIRTH;Initial Catalog=TestNHibernate;User ID=azer89;Password=azer89</property>
      <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
      <property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
      <mapping assembly="NHibernateTest"/>
    </session-factory>
  </hibernate-configuration>
</configuration>

You must pay attention in property connection.connection_string and dialect in code above. Add a model class similar to Account Table, the fields should be similar too

Your Account.cs should be like this :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace NHibernateTest
{
    class Account
    {
        private int accId;
        private string username;
        private string pass;

        public virtual int AccId
        {
            get { return accId; }
            set { accId = value; }
        }

        public virtual string UserName
        {
            get { return username; }
            set { username = value; }
        }

        public virtual string Pass
        {
            get { return pass; }
            set { pass = value; }
        }
    }
}

After creating Account class, we will create map to Account Table using XML file, so NHibernate will know that Account class in application will map to Account Table in database. The XML filename will be “Account.nbm.xml”

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="NHibernateTest" assembly="NHibernateTest">
  <class name="Account" table="ACCOUNT" lazy="false">
    <id name="AccId">
      <column name="ACCID"/>
      <generator class="native"/>
    </id>
    <property name="UserName">
      <column name="username"/>
    </property>
    <property name="Pass">
      <column name="pass"/>
    </property>
  </class>
</hibernate-mapping>

Look at generator class, the value is “native”, it is because we have set ACCID column to autoincrement. Oh yeah, Build Action for hbm.xml file must be “Embedded Resource”

Next step is to create persistence class, we will name it NHibernateHelper.cs

using System.Reflection;
using NHibernate;
using NHibernate.Cfg;

namespace NHibernateTest
{
    public sealed class NHibernateHelper
    {
        private static ISessionFactory SessionFactory;

        private static void OpenSession()
        {
            Configuration configuration = new Configuration();
            configuration.AddAssembly(Assembly.GetCallingAssembly());
            SessionFactory = configuration.BuildSessionFactory();
        }

        public static ISession GetCurrentSession()
        {
            if (SessionFactory == null)
                NHibernateHelper.OpenSession();

            return SessionFactory.OpenSession();
        }

        public static void CloseSessionFactory()
        {
            if (SessionFactory != null)
                SessionFactory.Close();
        }
    }
}

Since ISessionFactory is expensive to be created (it almost take one second in my old computer) we will create one object of ISessionFactory, otherwise with ISession, very cheap to be created, so we can create it very often in your application. We will test the code :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;

namespace NHibernateTest
{
    class Program
    {
        static void Main(string[] args)
        {
            string username = "reza";
            string pass = "reza";

            ISession session = NHibernateHelper.GetCurrentSession();
            IQuery query = session.CreateQuery("FROM Account WHERE username = :name  AND pass = :pass ");
            query.SetString("name", username);
            query.SetString("pass", pass);
            IList<Account> acc = query.List<Account>();
            session.Close();
            if (acc.Count == 0)
                Console.WriteLine("Cannot find specified user");
            else
                Console.WriteLine("Found " + acc[0].UserName);

            Console.ReadKey();

        }
    }
}

It works !

About these ads
Comments
  1. Ranjeet says:

    Its really very good tutorial for starting Nhibernate………..

  2. sisili says:

    Hi
    I run these code in vs2008 and the app has one error:
    The non-generic method ‘NHibernate.IQuery.List()’ cannot be used with type arguments
    please help me to handel it.

  3. Comments says:

    Simple Example of NHibernate and SQL Server 2008 « Computer Horror…

    Thank you for submitting this cool story – Trackback from Comments…

  4. mrRose says:

    Thanks. Easy, useful, enough for first kick.

  5. Prog_Guru says:

    I just wanted to thank you dude for that awsome explanation,it was really hard for me to understand what really Nhibernate means,but now i feel so comfortable…
    If there are many men like you on the planet we would be so good

  6. Vishal Surve says:

    Hi!!!
    Very nice article……very easy for beginners…….Thanks a lot

  7. Kimsuor says:

    When I try with this sample I meet error :
    “An unhandled exception of type ‘NHibernate.Hql.Ast.ANTLR.QuerySyntaxException’ occurred in NHibernate.dll
    Additional information: Account is not mapped”

  8. harold says:

    hi men , you have project for download …… thnx

  9. harold says:

    hi men; I just do not work the code

  10. [...] https://azerdark.wordpress.com/2010/04/01/simple-example-of-nhibernate-and-sql-server-2008/ Like this:LikeBe the first to like this post. Esta entrada fue publicada en Paginas polenta. Guarda el enlace permanente. ← Web Services [...]

  11. luis says:

    which version of nhibernate?

  12. Nam says:

    it’s cool, very clear to me.
    thanks so much !

  13. Susan says:

    I think above scenario can be useful for sql server 2005 right? It found very useful and nice content.

  14. vini says:

    hi
    when i run this example i got a error :(

    error : unableToLoadProxyFactoryFactoryException was unhandled.

  15. Sham says:

    Your code is not working

  16. So, the bottom line is that, one more time, we have another tool that you have to write a lot to make something work. Comparting this to n-tier development we are doing the same again. Does it worth the effort? We are in 21 century and I believe we have tools to automate this boring stuff. Why not try something more “wizard like” ?

  17. Aravinda says:

    I started working on this. But I realized that NHibernate.ByteCode.LinFu.dll is not part of nHibernate downloads I have downloaded NHibernate-3.3.3.CR1-bin and I don’t find NHibernate.ByteCode.LinFu.dll in it. Could you please provide me more info about where to find this?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s