Nhibernate simple Select, Insert, Update and Delete

nhibernate

Nhibernate is one of the many Object-Relational Mapping (ORM) tools for the .NET platform.

A very quick description of ORM:

ORM is the process of mapping objects to database relations without the requirement of writing SQL statements (such as SELECT, INSERT, UPDATE, DELETE, JOIN, etc etc) generally through the use of XML files.

Nhibernate eliminates the requirement of using ADO.NET, Linq, etc and thus is the back bone of the Data Access Layer (in nThier Architectures).

Requirements for this Tutorial:

This tutorial will be split in 12 steps:

Step 1:- Create a 2-Thier Project

Step 2:- Install Nhibernate

Step 3:- Define Business Objects (Domain Folder)

Step 4:- Define the Mapping (Mappings Folder)

Step 5:- Setting XML Schema

Step 6:- Configure Nhibernate

Step 7:- Create ISessionFactory

Step 8:- Create Table from Schema and Test Connection

Step 9:- Create Repositories

Step 10:- Define Method from Repositories

Step 11:- Testing Add, Update, Delete Method

Before we continue, I want to add that “I know this is a very long and mostly text based tutorial” but I have searched the internet sooo much for a decent working tutorial without errors and complications that this post here should cover it ALL … I also did it again while writing it to make sure no errors are found. So, hold on and read through… EVERYTHING should be there 🙂

So, lets begin …

Step 1:- Create a 2-Thier Project

1. New Web Application and name solution to “MyFirstNhibernate”

2. Add a new Class Library project to the solution and name it “DataLayer” which will be used to connect to the database through Nhibernate

3. Rename the Web Layer to “PresentationLayer” and add reference to the “DataLayer” Project

4. In the “DataLayer” Class Library add the following folders:

— Design (will contain class diagram of all classes in the Domain Folder)

— Domain (will contain classes to represent tables and their properties in the database)

— Mappings  (will contain xml files to map to tables in database through Nhibernate)

— Repositories (method implementation for Select, Insert, Update and Delete in tables of the database)

Step 2:- Install Nhibernate

Nhibernate has to be installed for both Thiers. Make sure you install NuGet to be able to complete this step:

Visual Studio Tools >> Library Package Manager >> Manage NuGet Packages for Solution >> type “Nhibernate” >> Add Nhibernate

1-NuGet-Install-NHibernate

Step 3:- Define Business Objects (Domain Folder)

In the folder “Domain”, add a new class and name it “Book.cs” and copy the following properties:


public class Book
{
public virtual Guid Id { get; set; }
public virtual string Title { get; set; }
public virtual string Author { get; set; }
}

Step 4:- Define the Mapping (Mappings Folder)

Add XML file and make sure to name it with the right convension naming for nhibernate to auto recognize it is a mapping file: use ending .hbm.xml

Lets name it “Book.hbm.xml” for this example to match the Book class in the Domain.

IMP: Click on the newely created xml file, on properties, select  “Embedded Resource” for Build Action

Step 5:- Setting XML Schema and properties

Now we need to set the XML schema for the xml we just created. Lets start by importing the nhibernate xml schema as follow:

1. In Windows Explorer: Open the folder of your project

2. Click packages >> click on the Nhibernate.3.3.2 folder

3. Copy the nhibernate-mapping.xsd file

4. Go to the main directory of your Class Library “DataLayer” and paste it.

So, now we need to define that schema in our mapping  XML file, and that should look like:


<?xml version=”1.0″ encoding=”utf-8″ ?>


<hibernate-mapping xmlns=”urn:nhibernate-mapping” assembly=”MyFirstNhibernate” namespace=”DataLayer.Domain”>
<!–Your Mapping Code Here –>
</hibernate-mapping>

Once the schema has been defined, we need to create entity mapping by listing all columns and their properties In the <!–Your Mapping Code Here –> above:

<class name=”Book”>
<id name=”Id”>
<generator class=”guid” />
</id>
<property name=”Title” not-null=”true” />
<property name=”Author” not-null=”true” />
</class>

 

Step 6:- Configure Nhibernate to SQL Server 2008

Create a new XML file in the main directory of your project and name it hibernate.cfg.xml

On the propertied of hibernate.cfg.xml change the setting “Copy to Output directory” to “Copy Always”

In the hibernate.cfg.xml copy the following Connection:

<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.SqlClientDriver</property>
<property name=”connection.connection_string”>Data Source=XXX.XXX.XXX.XXX;Initial Catalog=DBname;MultipleActiveResultSets=True;Persist Security Info=True;User ID=username;Password=********</property>
<property name=”dialect”>NHibernate.Dialect.MsSql2008Dialect</property>


<property name=”show_sql”>true</property>
</session-factory>
</hibernate-configuration>

P.S do not forget to change the connection string with the right values for the IP, Catalog, UserId and password

Copy this hibernate.cfg.xml file from the DataLayer into the Presentation Layer where we will test connection to the database.

Step 7:- Create ISessionFactory (NhibernateHelper)

Create a new class in the main directory of the Class Library and name it “NhibernateHelper.cs” which will create a session factory when the client makes his first request a new session (ONLY ONCE)

Add the following references to the NhibernateHelper class:


using NHibernate;
using NHibernate.Cfg;
using DataLayer.Domain;

Make sure the class is made public and copy the following ISessionFactory methods:


private static ISessionFactory _sessionFactory;


private static ISessionFactory SessionFactoryBook
{
get
{
if (_sessionFactory == null)
{
var configuration = new Configuration();
configuration.Configure();
configuration.AddAssembly(typeof(Book).Assembly);
_sessionFactory = configuration.BuildSessionFactory();
}
return _sessionFactory;
}
}


public static ISession OpenSessionProduct()
{
return SessionFactoryBook.OpenSession();
}

Step 8:- Create Table from Schema and Test Connection

Now lets check if our code is working and to do so we will create the Book table from the schema created.

In the “DataLayer” create a new class and name it “SchemaGenerator.cs”, add the following references:

using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;
using DataLayer.Domain;

And paste the following method (make sure to make the class public):

public void generate_book_schema()
{
var cfg = new Configuration();
cfg.Configure();
cfg.AddAssembly(typeof(Book).Assembly);


new SchemaExport(cfg).Execute(false, true, false);
}

Now, lets go to the “PresentationLayer” and on the Default page add a button “btn_Create_Book_Table”.

On the on_click event call the method we just created in the SchemaGenerator.cs which should look something like:

new DataLayer.SchemaGenerator().generate_book_schema();

Run this page and click the button.

Finally, check your tables in the database and you should see the table “Book” listed within the tables 🙂

Step 9:- Create Repositories (Domain Folder)

Repository interface is part of the domain, while implementation will be held in a sub folder of the domain to keep the domain persistant ignorant (PI).

In the Domain Folder, Add>> New Item>> Interface >> name it  “IBookRepository”

Set the interface to public and add the following code for the Select, Insert, Update and Delete (a.k.a CRUD create, read, update, delete):

public interface IBookRepository
{
void Add(Book book);
void Update(Book book);
void Delete(Book book);
Book GetById(Guid Id);
}

Step 10:- Define Method from Repositories (Repositories Folder)

Add a new class and name it “BookRepository” where implementation of each method will take place.

Unless we  inheritance from IBookRepository which we created above, it will give us an error so do not forget to inherit the interface.

So lets implement the first method, Add:

public void Add(Book book)
{


using (ISession session = NhibernateHelper.OpenSessionBook())
using (ITransaction transaction = session.BeginTransaction())
{
try
{
session.Save(book);
//Save Changes in Database
transaction.Commit();
}
catch (Exception ex)
{
//If error occurs, all changes will be reverted
transaction.Rollback();
}
}

}

So thats our first method which creates an ISession for transactions which allow us to rollback if an exception occurs. This is mostly useful when update multuple tables.

Now implement the Update and Delete which are pretty much the same expect you will need to change the session.Save(book) accordingly to session.Update(book) and session.Delete(Book).

Last method is the Get Row by a specific Id which in this is using a GUID / uniqueidentifier:

public Book GetById(Guid Id)
{
using (ISession session = NhibernateHelper.OpenSessionBook())
return session.Get<Book>(Id);
}

Unless you implement all 4 methods, a similar error to the following will keep showing up:

‘DataLayer.Repositories.BookRepository’ does not implement interface member ‘DataLayer.Domain.IBookRepository.Update(DataLayer.Domain.Book)’

Step 11:- Testing Add, Update, Delete Method

1. Testing the ADD Method:

3.Add-Item

In our Web Project, the “PresentationLayer” in the Default page or any other page you prefer add the following table:

<fieldset>
<legend> Book Details: </legend>
<table>
<tr>
<td>Title:</td>
<td><asp:TextBox ID=”txt_title” runat=”server”></asp:TextBox></td>
</tr>
<tr>
<td>Author:</td>
<td><asp:TextBox ID=”txt_author” runat=”server”></asp:TextBox></td>
</tr>
<tr>
<td colspan=”2″><asp:Button ID=”btn_add_book” runat=”server” Text=”Add New Book”
onclick=”btn_add_book_Click” /></td>
</tr>
</table>
</fieldset>

In your code behind add the on_click event and the required references to DataLayer.Domain and DataLayer.Repositories:

protected void btn_add_book_Click(object sender, EventArgs e)
{
var book = new Book { Title = (txt_title.Text), Author = (txt_author.Text) };
IBookRepository repository = new BookRepository();
repository.Add(book);
}

Now, run the page in your browser and click the button “Add New Book”

Open your database >> Right-Click table Book >> Show Table Data:

4.Add-Item-confirm-db

and there is our first row!!!!

2. Testing the Update Method:

Lets first copy the Guid (Id) of our first book and paste it somewhere easy to find so that we can edit that row.

In the same table we created above, add the following row:

<tr>
<td colspan=”2″><asp:Button ID=”btn_update_book” runat=”server” Text=”Update Book”
onclick=”btn_update_book_Click” /></td>
</tr>

and in the code behind lets add its on_click event:

protected void btn_update_book_Click(object sender, EventArgs e)
{
var book = new Book { Id = new Guid(txt_id.Text), Title = (txt_title.Text), Author = (txt_author.Text) };
IBookRepository repository = new BookRepository();
repository.Update(book);
}

5.Update-Item

Now, in your browser copy and paste the guid in the ID textbox, and the new title and author… click “Update Book”

Lets confirm the update, refresh the table content and you should see your changes there:

6.Update-Item-confirm-db

3. Testing the Delete Method

Lets add a new button “Delete Book” to the current html table:

<tr>
<td colspan=”2″><asp:Button ID=”btn_delete_book” runat=”server” Text=”Delete Book”
onclick=”btn_delete_book_Click” /></td>
</tr>

Also as we did before, add the on_click event in the code behind:

protected void btn_delete_book_Click(object sender, EventArgs e)
{
var book = new Book { Id = new Guid(txt_id.Text) };
IBookRepository repository = new BookRepository();
repository.Delete(book);
}

Run in your browser >> copy and paste the same guid so that we delete that same row:

7.Delete-Item

The ID is enough for it to work, click “Delete Book” button and refresh your table one last time:

7.Delete-Item-confi

4. Testing SELECT ( Get Book By ID )

Our last method to test is the Select row by ID, so lets start by adding a new button to the html table:

<tr>
<td colspan=”2″><asp:Button ID=”btn_get_book_by_id” runat=”server” Text=”Get Book By ID”
onclick=”btn_get_book_by_id_Click” /></td>
</tr>

Add the code behind:

protected void btn_get_book_by_id_Click(object sender, EventArgs e)
{
Guid Id = new Guid(“4823c5b6-7b70-459b-9a8b-86beb624a623“);     //Replace GUID with your Id
IBookRepository repository = new BookRepository();
var book = repository.GetById(Id);

txt_title.Text = book.Title;
txt_author.Text = book.Author;
}

Now we need to add a new row in the Book table, so run the Page, fill the Title and Author and click “Add New Book”

Refresh the table and copy the Id (guid)

Paste the GUID in the code behind where we have Guid Id = new Guid(“”);

Run the Page again and click “Get Book By ID”

8.Select-Item

As in the screenshot above, the Title and the Author of the given ID will be displayed.

And that is done .. hope you did not encounter any issues which have not been covered in this tutorial. If so do not hasitate to ask or if you have the solution to it, helpful comments are appreciated.

Goodluck and enjoy 🙂

Download <a href=”http://www.twiggle-web-design.com/tutorials/nhibernate/nhibernate.html“> Project Demo </a>

//

8 thoughts on “Nhibernate simple Select, Insert, Update and Delete

  1. Pingback: Fluent Nhibernate Made Easy | Rochcass's Blog
  2. Hi Rochcass,

    Your step by step tutorial is very helpful for NHibernate beginner. Thx alot for posting.
    Could I have your project code? I’m up to step 8, when I run it I hit an error. I’m not able to figure it out.

    Thx in advance

  3. Hi, thx a lot for this tutorial. Nevertheless, i am also lock at step 8. The javascript is not working, it says that DataLayer is undefined. I added the DataLayer project as reference in the PresentationLayer. What is missing?
    thanks!

    • Hi David,

      Im not sure what you mean by the javascript is not working. Step 8 is basically creating a class named “SchemaGenerator.cs” in the folder “DataLayer” where you write the code indicated in the article.

      Meanwhile in the presentation layer, in your aspx page you add an asp button, fire the on_click event and in the code behind you add “new DataLayer.SchemaGenerator().generate_book_schema();”.

      Please review this step and let me know if you manage to find the mistake.

      If you still have doubts on this step, feel free to download a sample of this project from this link:

      http://www.twiggle-web-design.com/tutorials/nhibernate/nhibernate.html

      Thanks and Goodluck.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s