Connect to PostgreSQL With NHibernate Using C#

This tutorial explains how to use NHibernate for data operations in .NET applications. Using dotConnect for PostgreSQL, we will create NHibernate models, develop a console application, and implement CRUD operations with a reliable connection class for seamless database interaction.

Why dotConnect for PostgreSQL?

dotConnect for PostgreSQL is a powerful ADO.NET data provider designed to enable efficient interaction with PostgreSQL databases. It supports dynamic (on-the-fly) connector creation, flexible configuration options, and ensures smooth integration with Visual Studio.

Advanced ORM support, including compatibility with EF Core, Dapper, and NHibernate simplify all the development and data access routines in .NET applications.

Requirements

To follow this guide, make sure the following tools and components are available:

Visual Studio 2022. This guide uses Visual Studio 2022 as the integrated development environment (IDE). If it's not already installed, you can download the free Community Edition from the official website.
dotConnect for PostgreSQL. A high-performance data provider for PostgreSQL with enhanced ORM support and database connectivity features.
Entity Developer. A visual ORM designer for .NET ORM Frameworks with powerful code generation (more on this later).
DvdRental sample database. A sample PostgreSQL database available from the official PostgreSQL documentation, used here to demonstrate practical examples.

Download and activate dotConnect for PostgreSQL

30-day free trial version

You can start using dotConnect for PostgreSQL immediately with a 30-day free trial. Choose one of the following installation options:

Installer: Download and install dotConnect for PostgreSQL directly on your machine.
NuGet: Add the Devart.Data.PostgreSQL package to your project via NuGet.

No license key is required for the trial. Just install it and explore.

Full version

Once you've purchased the full version:

1. Go to the Products section of your Devart profile.

2. Locate your product and either click the product name or hover over it and click Details.

The License details page will open, where you'll find your Activation Key.

License details and the activation key

To activate dotConnect for PostgreSQL in your application, copy the Activation Key and include it in your connection string as the LicenseKey parameter.

Create a .NET project

First of all, we need a project to demonstrate the usage of NHibernate. Let us create a simple project.

Open Visual Studio, select Create a new project. Then give your project a name. We name our demo project PgNHibernate.

Check PostgreSQL database objects

In Server Explorer, right-click Data Connections and select Add Connection.

Add a new connection to the database

Then, choose the PostgreSQL data source, fill in the details of your PostgreSQL database connection, and click Next.

Test connection and connect to PostgreSQL

After that, select the tables and fields you want to work with and retrieve the data.

Retrieve data from the PostgreSQL database

Create an NHibernate model via Entity Developer

Right-click the PgNHibernate project in the Solution Explorer and select Add > New Item.

Add a new item

Go to Installed > C# items > Data, select Devart NHibernate Model, and click Add.

Start the configuration

In the Create Model Wizard, select Database First and click Next.

Choose the Database-First approach

Fill in the details of your PostgreSQL database connection and click Next.

Provide the connection details

Select Generate From Database and click Next.

Select the Generate from database option

Choose the database objects you want to scaffold.

Select the database objects

Define the naming convention for the property names in the database object and click Next. For this tutorial, we suggest keeping the default settings.

Specify the naming conventions

On the Model properties page, set your model settings and click Next.

Configure the model properties

Choose the model diagram contents. You can use all entities, split the entities by database, or do a custom selection. For this tutorial, select All Entities and click Next.

Set the model diagram contents

Choose the code generation templates for your objects. You can define different parameters you want the object to follow. We use the default settings for this tutorial. Click Next.

Select the code generation templates

Your model is ready now. Keep active checkboxes for NuGet packages installation and click Finish.

Finish the model creation

You can see the created model now.

View the created model

Given that you have the Film, FilmActor, and FilmCategory model classes, as well as the app.config files, already set up, let's integrate everything into a Console Application.

Configure NHibernate Session Factory

Create a new class NHibernateHelper.cs in your project:

using NHibernate;
using NHibernate.Cfg;

public static class NHibernateHelper
{
    private static ISessionFactory _sessionFactory;

    private static void InitializeSessionFactory()
    {
        var configuration = new Configuration();
        configuration.Configure();
        _sessionFactory = configuration.BuildSessionFactory();
    }

    public static ISession OpenSession()
    {
        if (_sessionFactory == null)
            InitializeSessionFactory();
        return _sessionFactory.OpenSession();
    }
}

Create a custom NHibernate driver

Create a new class DevartPgDriver.cs in your project:

using System.Data.Common;
using Devart.Data.PostgreSql;
using NHibernate.Driver;
using NHibernate.SqlTypes;

namespace PgNhibernate
{
    public class DevartPostgreSqlDriver : ReflectionBasedDriver
    {
        public DevartPostgreSqlDriver() : base(
            "Devart.Data.PostgreSql",
            "Devart.Data.PostgreSql.PgSqlConnection",
            "Devart.Data.PostgreSql.PgSqlCommand")
        {
        }

        public override bool UseNamedPrefixInParameter => true;
        public override bool UseNamedPrefixInSql => true;
        public override string NamedPrefix => ":";

        protected override void InitializeParameter(DbParameter dbParam, string name, SqlType sqlType)
        {
            base.InitializeParameter(dbParam, name, sqlType);
            var parameter = dbParam as PgSqlParameter;
            if (parameter != null)
            {
                // You can set any specific parameter properties here if needed
            }
        }
    }
}

Update app.config

Update your app.config file to use the custom DevartPgDriver:

 <?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="dialect">NHibernate.Dialect.PostgreSQLDialect</property>
             <property name="connection.driver_class">PgNhibernate.DevartPostgreSqlDriver, PgNhibernate</property>
       <property name="connection.connection_string">User Id=TestUser;Host=127.0.0.1;Database=dvdrental;Initial Schema=public;</property>
       <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
       <property name="show_sql">false</property>
       <property name="hbm2ddl.keywords">none</property>
       <mapping assembly="PgNhibernate" />
     </session-factory>
   </hibernate-configuration>
 </configuration>

Replace the placeholders in the connection string with your actual PostgreSQL database credentials.

Note

If you're using a purchased dotConnect for PostgreSQL license, include the license key in the connection strings.

Connect to PostgreSQL and retrieve data

The application will connect to the PostgreSQL database, execute the query, and display the data from the Film table in the console.

Add to your Program.cs file with the following code:

using NHibernate;

namespace PgNhibernate
{
 class Program
  {
   static void Main(string[] args)
     {
       try
        {
         using (ISession session = NHibernateHelper.OpenSession())
          {
            Console.WriteLine("Connection to the database successful!\n");

              // Retrieve only the Title and Release year of the first 10 films
               var films = session.Query()
                 .Select(film => new { film.Title, film.ReleaseYear })
                 .ToList();

                Console.WriteLine("First 10 Films:");
                foreach (var film in films.Take(10))
              {
            Console.WriteLine($"Title: {film.Title}. Year: {film.ReleaseYear}");
             }
           }
          }
        catch (Exception ex)
    {
    Console.WriteLine($"An error occurred: {ex.Message}");
  }
  }
 }
}

Build and run your application by pressing F5 or selecting "Start" from the menu.

Retrieve data into the console application

Insert new records using NHibernate

In this example, we insert a new test row into the Film table using NHibernate. Then we set the app to show all rows. Add the following code:

using NHibernate;

namespace PgNhibernate
{
  class Program
    {
     static void Main(string[] args)
      {
       try
        {
          using (ISession session = NHibernateHelper.OpenSession())
           {
            Console.WriteLine("Connection to the database successful!\n");

           // Insert a new film
            var newFilm = new Film
              {
                Title = "The Great Adventure",
                Description = "A thrilling journey through the unknown lands.",
                ReleaseYear = 2023,
                LanguageId = 1, // Assuming 1 is a valid language ID
                RentalDuration = 5,
                RentalRate = 5.99m,
                Length = 120,
                ReplacementCost = 19.99m,
                Rating = "PG",
                LastUpdate = DateTime.Now,
                Fulltext = "A thrilling journey through the unknown lands.",
                SpecialFeatures = "Trailers,Deleted Scenes"
              };

           using (ITransaction transaction = session.BeginTransaction())
            {
              session.Save(newFilm);
              transaction.Commit();
            }

            Console.WriteLine("New film inserted successfully!\n");

            // Retrieve only the Title and Description of the first 10 films
               var films = session.Query()
                .Select(film => new { film.Title, film.ReleaseYear })
                .ToList();

                Console.WriteLine("All Films:");
                foreach (var film in films)
                {
                Console.WriteLine($"Title: {film.Title}. Year: {film.ReleaseYear}");
                }


            }
         }
       catch (Exception ex)
      {
    Console.WriteLine($"An error occurred: {ex.Message}");
   }
  }
 }
}

See the results:

View the insert operation results

Update PostgreSQL data using NHibernate

We want to update the row in the Film table ("The Great Adventure") and change the film title to "The Epic Journey". The code is:

using NHibernate;

namespace PgNhibernate
{
  class Program
   {
    static void Main(string[] args)
      {
       try
        {
       	  using (ISession session = NHibernateHelper.OpenSession())
            {
              Console.WriteLine("Connection to the database successful!\n");

               using (ITransaction transaction = session.BeginTransaction())
                {
                  var films = session.Query().ToList();
                  var film = films.FirstOrDefault(f => f.Title == "The Great Adventure");

                  if (film != null)
                   {
                    film.Title = "The Epic Journey";
                    film.LastUpdate = DateTime.Now;
                            
                    session.Update(film);
                    transaction.Commit();
                            
                    Console.WriteLine($"Film updated successfully! New title: {film.Title}");
                   }
                else
              {
            Console.WriteLine("Film with title 'The Great Adventure' not found.");
           }
          }
         }
        }
      catch (Exception ex)
     {
    Console.WriteLine($"An error occurred: {ex.Message}");
     }
    }
  }
}

Now, let us test our application:

View the the results of the update operation

Delete PostgreSQL data using NHibernate

To delete the "The Epic Journey" row from the Film table, use the following code:

using NHibernate;

namespace PgNhibernate
{
  class Program
   {
    static void Main(string[] args)
     {
      try
       {
        using (ISession session = NHibernateHelper.OpenSession())
         {
          Console.WriteLine("Connection to the database successful!\n");

           using (ITransaction transaction = session.BeginTransaction())
            {
             var allFilms = session.Query().ToList();
             var filmToDelete = allFilms.FirstOrDefault(f => f.Title == "The Epic Journey");

             if (filmToDelete != null)
            {
               session.Delete(filmToDelete);
               transaction.Commit();
                            
               Console.WriteLine($"Film '{filmToDelete.Title}' deleted successfully!");
              }
               else
             {
            Console.WriteLine("Film with title 'The Epic Journey' not found.");
           }
          }
         }
        }
      catch (Exception ex)
      {
     Console.WriteLine($"An error occurred: {ex.Message}");
     }
    }
  }
}

Let us check the data in the application:

View the deletion operation results

Conclusion

This tutorial demonstrated how to integrate NHibernate with PostgreSQL using dotConnect for PostgreSQL. The robust ORM capabilities of NHibernate and the powerful connectivity features of dotConnect enable you to manage data access in .NET applications with the utmost flexibility and efficiency.

Build scalable, maintainable, and high-performance solutions relying on PostgreSQL databases and ensure smooth connection and excellent data operations with dotConnect for PostgreSQL!