In the modern enterprise ecosystem, the ability to interact with an Oracle Database efficiently is a critical skill for .NET developers. While Entity Framework (EF) Core is a powerful tool, it often introduces overhead that can become a bottleneck in high-throughput applications. Dapper, known as the "Micro-ORM," offers a lean, high-performance alternative by extending the IDbConnection interface with intuitive mapping methods.
However, Oracle is not SQL Server. It has unique architectural requirements—such as its specific parameter prefixing (:), case-sensitivity rules, and the lack of a native Boolean type. This article provides a comprehensive, end-to-end blueprint for building a production-ready CRUD (Create, Read, Update, Delete) layer using Dapper and Oracle, complete with transactional integrity.
1. Architectural Overview and Setup
To build a robust integration, we rely on the official Oracle managed driver. This driver is cross-platform and does not require a heavy Oracle Client installation on your web or application servers.
Required NuGet Packages
Dapper: The core library for object mapping.
Oracle.ManagedDataAccess. Core: The official Oracle driver for .NET 6/7/8+.
The Database Schema
We will use a Contacts table. Since Oracle 12c, the IDENTITY column is the preferred way to handle auto-incrementing primary keys.
SQL
CREATE TABLE Contacts (
ContactId NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
FullName VARCHAR2(100) NOT NULL,
Email VARCHAR2(100) NOT NULL,
IsActive NUMBER(1) CHECK (IsActive IN (0,1)), -- Oracle Boolean workaround
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE AuditLogs (
LogId NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
LogMessage VARCHAR2(500),
LogDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
2. Bridging the Gap: Type Handlers and Models
Oracle uses NUMBER(1) to represent truth values (1 for true, 0 for false). In C#, we want to use bool. We use a Dapper TypeHandler to automate this translation, ensuring our business logic remains clean.
The Model and Handler
C#
using Dapper;
using System.Data;
public class Contact
{
public int ContactId { get; set; }
public string FullName { get; set; }
public string Email { get; set; }
public bool IsActive { get; set; } // Maps to NUMBER(1)
public DateTime CreatedAt { get; set; }
}
public class OracleBooleanHandler : SqlMapper.TypeHandler<bool>
{
public override void SetValue(IDbDataParameter parameter, bool value)
=> parameter.Value = value ? 1 : 0;
public override bool Parse(object value)
=> Convert.ToInt32(value) == 1;
}
3. The Repository and Transactional Unit of Work
The key to Oracle success is BindByName = true. By default, Oracle binds parameters by position. If your C# object properties are ordered differently than your SQL parameters, Oracle will insert the wrong data into the wrong columns. BindByName fixes this.
The Implementation
C#
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Oracle.ManagedDataAccess.Client;
using Dapper;
public class ContactRepository
{
private readonly string _connectionString;
public ContactRepository(string connString)
{
_connectionString = connString;
// Register the Boolean handler once
SqlMapper.AddTypeHandler(new OracleBooleanHandler());
}
private OracleConnection GetConnection()
{
var conn = new OracleConnection(_connectionString);
conn.Open();
conn.BindByName = true; // CRITICAL: Maps :Name to Property 'Name'
return conn;
}
// CREATE with Transactional Integrity
public void CreateContactWithLog(Contact contact)
{
using var connection = GetConnection();
using var transaction = connection.BeginTransaction();
try
{
// 1. Insert Contact
string sqlContact = @"INSERT INTO Contacts (FullName, Email, IsActive)
VALUES (:FullName, :Email, :IsActive)
RETURNING ContactId INTO :NewId";
var contactParams = new DynamicParameters(contact);
contactParams.Add("NewId", dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute(sqlContact, contactParams, transaction);
int newId = contactParams.Get<int>("NewId");
// 2. Insert Audit Log
string sqlLog = "INSERT INTO AuditLogs (LogMessage) VALUES (:Msg)";
connection.Execute(sqlLog, new { Msg = $"Created ID {newId}" }, transaction);
// 3. Commit
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
// READ
public List<Contact> GetAll()
{
using var db = GetConnection();
return db.Query<Contact>("SELECT * FROM Contacts ORDER BY ContactId DESC").ToList();
}
// UPDATE
public void Update(Contact contact)
{
using var db = GetConnection();
string sql = "UPDATE Contacts SET FullName = :FullName, Email = :Email WHERE ContactId = :ContactId";
db.Execute(sql, contact);
}
// DELETE
public void Delete(int id)
{
using var db = GetConnection();
db.Execute("DELETE FROM Contacts WHERE ContactId = :Id", new { Id = id });
}
}
4. Key Performance Considerations
When working with Dapper and Oracle, performance is usually the primary goal. Keep these three "Pro-Tips" in mind:
I. Bulk Inserts
If you need to insert 1,000+ records, don't use a loop. Dapper's Execute method accepts an IEnumerable. When you pass a list, Dapper automatically uses Oracle's internal array binding, which is significantly faster than individual calls.
II. RETURNING INTO Clause
In SQL Server, you might use SELECT SCOPE_IDENTITY(). In Oracle, the RETURNING [Column] INTO :Variable clause is much more efficient as it retrieves the generated ID in a single round-trip without a secondary select.
III. Disposing Connections
Oracle is sensitive to "Connection Leaks." Always wrap your OracleConnection in a using block. This ensures that the connection is returned to the connection pool immediately after the work is finished.
5. Conclusion
Dapper provides the ultimate balance for Oracle development: the raw speed of SQL with the developer productivity of an ORM. By configuring BindByName, implementing TypeHandlers for booleans, and managing transactions explicitly, you create a data access layer that is both maintainable and high-performing.
Whether you are building a microservice or a legacy system migration, the Dapper + Oracle stack ensures your application remains responsive under heavy load while keeping your code clean and readable.