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.
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.
To activate a 30-day trial, use the license key assigned to you after completing the registration on the Devart website.
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.
To activate dotConnect for PostgreSQL in your application, copy the Activation
Key and include it in your connection string as the LicenseKey
parameter.
Check PostgreSQL database objects
In Server Explorer, right-click Data Connections and select Add Connection.
Then, choose the PostgreSQL data source, fill in the details of your PostgreSQL database connection, and click Next.
After that, select the tables and fields you want to work with and retrieve the data.
Create an NHibernate model via Entity Developer
Entity Developer allows you to visually design and generate NHibernate models, making database application development faster, easier, and more efficient. If you don't have it already installed, close your Visual Studio instance, download Entity Developer, and install it following the on-screen instructions.
Follow the detailed illustrated guide to create your database model using Entity Developer. When this process is complete, the model you created opens.
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.
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.
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:
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:
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:
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!