Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Using CUBRID with NHibernate - Beginner's Guide


Introduction

As you probably know already, NHibernate is a .NET port of the well-known Hibernate Java framework.It is an open source project which implements persisting objects (an ORM) to and from an underlying relational database – MySQL, PostgreSQL, SQL Server, Oracle etc.

What we have done in the scope of the CUBRID NHibernate project was to add support in NHibernate for CUBRID.

 

Note: This tutorial assumes that you are familiar with Hibernate, from which NHibernate is derived!

If you are not familiar, we strongly suggest reading more about Hibernate, before proceeding further with this tutorial.

And to help you get started fast with CUBRID and NHibernate working together, we have prepared a couple of tutorials!

This tutorial will introduce you to the very basics of working with NHibernate and CUBRID; it will be followed shortly by an “advanced tutorial”.

So let’s get started!

 

Software prerequisites

Before you start developing .NET applications with CUBRID and NHibernate, you will need the NHibernate libraries and the CUBRID ADO.NET Data provider library(Cubrid.Data.dll):

 

Setup the environment

 

Installing NHibernate with the CUBRID extensions

If you've downloaded the NHibernate with CUBRID extensions binaries in a zip file from here, all you need to do is extract that files. In this tutorial we will use this folder: C:\NHibernateCUBRID. And that's it, NHibernate is installed - there's nothing else to be done to get NHibernate.

All is left to do now is add the required references to you project. We will see in the following sections how to create a MS Visual Studio project with the proper references and settings.

 

Compiling NHibernate with CUBRID extensions

If you want to compile NHibernate with CUBRID extensions from the source code, all you need to do is load in Visual Studio 2012 the solution file located in the \NHibernate\src\ folder and build the NHibernate project:

cubrid_extensions.png 

 

Creating a MS Visual Studio project

Now that we have the NHibernate binaries in place, let's see how to create a Visual Studio project and add the required references and settings in order to use NHibernate with CUBRID database.

Since we're not going to be focusing on the user interface in this tutorial, we will see how to create a C# console application project.

So, fire up MS Visual Studio and create a new C# console application. Let's call it HelloNHibernate.

Please note that it is strongly recommended to stick to this naming since in many cases these names may be used in other parts of the tutorial.

project.png 

Next, we have to add the references to NHibernate:

files.png

…and the CUBRID ADO.NET Data provider:

cubrid_data.png 

The last step is to configure NHibernate for use with CUBRID.

We do that by adding to our project a new xml configuration file called hibernate.cfg.xml. In order for NHibernate to use it as its configuration file we have to set the Build Action property to Embedded Content and its Copy to Output Directory property to Copy always.

The content of the file is as follows:

<?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.CUBRIDDriver
</property>
<property name="connection.connection_string">
server=localhost;database=demodb;port=33000;user=public;password=
</property>
<property name="dialect">
NHibernate.Dialect.CUBRIDDialect
</property>
</session-factory>
</hibernate-configuration>

Here is a quick intro of the properties declared inside the xml file:

  • connection.provider sets which connection provider should be used by NHibernate to connect to the database.
  • connection.driver_class sets which driver should be used and in this case, when using CUBRID, CUBRIDDriver is the logical choice
  • connection.connection_string is the connection string to the database. In this tutorial we will use the default CUBRID Connection string.
  • dialect states the NHibernate class name that enables certain platform dependent features, and in this case, since we are using CUBRID, the obvious choice is CUBRIDDialect

That is all. We are now ready to start coding with NHibernate and CUBRID!

A first NHibernate CUBRID application

For a first example of using NHibernate with CUBRID, we will use the Athlete table from the demodb database, which comes with your CUBRID install and we will see how to retrieve data from that table.

athlete.png 

 

The NHibernate persistence class

Let’s create a new class in your project named Athlete with the properties below (note that NHibernate is case sensitive, so our property names will need to exactly match our NHibernate mapping that we will be creating later):

 

namespace HelloNHibernate
{
    public class Athlete
    {
        virtual public int code { get; set; }
        virtual public string name { get; set; }
        virtual public string gender { get; set; }
        virtual public string nation_code { get; set; }
        virtual public string athlete_event { get; set; }
    }
}

 

The mappings

To be able to use the Athlete class, it is necessary to create a NHibernate mapping file that contains the metadata that NHibernate uses for the object/relational mapping, i.e., connecting the class declaration, the properties to columns, and keys in the database tables.

So, we will create a new xml file called Athlete.hbm.xml and set its Build Action property to Embedded Resource. The content of the file is:

Within the hibernate-mapping tag, we're referencing both the assembly and namespace that the classes (POCOs) below reside in.  This is handy when your mapping files end up in a separate namespace or assembly than your model.

  • The class element represents our mapping to a single POCO.  The name represents the name of a class within the assembly and namespace from above, while the table attribute tells NHibernate which table or view in our database to map to.
  • The id element tells NHibernate which database field and corresponding object property to use as a unique key.  In this case, we are using the Code field.
  • The generator element tells NHibernate how to create unique IDs for new entities.  Since CUBRID supports identity columns through AUTO_INCREMENT, we specify the generator class as 'identity'.
  • The property tag is the tag you will see the most.  This simply maps a POCO property to the corresponding field in our table or view.
<?xml version="1.0"encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="HelloNHibernate" namespace="HelloNHibernate">
<class name="Athlete" table="Athlete">
<id name="code">
<generator class="identity" />
</id>
<property name="name"/>
<property name="gender"/>
<property name="nation_code"/>
<property name="athlete_event" column="event"/>
</class>
</hibernate-mapping>

 

The code

We are now ready to do some coding... In this example we will query the Athlete table and print the first ten entries in the console. The code is placed directly in the main method of our console application:

private static void Main(string[] args)
{
    Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof (Athlete).Assembly);
    ISessionFactory sessionFactory = cfg.BuildSessionFactory();
    using (var session = sessionFactory.OpenSession())
    {
        IQuery query = session.CreateQuery("FROM Athlete");
        IList<Athlete> athletes = query.List<Athlete>();
        Console.WriteLine("There are {0} athletes in the databse.\n", athletes.Count);
        Console.WriteLine("The first 10 athletes are:\n");
        for (var i = 0; i < 9; i++)
        {    
            Console.WriteLine(
                string.Format("|{0}|{1}|{2}|{3}|{4}|",
                AlignCenter(athletesi.code.ToString(), 5),
                AlignCenter(athletesi.name, 30),
                AlignCenter(athletesi.gender, 5),
                AlignCenter(athletesi.nation_code, 5),
                AlignCenter(athletesi.athlete_event, 20)));
        }
    }
    Console.WriteLine(@"Press any key to continue...");
    Console.ReadKey();
}

Our first line of the main method is simply creating an instance of the NHibernate Configuration object based on the values in our hihernate.cfg.xml file and then we load our mapping files.  These are used to map our POCOs to our persistent database objects. 

There are several techniques for loading these in, but all will require an assembly to be passed as a parameter.  NHibernate will then look for all persistence mapping data within those assemblies:

Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof (Athlete).Assembly);

Next, we open a Session from our SessionFactory.  All of our NHibernate persistence operations will be handled within the context of a Session:

using (var session = sessionFactory.OpenSession())

With a session created, we can now get down to business.  In cases where we are simply retrieving data, so our Session object is all we need.

Retrieving Athlete objects from the database is performed by creating aIQuery object from our session and execute the desired query using it. In addition, we use the List() method to retrieve a list of all the athletes in the database:

IQuery query = session.CreateQuery("FROM Athlete");
IList<Athlete> athletes = query.List<Athlete>(); 

 

Note that for printing the entries in the Athlete table in a easily readable format we use the AlignCentre method with the following code:

static string AlignCenter(string text, int width)
{
    if (string.IsNullOrEmpty(text))
    {
        return new string(' ', width);
    }
    else
    {
        return text.PadRight(width - (width - text.Length) / 2).PadLeft(width);
    }
} 

The output from running the above code is:

output_code.png

 

An example which implements Update operations

In this example we will show how to handle the basic SQL operations (insert, select, update and delete) on all the standard data types.

For this purpose, we will create a table that has columns of all the standard SQLdata types. The SQL for creating this table is:

create TABLE DataTypes (
c_integer INTEGER AUTO_INCREMENT,
c_smallint SMALLINT,
c_bigint BIGINT,
c_numeric numeric(10,2),
c_float FLOAT,
c_decimal decimal(19,5),
c_double DOUBLE,
c_char char(1),
c_varchar varchar(30),
c_time TIME,
c_date DATE,
c_timestamp TIMESTAMP,
c_datetime DATETIME,
c_monetary MONETARY,
c_string STRING,
c_bit BIT(8),
c_varbit bit varying(8),
primary key (c_integer))

 

The Persistent class

As we did in the previous example, to persist our database table in NHibernate we create a class called DataTypes:

namespace HelloNHibernate
{
   public class DataTypes
   {
       virtual public int c_integer { get; set; }
       virtual public short c_smallint { get; set; }
       virtual public long c_bigint { get; set; }
       virtual public decimal c_numeric { get; set; }
       virtual public float c_float { get; set; }
       virtual public decimal c_decimal { get; set; }
       virtual public double c_double { get; set; }
       virtual public string c_char { get; set; }
       virtual public string c_varchar { get; set; }
       virtual public DateTimec_time { get; set; }
       virtual public DateTimec_date { get; set; }
       virtual public DateTimec_timestamp { get; set; }
       virtual public DateTime c_datetime { get; set; }
       virtual public intc_monetary { get; set; }
       virtual public String c_string { get; set; }
       virtual public byte c_bit { get; set; }
       virtual public byte c_varbit { get; set; }
   }
} 

 

The Mappings

Again, to map the database column to our persistent class we have to create a mapping file called DataTypes.hbm.xml and set its Build Action property to Embedded Resource. The content of this file is as follows:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="HelloNHibernate" namespace="HelloNHibernate">
<class name="DataTypes" table="DataTypes">
<id name="c_integer">
<generator class="identity" />
</id>
<property name="c_smallint" />
<property name="c_bigint" />
<property name="c_numeric" />
<property name="c_float" />
<property name="c_decimal" />
<property name="c_double" />
<property name="c_char" />
<property name="c_varchar" />
<property name="c_time" />
<property name="c_date" />
<property name="c_timestamp" />
<property name="c_datetime" />
<property name="c_monetary" />
<property name="c_string" />
<property name="c_bit" />
<property name="c_varbit" />
</class>
</hibernate-mapping>

 

The code

Now we have the persistent class and the mapping file ready.

Next we will implement a simple console application enables the user to perform the basic CRUD operations. The application will prompt the user for a supported type of operation and perform it. After that, the user is prompted again for a type of operation until he inputs a "Q" character which will close the application.

The full code for the application can be downloaded from here.

 

Insert

Whether we have one or several properties, creating our new persistent object follows the same pattern, and can be accomplished in just a few lines of code. 

  • Create a Transaction within which you will perform your updates
  • Execute the Save method on your current session, passing the object you wish to persist as a parameter
  • Commit your transaction
private static void Create(ISession session, DataTypes entry)
{
    using (var trans = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        session.Save(entry);
        trans.Commit();
    }
}

 

Select

Retrieving object from the database is performed by creating aIQuery object from our session and execute the desired query using it. In addition, we use the List() method to retrieve the object as a list:

private static void Retrieve(ISession session)
{
    IQuery query = session.CreateQuery("FROM DataTypes");
    IList<DataTypes>dataTypes = query.List<DataTypes>();
    Console.WriteLine("There are {0} entries in the DataTypes table.", dataTypes.Count);
    for (var i = 0; i <dataTypes.Count; i++)
    {
       Console.WriteLine("Entry number {0} has c_integer {1} and was created at {2}",
          i+1, dataTypesi.c_integer, dataTypesi.c_datetime);
    }
    Console.WriteLine();
}

After performing these operations, the console output will look like this:

insert.png 

Please note that for appearance and simplicity purposes we didn't output to the console all the column values retrieved from the database. Instead, we output just the c_integer column value, which is the primary key of the table, and the c_datetime column value to differentiate between our entries. 

 

Update

For simplicity purposes we will just update the c_datetime column. We will update a persistent object the same way we created one - by executing a method against the session object within the context of a transaction, as shown below:

private static void Update(ISession session)
{
    DataTypespGet = session.Get<DataTypes>(1);
    pGet.c_datetime = DateTime.Now;
    using (var trans = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        session.Update(pGet);
        trans.Commit();
    }
}

Let's see it in action:

update.png

 

Delete

For our last example, we will be deleting a record.  Again, since this will affect our persistent entity, we will perform our delete method within the scope of a transaction, and pass the object we wish to delete as a parameter:

private static void Delete(ISession session)
{
    DataTypespGet = session.Get<DataTypes>(1);
    using (var trans = session.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        session.Delete(pGet);
        trans.Commit();
    }
}

delete.png

And this concludes this first tutorial…!

In the next tutorial, we will go through some more advanced topics:

  • Tables relationships
  • Working with special CUBRID data types: LOB etc.

 

References

CUBRID NHibernate Wiki page

http://www.cubrid.org/wiki_apis/entry/cubrid-nhibernate-support

NHibernate home page

http://nhforge.org/

CUBRID ADO.NET driver

http://www.cubrid.org/wiki_apis/entry/cubrid-ado-net-driver

CUBRID NHibernate source code

http://svn.cubrid.org/cubridapis/NHibernate

CUBRID-enabled NMG tool

http://nmg.codeplex.com

We hope you enjoyed this tutorial! Stay tuned for the next part!

As always, we kindly ask you to let us know your feedback and suggestions, so we can improve!

Thank you!

The CUBRID API team

comments powered by Disqus
Page info
viewed 1260 times
translations en
Author
posted last year by
CUBRID
Contributors
updated last year by
View revisions
tagged
Share this article