This tutorial explains how to use NHibernate for data operations in .NET applications. Using dotConnect for Oracle, we will create NHibernate models, develop a console application, and implement CRUD operations with a reliable connection class for seamless database interaction.
dotConnect for Oracle is the ideal data provider for all Oracle-related operations. It offers on-the-fly connector creation and flexible configuration, seamless integration into Visual Studio, and enhanced ORM support.
Download and install dotConnect for Oracle directly on your machine, or install the Devart.Data.Oracle NuGet package.
No license key is required, and you can start exploring the product immediately.
After purchasing the full version, go to your profile's Licenses page. Choose your product and click Details. Here, you'll find the license details and the Activation Key.
To activate a connection in your application, add the License Key to your connection strings.
Connect to Oracle using the built-in Data Explorer, click Tools and select Connect to Database.
Then, choose the Oracle data source, fill in the details of your Oracle database connection, and click OK.
After establishing the connection, select the tables and fields you want to work with and retrieve the data.
1. Right-click the OracleNHibernate project in the Solution Explorer and select Add > New Item.
2. Go to Installed > C# items > Data, select Devart NHibernate Model, and click Add.
3. In the Create Model Wizard, select Database First to enable model creation from a pre-existing database and click Next.
4. Fill in the details of your Oracle database connection and click Next.
5. Select Generate From Database to fill the model with the database contents and click Next.
6. Choose the database objects you want to scaffold and click Next.
7. Define the naming convention for the property names in the database object and click Next. We suggest keeping the default settings for this tutorial.
8. On the Model properties page, define your model settings and click Next.
9. Choose the model diagram contents. You can use all entities, split the entities by users, or do a custom selection. For this tutorial, select All Entities and click Next.
10. Choose code generation templates for your objects. You can define different parameters you want the object to follow. Let's use the default settings for this tutorial. Click Next.
Your model is ready now.
11. Keep active checkboxes for NuGet packages installation and click Finish.
The model you created will open.
Given that you have the DataModel1.EMPLOYEE.cs, DataModel1.EMPLOYEE.hbm.xml, and 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;
namespace OracleNHibernate {
public class NHibernateHelper {
private static ISessionFactory _sessionFactory;
private static ISessionFactory SessionFactory {
get {
if (_sessionFactory == null) {
var configuration = new Configuration();
configuration.Configure(); // This will read the configuration from app.config
_sessionFactory = configuration.BuildSessionFactory();
}
return _sessionFactory;
}
}
public static ISession OpenSession() {
return SessionFactory.OpenSession();
}
}
}
Create a new class DevartOracleDriver.cs in your project:
using System.Data;
using System.Data.Common;
using Devart.Data.Oracle;
using NHibernate.AdoNet;
using NHibernate.Driver;
using NHibernate.SqlTypes;
namespace OracleNHibernate
{
public class DevartOracleDriver : ReflectionBasedDriver, IEmbeddedBatcherFactoryProvider
{
public DevartOracleDriver() : base(
"Devart.Data.Oracle",
"Devart.Data.Oracle.NHibernate.NHibernateOracleConnection",
"Devart.Data.Oracle.NHibernate.NHibernateOracleCommand"
)
{
}
public override bool UseNamedPrefixInParameter { get; } = true;
public override bool UseNamedPrefixInSql { get; } = true;
public override string NamedPrefix { get; } = ":";
private static readonly SqlType GuidSqlType = new SqlType(DbType.Binary, 16);
protected override void InitializeParameter(DbParameter dbParam, string name, SqlType sqlType)
{
if (sqlType.DbType == DbType.Guid)
{
base.InitializeParameter(dbParam, name, GuidSqlType);
OracleParameter oraParam = (OracleParameter)dbParam;
oraParam.OracleDbType = OracleDbType.Raw;
}
else
{
base.InitializeParameter(dbParam, name, sqlType);
}
}
System.Type IEmbeddedBatcherFactoryProvider.BatcherFactoryClass
{
get { return typeof(OracleDataClientBatchingBatcherFactory); }
}
}
}
Update your app.config file to use the custom DevartOracleDriver:
<?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.Oracle10gDialect</property>
<property name="connection.driver_class">OracleNHibernate.DevartOracleDriver, OracleNHibernate</property>
<property name="hbm2ddl.keywords">none</property>
<property name="connection.connection_string">Direct=True;Server=127.0.0.1;UserId=TestUser;Password=TestPassword;ServiceName=TestDb;LicenseKey=**********</property>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<mapping assembly="OracleNHibernate" />
</session-factory>
</hibernate-configuration>
</configuration>
Replace the placeholders in the connection string with your actual Oracle database credentials.
In this example, we connect a C# console application to an Oracle database using NHibernate and retrieve records from the EMPLOYEE table. The application opens a session through the NHibernateHelper, performs a LINQ query to fetch all employees, and prints each employee’s first name, last name, and email to the console. If the connection or query fails, the exception is caught and an error message is displayed. For successful execution, ensure that the EMPLOYEE class is properly mapped to the Oracle table and that the NHibernateHelper is correctly configured with your database settings.
Add the following code to your Program.cs file:
using NHibernate;
namespace OracleNHibernate
{
class Program
{
static void Main(string[] args)
{
try
{
using (ISession session = NHibernateHelper.OpenSession())
{
Console.WriteLine("Connection to Oracle successful.");
// Use the correct entity class and property names
var employees = session.Query<EMPLOYEE>().ToList();
Console.WriteLine("Rows from EMPLOYEE table:");
foreach (var employee in employees)
{
Console.WriteLine($"Employee: {employee.FIRSTNAME} {employee.LASTNAME}, E-mail: {employee.EMAIL}");
}
}
}
catch (Exception ex)
{
Console.WriteLine("Connection to Oracle failed.");
Console.WriteLine(ex.Message);
}
}
}
}
Build and run your application by pressing F5 or selecting Start from the menu.
In this example, we will insert a new test row into the EMPLOYEE table using NHibernate. First, the application opens a session with the Oracle database using a helper class. It then creates a new EMPLOYEE object with sample data, including an ID, name, email, hire date, and salary. The new employee is saved to the database within a transaction to ensure data integrity.
After the insertion, the application retrieves all rows from the EMPLOYEE table using a LINQ query and displays each employee's ID, name, email, hire date, and salary in the console. This allows us to confirm that the new record has been successfully added. If there is an issue at any point - such as a connection failure or mapping error - an exception is caught, and an error message is displayed. This example demonstrates a complete cycle of inserting and reading data with NHibernate in a simple, console-based application.
Run the following code:
using NHibernate;
using System;
using System.Linq;
namespace OracleNHibernate
{
class Program
{
static void Main(string[] args)
{
try
{
using (ISession session = NHibernateHelper.OpenSession())
{
Console.WriteLine("Connection to Oracle successful.");
// Create a new employee
var newEmployee = new EMPLOYEE
{
EMPLOYEEID = 1003,
FIRSTNAME = "TestUsername",
LASTNAME = "TestLastname",
EMAIL = "[email protected]",
HIREDATE = DateTime.Parse("2025-07-16"),
SALARY = (double?)55000.00m
};
// Save the new employee
using (ITransaction transaction = session.BeginTransaction())
{
session.Save(newEmployee);
transaction.Commit();
}
Console.WriteLine("New employee inserted successfully.\n");
// Retrieve and display all employees
var allEmployees = session.Query<EMPLOYEE>().ToList();
Console.WriteLine("All employees:");
foreach (var employee in allEmployees)
{
Console.WriteLine($"ID: {employee.EMPLOYEEID}, Name: {employee.FIRSTNAME} {employee.LASTNAME}, Email: {employee.EMAIL}, " +
$"Hire Date: {employee.HIREDATE}, Salary: {employee.SALARY}");
}
}
}
catch (Exception ex)
{
Console.WriteLine("Connection to Oracle failed.");
Console.WriteLine(ex.Message);
}
}
}
}
You will get the following result:
In this example, we update an existing employee’s first and last name in the Oracle database using NHibernate. The UpdateEmployeeName method opens a session, retrieves all employees, and searches for the one with the specified EMPLOYEEID. If found, it begins a transaction, updates the FIRSTNAME and LASTNAME fields, and commits the changes. After the update, it prints all the employee’s details to the console. If the employee isn’t found or an error occurs, appropriate messages are displayed. This demonstrates a simple NHibernate update operation with basic error handling.
You can use the following example with NHibernate:
using NHibernate;
namespace OracleNHibernate
{
class Program
{
static void Main(string[] args)
{
// Example usage: update employee 1003's name
UpdateEmployeeName(1003, "NewUsername", "NewLastname");
}
public static void UpdateEmployeeName(long employeeId, string newFirstName, string newLastName)
{
try
{
using (ISession session = NHibernateHelper.OpenSession())
{
// Retrieve all employees first (workaround for query issue)
var allEmployees = session.Query<EMPLOYEE>().ToList();
var employee = allEmployees.FirstOrDefault(e => e.EMPLOYEEID == employeeId);
if (employee != null)
{
using (ITransaction transaction = session.BeginTransaction())
{
try
{
employee.FIRSTNAME = newFirstName;
employee.LASTNAME = newLastName;
session.Update(employee);
transaction.Commit();
Console.WriteLine($"Employee with ID {employeeId} updated successfully.\n");
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine($"Error updating employee: {ex.Message}");
return;
}
}
// Show all data columns of employee with ID 1003
Console.WriteLine("Employee data after update:");
Console.WriteLine($"ID: {employee.EMPLOYEEID}");
Console.WriteLine($"First Name: {employee.FIRSTNAME}");
Console.WriteLine($"Last Name: {employee.LASTNAME}");
Console.WriteLine($"Email: {employee.EMAIL}");
Console.WriteLine($"Hire Date: {employee.HIREDATE}");
Console.WriteLine($"Salary: {employee.SALARY}");
}
else
{
Console.WriteLine($"Employee with ID {employeeId} not found in the database.");
}
}
}
catch (Exception ex)
{
Console.WriteLine("Connection to Oracle failed.");
Console.WriteLine(ex.Message);
}
}
}
}
Let’s test our application:
In this example, we delete a record from the EMPLOYEE table using NHibernate. The application begins by opening a session with the Oracle database and retrieving all employees. It first displays all existing records in the console for reference. Then it attempts to find and delete the employee with EMPLOYEEID 1003. If this employee exists, the record is deleted within a transaction, and the change is committed.
If the employee with ID 1003 is not found, the program deletes the first available employee as a fallback. In both cases, error handling is included to catch and roll back the transaction if needed. Finally, the application retrieves and displays the remaining employees in the database, or a message if no records are left.
using NHibernate;
namespace OracleNHibernate
{
class Program
{
static void Main(string[] args)
{
try
{
using (ISession session = NHibernateHelper.OpenSession())
{
Console.WriteLine("Connection to Oracle successful.");
// First, retrieve and display all employees
var allEmployees = session.Query<EMPLOYEE>().ToList();
Console.WriteLine("All employees before deletion:");
foreach (var employee in allEmployees)
{
Console.WriteLine($"ID: {employee.EMPLOYEEID}, Name: {employee.FIRSTNAME} {employee.LASTNAME}, Email: {employee.EMAIL}, " +
$"Hire Date: {employee.HIREDATE}, Salary: {employee.SALARY}");
}
// Try to delete employee with ID 1003 if it exists
var employeeToDelete = allEmployees.FirstOrDefault(e => e.EMPLOYEEID == 1003);
if (employeeToDelete != null)
{
using (ITransaction transaction = session.BeginTransaction())
{
try
{
session.Delete(employeeToDelete);
transaction.Commit();
Console.WriteLine($"\nEmployee with ID {employeeToDelete.EMPLOYEEID} deleted successfully.\n");
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine($"Error deleting employee: {ex.Message}");
}
}
}
else
{
Console.WriteLine("\nEmployee with ID 1003 not found in the database.");
// Delete the first employee if any exist (as fallback)
if (allEmployees.Count > 0)
{
var firstEmployee = allEmployees.First();
using (ITransaction transaction = session.BeginTransaction())
{
try
{
session.Delete(firstEmployee);
transaction.Commit();
Console.WriteLine($"\nEmployee with ID {firstEmployee.EMPLOYEEID} deleted successfully instead.\n");
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine($"Error deleting employee: {ex.Message}");
}
}
}
}
// Display remaining employees after deletion
var remainingEmployees = session.Query<EMPLOYEE>().ToList();
Console.WriteLine("Remaining employees after deletion:");
if (remainingEmployees.Count > 0)
{
foreach (var employee in remainingEmployees)
{
Console.WriteLine($"ID: {employee.EMPLOYEEID}, Name: {employee.FIRSTNAME} {employee.LASTNAME}, Email: {employee.EMAIL}, " +
$"Hire Date: {employee.HIREDATE}, Salary: {employee.SALARY}");
}
}
else
{
Console.WriteLine("No employees remaining in the database.");
}
}
}
catch (Exception ex)
{
Console.WriteLine("Connection to Oracle failed.");
Console.WriteLine(ex.Message);
}
}
}
}
This example demonstrates how to safely perform delete operations with fallback logic and clear output using NHibernate.
In this tutorial, we explored how integrating NHibernate with Oracle with the help of dotConnect for Oracle simplifies data operations in .NET applications. The advanced capabilities of dotConnect for Oracle, combined with the lightweight efficiency of NHibernate, offer a fast and flexible way to manage database interactions in Oracle-based applications. With these tools, you can confidently develop scalable, maintainable, and high-performance .NET solutions that seamlessly connect to Oracle databases.