-
.NET CLI
-
Package Manager Console
-
Windows installer
You can install the driver by using the Windows installer.
After you receive the license key, add it to your connection strings to connect to the data source.
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?
Advanced ORM support
Fully supports EF Core, Dapper, NHibernate, LinqConnect, and other modern data access technologies for efficient and reliable data management.
Full ADO.NET compliance
Conforms to the latest ADO.NET standards and recent industry innovations for seamless and consistent integration with .NET applications.
PostgreSQL-specific data types
Includes many PostgreSQL-specific features and fully supports all unique data types for accurate and complete data representation.
Secure connection options
Provides robust security with support for SSL/SSH connections, connecting via proxy servers, embedded servers, and HTTP tunneling.
IDE integration
Features native integration with Visual Studio and complete design-time support for accelerated development.
Priority support & frequent updates
Includes priority support, detailed documentation, and regular updates for continuous improvement.
Download and activate dotConnect for PostgreSQL
You can start using dotConnect for PostgreSQL immediately with a 30-day free trial. Choose one of the following installation options:
30-day free trial version
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.
Connection strings
| 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) |
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 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.
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<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:
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<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:
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<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:
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!
FAQ
How do you install and activate dotConnect for PostgreSQL in a .NET project?
Install dotConnect for PostgreSQL either by running the Windows installer (EXE) or by adding the Devart.Data.PostgreSql NuGet package to your .NET project. Then, retrieve your activation key from the Devart Customer Portal and specify it in your connection string by using the License Key parameter to activate the provider and connect successfully.
How do you create a connection to PostgreSQL using dotConnect in C#?
Define a connection string that includes Host, User Id, Password, Database, and (if required) License Key. Then create a PgSqlConnection with this string and call Open() inside a try/catch block to verify the connection and handle any errors.
How do you enable SSL/TLS for secure PostgreSQL connections with dotConnect?
Add SslMode=Require and provide the certificate file paths (for example, CACert, Cert, and Key) in the connection string. Then create a PgSqlConnection with this string and call Open() to establish an encrypted SSL/TLS session.
Can you connect to PostgreSQL using Entity Framework Core and dotConnect?
Yes, you can connect to PostgreSQL using Entity Framework Core and dotConnect. You can either use Entity Developer to visually create an EF Core model from the database and generate the DbContext and entity classes, or run Scaffold-DbContext with a dotConnect connection string (including the License Key) to scaffold the DbContext and entities from an existing database.
Is it possible to connect to PostgreSQL using Visual Studio Server Explorer with dotConnect?
Yes. In Visual Studio Server Explorer, you can add a new Data Connection, select dotConnect for PostgreSQL as the data provider, enter your PostgreSQL connection details, test the connection, and then browse and work with database objects directly in the IDE.
Dereck Mushingairi
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.
Try the 30-day trial of the full product. No limits. No card required.
Start free trial