Fast Inserts to Oracle with C#

Recently I found myself having to do bulk record inserts to an Oracle table on an API I created with .Net 6. I was using EntityFrameworkCore for this and read that even though I was using AddRange, the inserts actually happen one record at a time and would end up taking a very long time to complete.

After some research and experimentation, I found that OracleBulkCopy was able to do the job for me. Actually, there was a the Z.EntityFramework.Extensions.EFCore NuGet package that would have done the work for me very, very easily, but the license price was a bit too steep for me at the moment. For a large company with the budget, though, I would recommend it especially to keep your code simpler.

For my solution, you need to first make sure you have Oracle.ManagedDataAccess installed from NuGet. You can use the Core version instead, if you are using a .Net core project.

Make sure to include the using statement:

using Oracle.ManagedDataAccess.Client;

This method does require your records to be in a DataTable, though, but otherwise here's some example code on how to use OracleBulkCopy:

// Your records need to be in a DataTable.
var records = <your_records>;

using OracleConnection connection = new (<your_connection_string>);
connection.Open();

using OracleBulkCopy bulk = new(connection);
bulk.DestinationTableName = "<destination_table>";
bulk.ColumnMappings.Add(new OracleBulkCopyColumnMapping { DestinationColumn = "<destination_column>", SourceColumn = "<source_column>" }; // You can add more columns as needed
bulk.WriteToServer(records);

You'll probably also want to wrap the code from the connection to the WriteToServer in a try/catch block.

• Previous: Mixing Generics in C#