Home > .net, programming > Mono, NHibernate, MySQL – A complete example

Mono, NHibernate, MySQL – A complete example


This article aims to show you how to use Mono, MonoDevelop 2.4.2 and NHibernate (3.2.0.Alpha2) to connect to a MySQL database on Mac OSX 10.6, Snow Leopard. (I’m assuming the solution can be adapted to other *NIX variants, but I’ve only tried it on Snow Leopard). The examples I found online were frequently Windows only. I ran into a lot of problems along the way, which I am documenting here to prevent others from having the same issues.

Step 1 – MonoDevelop:

Download and install MonoDevelop. I am using version 2.4.2.

Step 2 – NHibernate:

Download NHibernate zip (I am using version 3.2.0.Alpha2), extract to a location you will remember later (I put it on my Desktop)

Step 3 – Install MySQL

3a.) Download MySQL, and install it. I used the 64 bit DMG Archive for Mac. I opened the package, and ran all of the installers. Make sure you install the PrefPane support too. When you go into the System Preferences, you should see an entry for MySQL.

Pref pane

Pref pane

Go into the MySQL settings and choose Start MySQL Server. After this is done, launch a terminal window. Type mysql -u root in the window; if everything is configured correctly, you should get a command prompt that looks like the following:

$ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1553 Server version: 5.5.11 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 

Type the following at the command line prompt to create a new database and user we will use later: (Note that in a production system you should not have a dev account with such a simple password, and certainly not with all privileges.)

mysql> CREATE DATABASE products; Query OK, 1 row affected (0.00 sec) mysql> CREATE USER 'dev'@'localhost' IDENTIFIED BY 'test_password'; Query OK, 1 row affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'dev'@'localhost' WITH GRANT OPTION; Query OK, 1 row affected (0.00 sec) 

At this point, we now have an account named ‘dev’ with password ‘test_password’, and a database named ‘products’. We will use that information when connecting to the database using NHibernate.

Step 4 – Download MySQL Driver

In order to use MySQL with .NET, you need a database driver. From the MySQL Connectors page, choose Connector/Net, and then choose .NET & Mono from the dropdown. Download the file; it should be entitled mysql-connector-net–6.3.6-noinstall.zip.

When the file finishes downloading, unzip it and place the folder somewhere easy to get to; I put it on my Desktop.

Step 5 – Create Domain Objects / Hibernate configs

Follow the instructions on Your first NHibernate based application, up until the section “Test the Setup” to create all of the domain classes. We need to modify the hibernate.cfg.xml file to match what’s in this gist.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
 <!-- an ISessionFactory instance -->
 <session-factory>
  <!-- properties -->
  <property name="connection.provider">
    NHibernate.Connection.DriverConnectionProvider
  </property> 
  <property name="connection.driver_class">
    NHibernate.Driver.MySqlDataDriver
  </property> 
  <property name="connection.connection_string">
    <!-- Found from http://www.connectionstrings.com/mysql; note that the 'dev' and 'test_password' 
    match the user we created, and 'products' matches the Database -->
Server=localhost;Database=products;Uid=dev;Pwd=test_password;
  </property>

  <!-- Will automatically create a fresh table each time we create an ISessionFactory object -->
  <property name="hbm2ddl.auto">create-drop</property>

  <property name="dialect">
    NHibernate.Dialect.MySQLDialect
  </property>
  <property name="show_sql">true</property>
 </session-factory>
</hibernate-configuration>

Note that the dialect, connection.driver_class, and connection.connection_string properties have all been changed; I also added an hbm2ddl.auto property. You can read more about that here.

Fix missing references

At this point, we need to ensure that all of the dlls we need are referenced correctly. Right click on the References node, and choose Edit references. Choose the .Net Assembly tab, and browse to where you saved the mysql-connector-net-6.3.6-noinstall folder; I placed mine on the desktop. Choose the v2 folder, select all of the dlls, and choose add.

At this point your screen should look something like the following:

Edit references

Edit references

edit references

If you don’t have an NHibernate.dll listed in your references, you will need to add this as well. Follow the same steps as previously listed, and add the NHibernate.dll found in NHibernate-3.2.0.Alpha2-src/lib/net/3.5/. I added all of the dlls in that folder, but that’s overkill.

Add main entry point

At this point, you’re almost ready to get started. Create a file named Main.cs at the same level as your hibernate.cfg.xml file. Use the following code:

using System;
using System.Collections.Generic;
using NHibernate;
using NHibernateTest.Domain;
using NHibernate.Tool.hbm2ddl;

namespace NHibernateTest
{
    class MainClass
    {
        public static void Main (string[] args)
        {
            IList products;

            // Don't need to use schema export because of the hbm2dll property.
            var cfg = new NHibernate.Cfg.Configuration();
            cfg.Configure();
            // ensure that mapping hbm.xml file is loaded
            cfg.AddAssembly(typeof(MainClass).Assembly);

            Product p = new Product() {Name="Captains of Crush Gripper #1", Category="fitness" };

            ISessionFactory factory =
                cfg.BuildSessionFactory();

            using (ISession session = factory.OpenSession())
            {
                session.Save(p);
                session.Flush();

                ICriteria sc = session.CreateCriteria();
                products = sc.List();
                Console.WriteLine(products[0].Name);
                session.Close();
            }
            factory.Close();

            Console.WriteLine( products.Count );

            Console.WriteLine ("Hello World!");
        }
    }
}

My namespace might be slightly different from yours; modify accordingly.

Build and run the project. You should get output like the following:

NHibernate: INSERT INTO Product (Name, Category, Discontinued, Id) VALUES (?p0, ?p1, ?p2, ?p3);?p0 = 'Captains of Crush Gripper #1' [Type: String (28)], ?p1 = 'fitness' [Type: String (7)], ?p2 = False [Type: Boolean (0)], ?p3 = a9f8e586-727f-49b9-98da-59f534163ae7 [Type: Guid (0)] NHibernate: SELECT this_.Id as Id0_0_, this_.Name as Name0_0_, this_.Category as Category0_0_, this_.Discontinued as Disconti4_0_0_ FROM Product this_ Captains of Crush Gripper #1 1 Hello World! Abort trap Press any key to continue... 

Conclusion/Troubleshooting

I hope this helps newbies like myself get up and running in a Mono environment with NHibernate and MySQL. The complete code can be found on Github.

MappingException

Unhandled Exception: NHibernate.MappingException: No persister for: NHibernateTest.Domain.Product 

If you get an exception like this, the Product.hbm.xml file is not being loaded correctly. Make sure that you have the following line:

cfg.AddAssembly(typeof(MainClass).Assembly); 

Conversely, make sure that your Product.hbm.xml file is set to be ‘embed as resource’. (Right click, choose Build Action, Embed as resource).

embed as resource

embed as resource

Could not compile the mapping document

Unhandled Exception: NHibernate.MappingException: Could not compile the mapping document: NHibernateTest.Mappings.Product.hbm.xml ---> System.InvalidOperationException: Could not find the dialect in the configuration 

If you get an error like this, you have inadvertently switched the order of your AddAssembly and Configure calls. Make sure you call Configure before you call AddAssembly.

About these ads
  1. paul
    May 3, 2011 at 9:01 am | #1

    Nice writeup! Hopefully this will help others who decide to try the unholy pairing of .Net and Mac. :D

  2. Ivan Mendoza
    June 18, 2011 at 5:42 pm | #2

    you rocks!!

  3. July 22, 2012 at 6:04 am | #3

    Thanks for giving that complete example, really appreciated! (i found it by googling for some seminar stuff, much thanks!!)

    • i82much
      July 26, 2012 at 7:58 pm | #4

      Glad it helped. I found there were no tutorials when I was trying to use those three technologies together, so hopefully the next searcher can stumble onto the post like you did

  4. fat burning workout gym
    December 20, 2013 at 3:30 am | #5

    I like the helpful info you provide in your articles.
    I will bookmark your weblog and check again here
    frequently. I am quite certain I will learn many new stuff right here!
    Best of luck for the next!

  1. No trackbacks yet.

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

%d bloggers like this: