.Net Ramblings

I am continuing to play with NHibernate and wanted to use MS Access as the database back end. I thought it would be an easy migration that required only a couple NHibernate configuration settings being changed below.

<property name="dialect">
  NHibernate.JetDriver.JetDialect, NHibernate.JetDriver
</property>
<property name="connection.driver_class">
  NHibernate.JetDriver.JetDriver, NHibernate.JetDriver
</property>
<property name="connection.connection_string">
  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb
</property>

But when I started up NHibernate, I received the following error:

System.IO.FileNotFoundException: Could not load file or assembly ‘NHibernate.JetDriver’ or one of its dependencies. The system cannot find the file specified.

File name: ‘NHibernate.JetDriver’

It turns out I needed to add the NHibernate.JetDriver DLL to the project. This DLL is not part of the main NHibernate project. It is located in the NHibernate Contrib project on Source Forge.

Building the NHibernate.JetDriver

1. Checkout the source code from Source Forge
   1. url: https://nhcontrib.svn.sourceforge.net/svnroot/nhcontrib/trunk
2. Build the NHibernate.JetDriver project
   1. Directory Location from trunk: /trunk/src/NHibernate.JetDriver
   2. The easiest way to build it is using the NAnt script included in the project (default.build)
      1. the NAnt script creates a “build” folder and places the driver in there
   3. You can also open the project and build it from there
      1. The driver will be located in: NHibernate.JetDriver\bin\Debug

Once you create the NHibernate.JetDriver all you need to do it copy it to your project directory and reference it from your project and then you should be good to use MS Access with NHibernate.

This past week I took the plunge and starting playing with NHibernate. I’ve wanted to use it for quite a while now, but it has a steep learning curve and I could never get a simple app to work. My main problem was that I wanted to use SQL Compact Edition as the database instead of SQL Server, and there aren’t many tutorials on getting the Compact Edition to work.

After much trial and error, I finally got NHibernate to work with SQL Compact Edition. Other than following the basic NHibernate configuration here are the additional steps I had to take to make NHibernate work with SQL Compact Edition.

• Add a reference of the System.Data.SqlServerCe to your project
  • Make sure to update its Copy Local property to True. By default it is set to False
    • When set to false, you will receive one of the two following errors:
    • The IDbCommand and IDbConnection implementation in the assembly System.Data.SqlServerCe could not be found. Ensure that the assembly System.Data.SqlServerCe is located in the application directory or in the Global Assembly Cache. If the assembly is in the GAC, use <qualifyAssembly/> element in the application configuration file to specify the full name of the assembly.
    • Could not create the driver from NHibernate.Driver.SqlServerCeDriver
      
• For computers running 64 bit processors: Make sure the project’s platform target is x86, not x64 nor Any CPU
  • This is a result of the ADO.Net provider not working with 64 bit systems
  • The setting is found in project Properties window under the Build tab.
    • Express Editions of Visual Studio do not have this drop down option in the Build tab. You will have to set the platform target by manually updating the .csproject file.
    • See this post for more information on updating the .csproj file
• Add SQL Compact Edition DLL’s to the project
  • These DLL’s are located in C:\Program Files\Microsoft SQL Server Compact Edition\v3.5
    • SQL Compact Edition must be installed on the computer
  • Add all the DLL’s starting with ‘sqlce‘
  • Set the Copy To Output Directory to true for all these files
  • This only needs done if you don’t want people to install SQL Compact Edition on their computer
• Nhibernate Configuration Settings
  • Latest version of NHibernate does not need the hibernate prefix to every setting
  • Below are 2 different ways to configure NHibernate
  • Programmatic Configuration

    IDictionary properties = new Dictionary();
    properties.Add("connection.driver_class", "NHibernate.Driver.SqlServerCeDriver");
    properties.Add("dialect", "NHibernate.Dialect.MsSqlCeDialect");
    properties.Add("connection.provider", "NHibernate.Connection.DriverConnectionProvider");
    properties.Add("connection.connection_string", "Data Source=MyDatabase.sdf; Password='some password'");
    Configuration conf = new Configuration();
    conf.Properties = properties;

  • App.config Configuration

    <?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="dialect">
            NHibernate.Dialect.MsSqlCeDialect
          </property>
          <property name="connection.driver_class">
            NHibernate.Driver.SqlServerCeDriver
          </property>
          <property name="connection.connection_string">
            Data Source=MyDatabase.sdf; Password='some password'
          </property>
        </session-factory
      </hibernate-configuration>
    </configuration>

You can also download a small sample app that uses SQL Compact Edition.