Friday, October 22, 2010

NHibernate ORM How-To. The Object, the Relational, the Mapping. In 4 Easy to Follow Steps

1. The Object aspect, sans relational. This will run as is(actually you can skip this Step 1, the Step 2 contains both object and its relational query):

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

namespace AwesomeNHibernate
{
 class Program
 {
  static void Main(string[] args)
  {
   IList<Musician> musicians = SampleMusicianList();                            
     
   foreach (Musician musician in musicians)
   {
    Console.WriteLine("*****\n{0}\n\nAge: {1}\n", musician.MusicianInfo, musician.Age);
   }

   Console.ReadLine();
      
  }


  static IList<Musician> SampleMusicianList()
  {
   return new List<Musician>
   {
    new Musician { MusicianId = 1, MusicianName = "Elvis Presley", BirthYear = 1935, FavoriteInstrument = "Guitar" },
    new Musician { MusicianId = 2, MusicianName = "Jimi Hendrix", BirthYear = 1942, FavoriteInstrument = "Electric Guitar" },
   };
  }

 }//Program

 public class Musician
 {
  public int MusicianId { get; set; }
  public string MusicianName { get; set; }
  public int BirthYear { get; set; }
  public string FavoriteInstrument { get; set; }



  public int Age
  {
   get
   {
    return DateTime.Today.Year - BirthYear;
   }
  }
  
  public string MusicianInfo
  {
   get
   {
    return string.Format("Musician ID: {0}\nName: {1}\nFavorite Instrument: {2}", MusicianId, MusicianName, FavoriteInstrument);
   }
  }        
  
 }//Musician
}//Awesome

2. The Relational aspect. This is a complete ORM code but will not run yet. There's already object-relational stuff happening in this code, but it's not mapped to a live database yet, shall run after you're done with steps 3 and 4.

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



namespace AwesomeNHibernate
{
 class Program
 {
     
  static void Main(string[] args)
  {
   IList<Musician> musicians = MusicianList();                            
     
   foreach (Musician musician in musicians)
   {
    Console.WriteLine("*****\n{0}\n\nAge: {1}\n", musician.MusicianInfo, musician.Age);
   }

   Console.ReadLine();
      
  }
 
  
  // Relational...
  static ISession OpenSession()
  {
   var c = new Configuration();
   c.AddAssembly(Assembly.GetCallingAssembly());
   ISessionFactory f = c.BuildSessionFactory();
   return f.OpenSession();
  }


  static IList<Musician> MusicianList()
  {
   using (ISession session = OpenSession())
   {
    IQuery query = session.CreateQuery("from Musician order by BirthYear, MusicianName");

    IList<Musician> musicians = query.List<Musician>();

    return musicians;
   }

  }
  // ...Relational

 }//Program

 public class Musician
 {
  public int MusicianId { get; set; }
  public string MusicianName { get; set; }
  public int BirthYear { get; set; }
  public string FavoriteInstrument { get; set; }



  // The beauty of ORM, it allows you to offload compute-intensive routine from database to front-end(e.g. webserver) or middle-tier        
  public int Age
  {
   get
   {
    return DateTime.Today.Year - BirthYear;
   }
  }

  
  public string MusicianInfo
  {
   get
   {
    return string.Format("Musician ID: {0}\nName: {1}\nFavorite Instrument: {2}", MusicianId, MusicianName, FavoriteInstrument);
   }
  }        
  
 }//Musician
}//Awesome

3. The Mapping aspect

Create RecordCompanyDatabase.hbm.xml file and add it to your project, then put this content...

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" assembly="AwesomeNHibernate" namespace="AwesomeNHibernate" >

  <class name="Musician" lazy="false" table="musician">
    <id name="MusicianId" column="musician_id">
      <generator class="sequence">
        <param name="sequence">musician_id_seq</param>
      </generator>
    </id>

    
    <property name="MusicianName" column="musician_name"/>
    <property name="BirthYear" column="birth_year"/>
    <property name="FavoriteInstrument" column="favorite_instrument"/>
    
    
  </class>

</hibernate-mapping>  


After adding that xml file, change the RecordCompanyDatabase.hbm.xml's Build Action property from Content to Embedded Resource. Keyboard shortcut: Ctrl+W+S, Ctrl+W+P, F4(or Alt+Down)



Alternatively, if your NHibernate objects are organized to different assemblies and/or namespace, you can put the explicit namespace name and assembly name in the class' name itself. i.e. class name="NamespacenameHere.ClassnameHere, AssemblynameHere"


<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true">

  <class name="AwesomeNHibernate.Musician, AwesomeNHibernate" lazy="false" table="musician">
    <id name="MusicianId" column="musician_id">
      <generator class="sequence">
        <param name="sequence">musician_id_seq</param>
      </generator>
    </id>

    
    <property name="MusicianName" column="musician_name"/>
    <property name="BirthYear" column="birth_year"/>
    <property name="FavoriteInstrument" column="favorite_instrument"/>
    
    
  </class>

</hibernate-mapping>  


4. The database

  • Connection. Put this in your App.config file
  • <?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="dialect">NHibernate.Dialect.PostgreSQLDialect</property>      
          <property name="connection.connection_string">Server=localhost;Database=test;User ID=postgres;Password=opensesame;</property>            
          <property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
                   
        </session-factory>
                
      </hibernate-configuration>
      
    </configuration>
    
    Note: In order for this to work in MonoDevelop, change app.config's Copy to output directory property from Do not copy to Always copy If you don't want to clutter your app.config, you can put nhibernate settings on separate file instead, NHibernate look its settings from hibernate.cfg.xml file:
    <?xml version="1.0" encoding="utf-8" ?>
    
    <hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
     
     <session-factory>
     
       <property name="dialect">NHibernate.Dialect.PostgreSQLDialect</property>      
       <property name="connection.connection_string">Server=localhost;Database=test;User ID=postgres;Password=opensesame;</property>            
       <property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
                 
     </session-factory>
             
    </hibernate-configuration>   
    
    Likewise, change hibernate.cfg.xml's Copy to output directory property from Do not copy to Always copy. And change the OpenSession code to:
    static ISession OpenSession()
       {
        var c = new Configuration(); 
        c.Configure(); // add this, this makes NHibernate look for its settings from hibernate.cfg.xml instead of app.config
        c.AddAssembly(Assembly.GetCallingAssembly());
        ISessionFactory f = c.BuildSessionFactory();     
        return f.OpenSession();
       }
    
    Note the c.Configure();, failing to add that code will result to:
    Unhandled Exception: NHibernate.MappingException: Could not compile the mapping document: AwesomeNHibernate.RecordCompanyDatabase.hbm.xml ---> System.InvalidOperationException: Could not find the dialect in the configuration
    
  • Download Postgres as your database
  • Supporting Components(add these 3 files to your Project's Reference)
    • NHibernate-2.1.2.GA-bin\Required_Bins\NHibernate.dll
    • NHibernate-2.1.2.GA-bin\Required_For_LazyLoading\LinFu\NHibernate.ByteCode.LinFu.dll
    • Npgsql2.0.10-bin-ms.net4.0\Npgsql.dll
    • Download:
  • Database content
  • create table musician
    (
    musician_id serial not null primary key,
    musician_name text,
    favorite_instrument text,
    birth_year int
    );
    
    insert into musician(musician_name, favorite_instrument, birth_year) values
    ('John Lennon','Guitar', 1940),
    ('Paul McCartney', 'Bass Guitar', 1942),
    ('George Harrison', 'Sitar', 1943),
    ('Ringo Starr', 'Drums', 1940);
    


You can now run the project. When we add the .NET 4.0 version of Npgsql, we must also change the Target Framework of our project to 4.0. Another note, if you received a compile-time error that type or namespace NHibernate could not be found even you already add the NHibernate.dll reference to your project, change the Target Framework from .NET Framework 4 Client Profile to .NET Framework 4

Note: To facilitate autocomplete for your NHibernate hbm.xml files, put these two files...

  • NHibernate-2.1.2.GA-bin\Required_Bins\nhibernate-configuration.xsd
  • NHibernate-2.1.2.GA-bin\Required_Bins\nhibernate-mapping.xsd

...to:

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Xml\Schemas




To use the above NHibernate code on Sql Server, just change the three session-factory properties of App.config(or in hibernate.cfg.xml)...


<property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
<property name="connection.connection_string">Data Source=.\SQLEXPRESS;Initial Catalog=RecordCompany;Trusted_Connection=yes</property>
<property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>

..., and change RecordCompanyDatabase.hbm.xml's Musician's id generator to:

<id name="MusicianId" column="musician_id">
    <generator class="native"/>
</id>    


...And on musician table, change the serial to its Sql Server equivalent

musician_id int identity(1,1) not null primary key

Another note, Postgresql's TEXT type is essentially the same as VARCHAR(MAX). So you also need to change the TEXT types to VARCHAR(MAX), e.g.

create table musician
(
musician_id int identity(1,1) not null primary key,
musician_name varchar(max),
favorite_instrument varchar(max),
birth_year int
);
 
insert into musician(musician_name, favorite_instrument, birth_year) values
('Kurt Cobain','Guitar', 1967),
('Krist Novoselic', 'Bass Guitar', 1965),
('Dave Grohl', 'Sitar', 1969)


If you will use TEXT type on SQL Server, you will encounter this error on your NHibernate query:


ADOException was unhandled

could not execute query
[ select musician0_.musician_id as musician1_0_, musician0_.musician_name as musician2_0_, musician0_.birth_year as birth3_0_, musician0_.favorite_instrument as favorite4_0_ from musician musician0_ order by musician0_.birth_year, musician0_.musician_name ]
[SQL: select musician0_.musician_id as musician1_0_, musician0_.musician_name as musician2_0_, musician0_.birth_year as birth3_0_, musician0_.favorite_instrument as favorite4_0_ from musician musician0_ order by musician0_.birth_year, musician0_.musician_name]

And it's not NHibernate's query fault. NHibernate just translates the HQL(Hibernate Query Language) to your RDBMS's SQL dialect. Sql Server cannot perform an ORDER BY on TEXT fields. Try to remove the order by BirthYear, MusicianName on your NHibernate's query, the program shall run as normal. NHibernate just reflect the fact Sql Server cannot perform ORDER BY on TEXT fields; one advice, avoid TEXT fields on Sql Server. NHibernate does a poor job on stating that fact ;-)

So if you use text on Sql Server Management Studio, and performed ordering on them,...

select * from musician order by birth_year, musician_name 

...this will be its actual error(programmer-friendly error):
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.



The how-to video:





























EDIT February 12, 2011

New approach, spoiler alert: 1 monolithic step only ;-)

One-stop-shop with Fluent NHibernate

1 comment:

  1. I would just like to thank you for an excellent tutorial on NHibernate with Mono. I have successfully implemented it with my ASP.NET MVC project. For a newbie like me this has been invaluable. Many thanks.

    ReplyDelete