Using OracleBulkCopy the right way

Why waiting 15 minutes when you can wait 5 seconds for the exact same query in Oracle? Or asked more cleverly: why an oracle C# data access provider would ask you this stupid question?

I recently wrote a simple “InsertMany<T>(IList<T> list)” function to “Bulk insert” inside an Oracle table. I really don’t know why I would insert any other way since my benchmarks show always a time improvement ranging between 5 and 150 times depending if I use plain ADO.NET or if I glue Entity Framework around my query.

It seems you need to pay to get this performance without sweating too much with your database provider default interface. Such a shame.

Well, I will show you how I found my way to use the default OracleBulkCopy interface straight from ADO.NET with a simple table having primary key constraint and a sequence attached to it in Oracle 11g (yep, sequences doesn’t exist in SQL Server and that’s a good thing: separating things that goes along is bad design).

Here is my algorithm:

1. Get the table/collection name from the class name or the one specified explicitly;
2. Initiate a connection+transaction in Oracle;
3. If the class representing objects in your list to insert contains a primary key, I assume you have set up a sequence in Oracle accordingly and that you follow some convention, mine is “SEQ_{TableName}”. So I update the sequence to avoid putting your sequence in a “UNUSABLE State” . I assume also you have set up a global temporary table with DELETE ON COMMIT to hold your primary sequence key values, mine is named “TEMP_NUMBER”.
4. Execute the bulk insert. Observe that you can’t put the bulk operation inside another transaction, it needs to have its own. Note also that you can’t have triggers on your table. If you have, you need to bulk in a temporary table, then insert normally into your target table and clean up the temporary data yourself (because the data needs to stay there inside two transactions)…

So, here is my code. OracleFields and BulkCopyHelper are just classes to perform some mapping by reflection between the input list and the datatable/datarow/sqlparameters used in the oracle query:

I hope it could help someone.

Cheers!