Bulk insert into an Oracle database through .Net C#

Bulk insert into an Oracle database through .Net C#

Below is the code to import thousands of records into an Oracle database in a .NET component.

Both allow you to do bulk inserts using something called array binding. This basically means that you can insert multiple records at once in one database call, thereby avoiding unnecessary round trips.

The table that I had to fill was called IMPORTJOBS:

CREATE TABLE IMPORTJOBS
(
  ID             RAW(16)                        NOT NULL,
  IMPORTBATCHID  RAW(16)                        NOT NULL,
  DATA           VARCHAR2(4000 BYTE)            NOT NULL,
  STATUSCODE     VARCHAR2(20 BYTE)              NOT NULL,
  RESULTID       RAW(16)
)

public interface IOracleBulkInsertProvider
{   
    void BulkInsertImportBatches(string connectionString,              
          List<ImportJob> importJobs);
}

public class OracleBulkInsertProvider : IOracleBulkInsertProvider
{
    public void BulkInsertImportBatches(string connectionString,
                                            List<ImportJob> importJobs)
    {                                        
        var ids = importJobs.Select(ib => ib.Id).ToList();
        var importBatchIds =
                  importJobs.Select(ib => ib.ImportBatchId).ToList();
        var datas = importJobs.Select(ib => ib.Data).ToList();
        var statusCodes =
                  importJobs.Select(ib => ib.ImportJobStatus.Code).ToList();               
                               
        const string sql = "insert into IMPORTJOBS (ID, IMPORTBATCHID, DATA,
            STATUSCODE) values (:id, :importBatchId, :data, :statusCode)";           

        using (var oracleConnection =
                               new OracleConnection(connectionString))
        {
            oracleConnection.Open();
            using (var command = oracleConnection.CreateCommand())
            {
                command.CommandText = sql;
                command.CommandType = CommandType.Text;
                command.Parameters.Add(":id", OracleDbType.Raw,
                  ids.ToArray(), ParameterDirection.Input);
                command.Parameters.Add(":importBatchId", OracleDbType.Raw,
                  importBatchIds.ToArray(), ParameterDirection.Input);
                command.Parameters.Add(":data", OracleDbType.VarChar,
                  datas.ToArray(), ParameterDirection.Input);
                command.Parameters.Add(":statusCode", OracleDbType.VarChar,
                  statusCodes.ToArray(), ParameterDirection.Input);
                command.ExecuteArray(importJobs.Count);                                   
            }                               
        }
    }
}

I think the code is straightforward. The goal is to create a command, add input parameters and at the same time provide a list of actual values to insert for that parameter. At the end we simply call the ExecuteArray operation.

This way, the time needed to insert 25.000 records takes about 2 seconds.