Monday, August 9, 2010

NHibernate and MS Access - it's simple, but...

Recently I got the task to get some data from MS Access database using NHibernate. As for true it was my first coding experience with NHibernate and Fluent NHibernate, so I was very glad that I was present not too long time before on 4th meeting of Lviv .Net User Group where Andriy Buday made a quite good presentation about this ORM framework.

The code had to be very simple, and you should to change just couple of lines in the configuration to make it work with MS Access. If you use Fluent NHibernate (like me) for writing configuration, then you can get something like the following piece of code.

private static ISessionFactory CreateSessionFactory(string databaseFile)
{
    return Fluently.Configure()
        .Database(
            JetDriverConfiguration.Standard.ConnectionString(c => c.DatabaseFile(databaseFile))
        )
        .Mappings(m => m.FluentMappings.AddFromAssemblyOf<Person>())
        .BuildSessionFactory();
}

So everything was fine, I was proud with myself ("Yoo-hoo, I did!") , but when I tried to run tests...
Could not load file or assembly 'NHibernate.JetDriver' or one of its dependencies. The system cannot find the file specified.
 The reason is simple - NHibernate.JetDriver.dll is not part of the NHibernate distribution. You can find it in the NHibernate Contrib project on Source Forge. But don't try to find ready DLL - only source codes are available. Checkout out it with SVN here: https://nhcontrib.svn.sourceforge.net/svnroot/nhcontrib/trunk/src/NHibernate.JetDriver/. Build it in any way you wish. As for me I used included VS 2008 solution.

Fine, I got NHibernate.JetDriver.dll, I was proud with myself even more than in previous time, I tried to run tests again... What I got? Here it is:
 Could not load file or assembly 'NHibernate, Version=2.1.0.4000, Culture=neutral, PublicKeyToken=aa95f207798dfdb4' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
When you will build NHibernate.JetDriver.dll reference those DLLs from the NHibernate distribution that you are using in your project. The NHibernate version in NHibernate.JetDriver folder is 2.1.0.4000, and I used version 2.1.2.

So at last I managed to make NHibernate to work with MS Access. Returning to the title of this post - it's simple but it is tricky:)

P.S. If you are working under 64 bit Windows you can get additional exception "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine" for dessert. Make sure that you selected the target cpu to x86 in the advanced compiler options. There is not a 64 bit version of Jet.

6 comments:

  1. Did not know how to use Nhibernate with MS Access. Great that you posted this.

    Versions in .net is very common thing you will face, I think.

    P.S. Thanks for mentioning me :)

    ReplyDelete
  2. I'm happy that somebody founds this information useful!

    P.S. You are welcome as long as you will make good presentations:)

    ReplyDelete
  3. I don't know how it relates to NHibernate, but 64-support for Jet/ACE has been implemented by MS in Access 2010. As an Access developer, I don't have to deal with this issues, so I haven't checked, but it may be that the 64-bit ACE that can be downloaded from MS will include the support necessary to compile for 64-bit.

    ReplyDelete
  4. David, thanks for the comment!
    Yes, you are right, I forgot about 64-bit version of Office 2010 (I tried that code at work where I have Office 2007 installed only) If anyone needs Microsoft Access Database Engine 2010 Redistributable it can be found on the Microsoft Download Center. (It is strange, but Blogger destroys comments with the links within minute O_o)
    Anyway forcing to x86 can still be the option, I don't think that there will be the significant gap in the performance.

    ReplyDelete
  5. Do you have a sample of this project? My project keeps returning no data from access and it's driving me nuts! I think it's a mapping problem.

    ReplyDelete
  6. Thanks for documenting this. Very useful.

    ReplyDelete