Fluent NHibernate Tutorial | C# Windows CRUD Example

NHibernate is a great ORM framework but I find the use of XML to be kind of a pain in a quite significant part of the body (the head). XML is too verbose and isn’t checked by the compiler. Fluent NHibernate is basically NHibernate without HBM.XML. It uses strongly typed C# code to map class attributes to database table fields. In this Fluent NHibernate tutorial, we are going to cover the following topics

Topics covered in this tutorial

• How to install Fluent NHibernate using NuGet
• How to map entities in Fluent NHibernate
• How to connect to SQL Server using NHibernate with fluent
• Fluent NHibernate tutorial data grid example
• Fluent NHibernate tutorial CRUD C# example
• Fluent NHibernate CRUD C# Example Complete Source Code
• Summary

How to install Fluent NHibernate using NuGet

If you are new to NHibernate then I recommend you read this article 4 ways in which NHibernate can make you a productive database driven application developer. If you would like to learn how to install Fluent NHibernate using NuGet package manager then I recommend you read this article We will use the customer ordering system case study database for this example. We will only focus on the customer object for simplicity’s sake in this Fluent NHibernate tutorial.

How to install fluent NHibernate using NuGet

There is no rocket science involved, read this article on how to install Fluent NHibernate using fluent

How to map entities in Fluent NHibernate

Let’s start with creating a new project in Visual studio Create a new C# windows application with the following namespace CodeBlog.FluentNHibernate The application design should be as follows  The application structure of your project should be as follows HERE,

• Mappings folder - contains your Fluent NHibernate mappings, class entity to database fields
• Models folder – contains the class representing entities such as customers, employees, e.t.c., with their attributes
• “frmCustomers.cs” is the hauntingly beautiful user interface displayed in the first picture

Note: The assumption made here is you are familiar with Visual Studio and how to design user interfaces.

Models

Create a new class named Customers.cs in Models folder if you havent already done that. Add the following source code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CodeBlog.FluentNHibernate.Models
{
    class Customers
    {
        public virtual int customer_id { get; protected set; }
        public virtual string name { get; set; }
        public virtual string email { get; set; }
        public virtual string contact_person { get; set; }
        public virtual string postal_address { get; set; }
        public virtual string physical_address { get; set; }
        public virtual string contact_number { get; set; }
    }
}

HERE,

• “public virtual data type variable_name {get; set}” defines the Customers class attributes. The attributes represent field names in the database

Mappings

Mappings link the class attributes to the database table fields. Mappings inherit the ClassMap class; the ClassMap is in the FluentNHibernate.Mapping namespace. Create a new class named CustomersMap.cs in the Mappings folder Add the following source code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CodeBlog.FluentNHibernate.Models;
using FluentNHibernate.Mapping;

namespace CodeBlog.FluentNHibernate.Mappings
{
    class CustomersMap : ClassMap<Customers>
    {
        public CustomersMap()
        {
            Id(x => x.customer_id);
            Map(x => x.name);
            Map(x => x.email);
            Map(x => x.contact_person);
            Map(x => x.postal_address);
            Map(x => x.physical_address);
            Map(x => x.contact_number);
        }
    }
}

HERE,

• “Id(x => x.customer_id);” defines the primary key customer_id
• “Map(x => x.field_name);” maps the other field names in the table

How to connect to SQL Server using NHibernate with fluent

We will connect to a CustomerRecordsDB database in SQL Server. At this point, we assume you have a working instance of SQL Server. Create a database named CustomerRecordsDB Run the following script to create the customers table and insert dummy records

USE [CustomerRecordsDB]
GO

/****** Object:  Table [dbo].[Customers]    Script Date: 05/06/2015 14:51:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customers](
 [customer_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
 [name] [nvarchar](75) NULL,
 [email] [nvarchar](95) NULL,
 [contact_person] [nvarchar](75) NULL,
 [postal_address] [nvarchar](150) NULL,
 [physical_address] [nvarchar](150) NULL,
 [contact_number] [nvarchar](50) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
 [customer_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [dbo].[Customers]
           ([name]
           ,[email]
           ,[contact_person]
           ,[postal_address]
           ,[physical_address]
           ,[contact_number])
     VALUES
           ('Kode Blog'
           ,'a-team@kode-blog.com'
           ,'Rodrick Kazembe'
           ,'Private Bag WWW'
           ,'Tanzania'
           ,'911'),
     ('Google Inc'
           ,'info@google.com'
           ,''
           ,''
           ,'USA'
           ,'')
GO

  NHibernate uses the ISessionFactory to connect to the database, ISessionFactory objects are immutable, and they cannot be changed once they have been configured. For more details, refer to this article. The ISessionFactory object has two (2) chain methods that we need to configure. The following diagram gives you a visual illustration of the process The following method creates and returns an ISessionFactory object

private static ISessionFactory CreateSessionFactory()
{
    ISessionFactory isessionFactory = Fluently.Configure()
        .Database(MsSqlConfiguration.MsSql2005
        .ConnectionString("Server=.; Database=OrdersDB; Integrated Security=SSPI;"))
        .Mappings(m => m
        .FluentMappings.AddFromAssemblyOf<frmCustomers>())
        .BuildSessionFactory();

    return isessionFactory;
}

HERE,

• “.Database(MsSqlConfiguration.MsSql2005” specifies the version of SQL server that you are using. This code specifies SQL Server 2005 but has been tested on SQL Server 2012 and works just fine. I did this for backwards compatibility.
• “.ConnectionString” specifies the database connection string
• “.Mappings” specifies the mappings that you are using
• “.BuildSessionFactory()” creates the session instance for the configuration

Fluent NHibernate tutorial data grid example

The following code retrieves data from SQL server and populates the datagrid

sessionFactory = CreateSessionFactory();

using (var session = sessionFactory.OpenSession())
{
    string h_stmt = "FROM Customers";

    IQuery query = session.CreateQuery(h_stmt);

    IList<Customers> customersList = query.List<Customers>();

    dgvListCustomers.DataSource = customersList;

    lblStatistics.Text = "Total records returned: " + customersList.Count;

}

HERE,

• “using var session = sessionFactory.OpenSession())” creates the session variable and assigns the open session to it.
• “string h_stmt…” creates the hibernate query language string that is equivalent to SQL SELECT * FROM Customers
• “IQuery query = session.CreateQuery” creates a query variable that holds the results of the executed query
• “IList<Customers> customersList = query.List<Customers>();” creates the IList interface that you can use as the data source for the data grid

Fluent NHibernate tutorial CRUD C# example

The following source code creates, reads, updates and deletes data from the database. The source code is self explanatory but if you have questions feel free to ask using the comments section.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Globalization;
using NHibernate.Persister;
using CodeBlog.FluentNHibernate.Models;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHibernate.Cfg;

namespace CodeBlog.FluentNHibernate
{
    public partial class frmCustomers : Form
    {
        #region declarations
        ISessionFactory sessionFactory;
        #endregion

        #region methods
        private void load_records(string sFilter = "")
        {
            try
            {
                sessionFactory = CreateSessionFactory();

                using (var session = sessionFactory.OpenSession())
                {
                    string h_stmt = "FROM Customers";

                    if (sFilter != "")
                    {
                        h_stmt += " WHERE " + sFilter;
                    }
                    IQuery query = session.CreateQuery(h_stmt);

                    IList<Customers> customersList = query.List<Customers>();

                    dgvListCustomers.DataSource = customersList;

                    lblStatistics.Text = "Total records returned: " + customersList.Count;

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private static ISessionFactory CreateSessionFactory()
        {
            ISessionFactory isessionFactory = Fluently.Configure()
                .Database(MsSqlConfiguration.MsSql2005
                .ConnectionString("Server=.; Database=CustomerRecordsDB; Integrated Security=SSPI;"))
                .Mappings(m => m
                .FluentMappings.AddFromAssemblyOf<frmCustomers>())
                .BuildSessionFactory();

            return isessionFactory;
        }

        private void load_customer_details(int customer_id)
        {
            using (ISession session = sessionFactory.OpenSession())
            {
                using (ITransaction transaction = session.BeginTransaction())
                {
                    try
                    {
                        IQuery query = session.CreateQuery("FROM Customers WHERE customer_id = " + customer_id);

                        Customers customer = query.List<Customers>()[0];

                        txtCustomerId.Text = customer.customer_id.ToString();
                        txtName.Text = customer.name;
                        txtEmail.Text = customer.email;
                        txtContactPerson.Text = customer.contact_person;
                        txtContactNumber.Text = customer.contact_number;
                        txtPostalAddress.Text = customer.postal_address;
                        txtPhysicalAddress.Text = customer.physical_address;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, "Exception Msg");
                    }
                }
            }
        }

        #endregion

        public frmCustomers()
        {
            InitializeComponent();
        }

        private void frmCustomers_Load(object sender, EventArgs e)
        {
            load_records();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void btnFilter_Click(object sender, EventArgs e)
        {
            string sFilterValue = string.Empty;
            string sField = cboFilter.Text;
            string sCriteria = cboCriteria.Text;
            string sValue = txtValue.Text;

            switch (sCriteria)
            {
                case "Equals":
                    sFilterValue = sField + " = '" + sValue + "'";
                    break;

                case "Begins with":
                    sFilterValue = sField + " LIKE '" + sValue + "%'";
                    break;

                case "Contains":
                    sFilterValue = sField + " LIKE '%" + sValue + "%'";
                    break;

                case "Ends with":
                    sFilterValue = sField + " LIKE '%" + sValue + "'";
                    break;
            }

            //data.Add(sFilterValue, sValue);

            load_records(sFilterValue);
        }

        private void dgvListCustomers_Click(object sender, EventArgs e)
        {
            int customer_id = 0;

            customer_id = int.Parse(dgvListCustomers.CurrentRow.Cells[0].Value.ToString());

            load_customer_details(customer_id);
        }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            //data validation
            if (txtName.Text == "")
            {
                MessageBox.Show("The name field is required", "Null name", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                return;
            }

            if (txtEmail.Text == "")
            {
                MessageBox.Show("The email field is required", "Null email", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                return;
            }

            if (txtPhysicalAddress.Text == "")
            {
                MessageBox.Show("The physical address field is required", "Null physical address", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                return;
            }

            Customers customer = new Customers();

            customer.name = txtName.Text;
            customer.email = txtEmail.Text;
            customer.contact_person = txtContactPerson.Text;
            customer.contact_number = txtContactNumber.Text;
            customer.physical_address = txtPhysicalAddress.Text;
            customer.postal_address = txtPostalAddress.Text;

            using (var session = sessionFactory.OpenSession())
            {
                using (ITransaction transaction = session.BeginTransaction())
                {
                    try
                    {
                        session.Save(customer);

                        transaction.Commit();

                        load_records();
                    }

                    catch (Exception ex)
                    {
                        transaction.Rollback();

                        MessageBox.Show(ex.Message, "Exception Msg");
                    }
                }
            }
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            load_records();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            //data validation
            if (txtName.Text == "")
            {
                MessageBox.Show("The name field is required", "Null name", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                return;
            }

            if (txtEmail.Text == "")
            {
                MessageBox.Show("The email field is required", "Null email", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                return;
            }

            if (txtPhysicalAddress.Text == "")
            {
                MessageBox.Show("The physical address field is required", "Null physical address", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                return;
            }

            using (var session = sessionFactory.OpenSession())
            {
                using (ITransaction transaction = session.BeginTransaction())
                {
                    try
                    {
                        IQuery query = session.CreateQuery("FROM Customers WHERE customer_id = '" + txtCustomerId.Text + "'");

                        Customers customer = query.List<Customers>()[0];

                        customer.name = txtName.Text;
                        customer.email = txtEmail.Text;
                        customer.contact_person = txtContactPerson.Text;
                        customer.contact_number = txtContactNumber.Text;
                        customer.physical_address = txtPhysicalAddress.Text;
                        customer.postal_address = txtPostalAddress.Text;

                        session.Update(customer);

                        transaction.Commit();

                        load_records();
                    }

                    catch (Exception ex)
                    {
                        transaction.Rollback();

                        MessageBox.Show(ex.Message, "Exception Msg");
                    }
                }
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            using (ISession session = sessionFactory.OpenSession())
            {
                using (ITransaction transaction = session.BeginTransaction())
                {
                    try
                    {
                        IQuery query = session.CreateQuery("FROM Customers WHERE customer_id = '" + txtCustomerId.Text + "'");

                        Customers customer = query.List<Customers>()[0];

                        session.Delete(customer); //delete the record 

                        transaction.Commit(); //commit it 

                        btnRefresh_Click(sender, e);

                    }

                    catch (Exception ex)
                    {

                        transaction.Rollback();

                        MessageBox.Show(ex.Message, "Exception Msg");

                    }

                }

            }
        }
    }
}

Summary

• Fluent NHibernate uses strongly typed C# code for mapping entities as opposed to using HBM.XML
• NuGet Packet manager makes it easy to install, remove and update Fluent NHibernate
• The ISessionFactory is used to create and configure database sessions
• The IList interface can be used to display the query results in a data grid