High-Performance Data Access The Definitive Guide to Dapper with Oracle Database

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

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

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.