Introduction
In Part 1, we implemented bulk CRUD operations using:
Global Temporary Tables (GTT)
Stored Procedures
Dapper-based bulk inserts
However, when dealing with 100K–1M+ records, even that approach can be further optimized.
In this article, we will explore:
Oracle Array Binding (ODP.NET)
Performance Benchmarking
Parallel Batch Processing
Memory & Transaction Optimization
Why Go Beyond Dapper?
While Dapper is fast, it still executes:
⚠️ Multiple parameterized executions internally
Problem:
Still incurs network round-trips
Not truly “bulk” at Oracle driver level
Solution: Oracle Array Binding (ODP.NET)
What is Array Binding?
Array Binding allows you to:
✔ Send entire arrays of data in one call
✔ Execute bulk insert/update in a single round-trip
✔ Achieve 10x–50x performance improvement
Bulk Insert Using Array Binding
Implementation
using Oracle.ManagedDataAccess.Client;
using System.Data;
public async Task BulkInsert_ArrayBinding(List<Employee> employees)
{
using var connection = new OracleConnection(_connectionString);
await connection.OpenAsync();
using var transaction = connection.BeginTransaction();
using var command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = @"
INSERT INTO EMPLOYEES (ID, NAME, EMAIL, SALARY)
VALUES (EMP_SEQ.NEXTVAL, :Name, :Email, :Salary)";
// Array Binding
command.ArrayBindCount = employees.Count;
command.Parameters.Add(":Name", OracleDbType.Varchar2,
employees.Select(e => e.Name).ToArray(), ParameterDirection.Input);
command.Parameters.Add(":Email", OracleDbType.Varchar2,
employees.Select(e => e.Email).ToArray(), ParameterDirection.Input);
command.Parameters.Add(":Salary", OracleDbType.Decimal,
employees.Select(e => e.Salary).ToArray(), ParameterDirection.Input);
await command.ExecuteNonQueryAsync();
transaction.Commit();
}
Bulk Update Using Array Binding
public async Task BulkUpdate_ArrayBinding(List<Employee> employees)
{
using var connection = new OracleConnection(_connectionString);
await connection.OpenAsync();
using var transaction = connection.BeginTransaction();
using var command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = @"
UPDATE EMPLOYEES
SET NAME = :Name, EMAIL = :Email, SALARY = :Salary
WHERE ID = :Id";
command.ArrayBindCount = employees.Count;
command.Parameters.Add(":Name", OracleDbType.Varchar2,
employees.Select(e => e.Name).ToArray(), ParameterDirection.Input);
command.Parameters.Add(":Email", OracleDbType.Varchar2,
employees.Select(e => e.Email).ToArray(), ParameterDirection.Input);
command.Parameters.Add(":Salary", OracleDbType.Decimal,
employees.Select(e => e.Salary).ToArray(), ParameterDirection.Input);
command.Parameters.Add(":Id", OracleDbType.Int32,
employees.Select(e => e.Id).ToArray(), ParameterDirection.Input);
await command.ExecuteNonQueryAsync();
transaction.Commit();
}
Bulk Delete Using Array Binding
public async Task BulkDelete_ArrayBinding(List<int> ids)
{
using var connection = new OracleConnection(_connectionString);
await connection.OpenAsync();
using var transaction = connection.BeginTransaction();
using var command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = @"DELETE FROM EMPLOYEES WHERE ID = :Id";
command.ArrayBindCount = ids.Count;
command.Parameters.Add(":Id", OracleDbType.Int32,
ids.ToArray(), ParameterDirection.Input);
await command.ExecuteNonQueryAsync();
transaction.Commit();
}
Join the conversation! Your thoughts help the community grow.