Connect to Oracle With NHibernate Using C#
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.
Why dotConnect for Oracle?
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 activate dotConnect for Oracle
Download and install dotConnect for Oracle directly on your machine, or install the Devart.Data.Oracle NuGet package.
To activate a 30-day trial, use the license key assigned to you after completing the registration on the Devart website.
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.
Check Oracle database objects
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.
Create an NHibernate model via Entity Developer
Entity Developer allows you to visually design and generate EF Core models, making database application development faster, easier, and more efficient. If you don't have it already installed, close your Visual Studio instance and download Entity Developer. 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, so you can work with it.
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.
Configure NHibernate SessionFactory
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 custom NHibernate driver
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 app.config
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.
Connect to Oracle and retrieve data
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.
Insert new records using NHibernate
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:
Update Oracle data using NHibernate
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:
Delete Oracle data using NHibernate
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.
Conclusion
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.