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.
Fully supports EF Core, Dapper, NHibernate, LinqConnect, and other modern data access technologies for efficient and reliable data management.
Conforms to the latest ADO.NET standards and recent industry innovations for seamless and consistent integration with .NET applications.
Includes many PostgreSQL-specific features and fully supports all unique data types for accurate and complete data representation.
Provides robust security with support for SSL/SSH connections, connecting via proxy servers, embedded servers, and HTTP tunneling.
Features native integration with Visual Studio and complete design-time support for accelerated development.
Includes priority support, detailed documentation, and regular updates for continuous improvement.
You can start using dotConnect for PostgreSQL immediately with a 30-day free trial. Choose one of the following installation options:
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.
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.
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 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 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.
| Property | Description |
|---|---|
| Host or Server | Host name or IP address of the PostgreSQL server |
| User Id | User ID used to authenticate with PostgreSQL |
| Password | Password for the user ID |
| Port | Port on which the PostgreSQL server listens |
| Database | Default database to use after connecting |
| Schema | PostgreSQL schema to use |
| License key | Your license key (only required when you use .NET Standard-compatible assemblies) |
The application will connect to the PostgreSQL database, execute the query, and display the data from the Film table in the console.
Add the following code to your Program.cs file:
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<Film>()
.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.
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<Film>()
.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:
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<Film>().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:
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<Film>().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:
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!
I'm a technical content writer who loves turning complex topics—think SQL, connectors, and backend chaos—into content that actually makes sense (and maybe even makes you smile). I write for devs, data folks, and curious minds who want less fluff and more clarity. When I'm not wrangling words, you'll find me dancing salsa, or hopping between cities.
