Linq to NHibernate

My previous codes show the use of NHibernate and Fluent NHibernate as OR/M with .NET framework. Next, I would refer to Fluent NHibernate and use Linq.

1. Download NHibernate.Linq-2.1.2-GA-Bin to get NHibernate.Linq.dll.
2. Add reference to NHibernate.Linq.dll in this file that we work with, and in the beginning add using NHibernate.Linq;
3. At line 43, add this:

  var x = (from p in session.Linq<Personal>() where p.Member== "Y" select p).Take(5);
  Console.WriteLine("Output #1:");
  foreach (var a in x)
    Console.WriteLine("id= {0}, ket= {1}", a.Id, a.Sex);

  var r = from p in session.Linq<Personal>()
          group p by p.Sex into g
          select new { sex = g.Key, count = g.Count() };
  Console.WriteLine("Output #2:");
  foreach (var a in r)
    Console.WriteLine("Sex = {0}, Count= {1}", a.sex, a.count);

4. In this sample, I add a new table and make mapping to database.

public class Personal
    {
        public virtual string Id { get; private set; }
        public virtual string Member { get; set; }
        public virtual string Sex { get; set; }
    }

public class PersonalMap : ClassMap<Personal>
{
  public PersonalMap()
  {
    Table("employees");
    Id(x => x.Id).Column("pk");
    Map(x => x.Member, "member");
    Map(x => x.Sex, "sex");
  }
}

Below the output, and it's time for me to sleep....good morning..

Fluent NHibernate with MySQL

NHibernate is an Object Relational Mapping framework, which (as ORM states) maps between relational data and objects. It defines its mappings in an XML format called HBM, each class has a corresponding HBM XML file that maps it to a particular structure in the database.

Fluent NHibernate offers an alternative to NHibernate's standard XML mapping files. Rather than writing XML documents (.hbm.xml files), Fluent NHibernate lets you write mappings in strongly typed C# code. This allows for easy refactoring, improved readability and more concise code. Fluent, XML-less, compile safe, automated, convention-based mappings for NHibernate.

Fluent NHibernate is external to the NHibernate Core, but is fully compatible with NHibernate version 2.1, and is experimentally compatible with NHibernate trunk.

Why replace HBM.XML? While the separation of code and XML is nice, it can lead to several undesirable situations.

• Due to XML not being evaluated by the compiler, you can rename properties in your classes that aren't updated in your mappings.
• XML is verbose; NHibernate has gradually reduced the mandatory XML elements, but you still can't escape the verbosity of XML.
• Repetitive mappings - NHibernate HBM mappings can become quite verbose if you find yourself specifying the same rules over again. For example if you need to ensure all string properties mustn't be nullable and should have a length of 1000, and all ints must have a default value of -1.

To counter these issues Fluent NHibernate does by moving mappings into actual code, so they're compiled along with the rest of your application; rename refactorings will alter your mappings just like they should, and the compiler will fail on any typos. As for the repetition, Fluent NHibernate has a conventional configuration system, where you can specify patterns for overriding naming conventions and many other things; you set how things should be named once, then Fluent NHibernate does the rest.

My post before, shows an example of using NHibernate. Below we modify the previous code to work with Fluent NHibernate.

1. Download fluentnhibernate-1.1.zip then extract.
2. Create a console application using Visual Studio and named as FluentNHibernateSample.
3. Add reference to FluentNHibernate.dll, NHibernate.ByteCode.Castle.dll, NHibernate.dll. In this sample we work with MySQL, so add MySql.Data.dll and remember to set Copy Local property to true.
4. Here the modified version of my previous code:

using System;
using System.Collections.Generic;
using System.Collections;
using MySql.Data.MySqlClient;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHibernate.Cfg;
using System.Linq;
using FluentNHibernate.Mapping;

namespace FluentNHibernateSample
{
  class Program
  {
    static void Main(string[] args)
    {
       FluentConfiguration config = Fluently.Configure()
          .Database(MySQLConfiguration.Standard
          .ConnectionString(c => c
             .Server("localhost")
             .Database("mydb")
             .Username("myname")
             .Password("mypass")));
       ISessionFactory factory = config.Mappings(m =>
          m.FluentMappings.AddFromAssemblyOf<Coba>())
          .BuildSessionFactory();

       var session = factory.OpenSession();
       System.Collections.IList myRows = session.CreateCriteria(typeof(Coba)).List();
       foreach (Coba item in myRows)
         Console.WriteLine("Id:{0} - Ket:{1}", item.Id, item.Ket);

       IQuery query = session.CreateQuery("from Coba order by ket desc");
       IList<Coba> dta = query.List<Coba>();
       foreach (Coba item in dta)
         Console.WriteLine("Id:{0} - Ket:{1}", item.Id, item.Ket);

       query = session.CreateQuery("FROM Coba WHERE kode=:kode");
       query.SetString("kode", "19");
       foreach (Coba item in query.List<Coba>())
         Console.WriteLine("\nResult:\nID:{0} with Ket:{1}", item.Id, item.Ket);
       session.close();
       Console.ReadKey();
     }
   }
   public class Coba
   {
     public virtual string Id{ get; private set; }
     public virtual string Ket {get; set;}
   }

   public class CobaMap : ClassMap<Coba>
   {
     public CobaMap()
     {
        Table("masjob");
        Id(x => x.Id).Column("kode");
        Map(x => x.Ket, "ket");
     }
  }
}

5. Run. We get the same result with my previous code.

The code does not use xml configuration files at all!

NHibernate MySQL

1. Download NHibernate.
2. Create a new C# console application "NHibernateTest", and create a folder "sharelib" that contains library from first step.
3. Make reference to NHibernate.dll, MySql.Data.dll, NHibernate.ByteCode.Castle.dll
4. Type this into Program.cs.
   

using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using MySql.Data.MySqlClient;
using NHibernate;
using NHibernate.Cfg;
namespace NHibernateTest
{
  class Program
  {
    static void Main(string[] args)
    {
      Configuration config = new Configuration();
      config.AddAssembly("NHibernateTest");
      config.Configure();

      ISessionFactory factory = config.BuildSessionFactory();
      try
      {
        ISession session = factory.OpenSession();
        IList myRows = session.CreateCriteria(typeof(NHibernateTest.Coba)).List();
        foreach (NHibernateTest.Coba item in myRows)
          Console.WriteLine("Id:{0} - Ket:{1}", item.Id,item.Ket);

        IQuery query = session.CreateQuery("from Coba order by ket desc");
        IList<coba> dta = query.List<coba>();
        foreach (NHibernateTest.Coba item in dta)
          Console.WriteLine("Id:{0} - Ket:{1}",item.Id,item.Ket);

        query = session.CreateQuery("FROM Coba WHERE kode=:kode");
        query.SetString("kode", "19");
        foreach (NHibernateTest.Coba item in query.List<coba>())
          Console.WriteLine("\nResult:\nID:{0} with Ket:{1}",item.Id,item.Ket);
        session.Close();
      }
      catch (Exception ex)
      {
         Console.Out.WriteLine("Error: " + ex.Message.ToString());
      }
      Console.In.Read();
    }
  }

  public class Coba()
  {
    public Coba() {}
    public virtual string Id { get; private set; }
    public virtual string Ket { get; set; }
  }
}

5. Create an XML Mapping File by right-click on the project, add a new item, an named the XML as "NHibernateTest.hbm.xml". Right-click on the file, select "Properties" and change the "Build Action" to "Embedded Resource". Here the contents.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="NHibernateTest.Coba, NHibernateTest" table="masjob" lazy="false">
    <id name="Id" column="kode" type="string">
      <generator class="native"></generator>
    </id>
    <property name="Ket" column="ket" />
  </class>
</hibernate-mapping>

6. Create Configuration File so NHibernate knows the database by right-click on the project, select "New Item...", select "Application configuration File", then type the following to "App.config".

<?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.MySqlDataDriver
      </property>
      <property name="connection.connection_string">
        Server=localhost;Database=myDB;User ID=myname;Password=mypass;
      </property>
      <property name="dialect">NHibernate.Dialect.MySQLDialect</property>
      <property name="show_sql">false</property>
      <property name="proxyfactory.factory_class">
        NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>
    </session-factory>
  </hibernate-configuration>
</configuration>

7. Assume we already have masjob table on myDB with this structure:

CREATE TABLE masjob (
`kode` int(11) NOT NULL AUTO_INCREMENT,
`ket` varchar(20) NOT NULL,
PRIMARY KEY (`kode`));

We sould have this output. Have a try!

Could not create the driver from NHibernate.Driver.MySqlDataDriver

Here a piece of #C code, I use to create session for NHibernate:

1. Configuration config = new Configuration();
2. config.Configure();
3. ISessionFactory factory = config.BuildSessionFactory();
4.......

Line 3 would raise error something like "Could not create the driver from NHibernate.Driver.MySqlDataDriver"

I pointed to Mysql.Data.dll file in References folder on the Solution Exploreror, than change Copy Global property from false to true. The code could work to open a session.