What is the fastest way to load data into an ORACLE database using .NET?

Question

I currently have a daily process that loads a large amount of data from a TXT file into an ORACLE database using a shell script that calls sql_loader. I want to port this to a .NET service, but I don't want to rely on sql_loader to execute from my service.

What is the best (and fastest) way to accomplish this?

Answer 1

I assume you don't like SQLLoader because of the command line interface and (a few) awkward control files. But this is not the only way to use "SQLLoader"

Oracle now has something called External Tables. You can see an example here .

It’s just that you put the file in a directory (the database object that defines the file system directory), you define the table parameters, etc .... just look at the example. Now all of a sudden, your flatfile looks like a table for Oracle.

You execute "INSERT INTO perm_Table SELECT * FROM external_table".

So, now your .net application simply renames the files according to the external table, then executes an INSERT and then renames the file.

Voila.

You have uploaded your data. All this is done with SQL, much faster than ADO or any other library that you can get to. There is no more awkward command line interface.

Answer 2

Load the data into a DataTable and use the OracleBulkCopy class (from Oracle Data Provider for.NET ) to load it into the database all at once. This will only work if you only insert data into the database, you cannot make updates using OracleBulkCopy.

Answer 3

The ADO.Net provider for SQL Server supports the SqlBulkCopy function, which mimics BCP SQL Server.

I do not know anything about the Oracle provider, but I would start looking for whether this provider supports a similar function for sql_loader.

Answer 4

I would look at third-party dotConnect libraries from DevArt (formerly CoreLab). Although I did not use their OracleLoader , I use their connection, command, datareader and dataadapter, and find that they are really very fast.

Hope this helps: o)

Answer 5

If you have SSIS, this will be a good tool. On the .NET side, I would recommend using the Oracle class mentioned above, but it is better to use SSIS or any ETL tool.

Answer 6

I really solved my own problem using an alternative method: I wrote a stored procedure to load data and used the UTL_FILE package, I don’t know if this is the fastest method, but it is quite fast and very flexible (I can manipulate text data like I I want, at boot).

Thanks for all the answers, I just posted this to show another alternative for people having the same problem as me.