2

In my application, I am using a Repository class that has a public Oracle.ManagedDataAccess.Client.OracleConnection DbConn property. In another class where I use this repository, there's a particular use case where I need to use OracleBulkCopy's in order to quickly import data from an Access file, which unfortunately still isn't supported by the Oracle Managed nuget. Therefore, I need to instantiate a Oracle.DataAccess.Client.OracleConnection and create the OracleBulkCopy's based on that connection. But I'd like to keep the whole process inside a transaction, both the changes made through the repository's connection and the 'non-Managed' OracleConnection. The only way I can see that being done, is by 'sharing' the underlying connection/session/transaction in the repository's Oracle.ManagedDataAccess.Client.OracleConnection to the Oracle.DataAccess.Client.OracleConnection. Is sucha a thing possible?

Thanks in advance.

Luis Costa
  • 330
  • 1
  • 14
  • What is the problem of only using `Oracle.DataAccess.Client`? – Wernfried Domscheit Apr 17 '20 at 15:29
  • This repository class is in a Common library project used by two different implementation projects, Server and Client. Of those two, only the Server actually performs the `OracleBulkCopy`'s. The Client project however is meant to be distributed across several clients' servers with, possibly, different versions of Oracle client installed. From what I've seen, using the Oracle Managed nuget would give me a wider range of compatibility instead of providing the Oracle.DataAccess.dll with my solution. – Luis Costa Apr 17 '20 at 15:42
  • If you start using `Oracle.DataAccess`, you will end-up installing Oracle Client again. Why would you need that? Why don't refactor code to do this bulk job using another method? build batches of `begin insert; insert; insert; ... end;` How many records do you have there? – T.S. Apr 17 '20 at 16:10
  • You can't mix 2 providers in same call. But yea, you can use both providers in same code. – T.S. Apr 17 '20 at 16:12
  • @T.S. The `OracleBulkCopy`'s from `Oracle.DataAccess` will only be used in the Server project where I don't mind having that dependency. Between 1 and 2 million records. – Luis Costa Apr 17 '20 at 18:45
  • Anonymous blocks parsed only the first time they are seen in the shared pool -- just like SQL, no different. So if you do 10K rows `begin insert into table values (:1, :2, :3);... end;`, you should be ok. Oracle says, *"The ODP.NET Bulk Copy feature uses a direct path load approach, which is similar to, but not the same as Oracle SQL*Loader. Using direct path load is faster than conventional loading (using conventional SQL INSERT statements)."* But, how much faster, especially if you send 10K at once? – T.S. Apr 17 '20 at 19:13
  • I would write this loop in 30 min and try 200K rows and see the timing – T.S. Apr 17 '20 at 19:15

1 Answers1

1

If you have a repository with a property

Oracle.ManagedDataAccess.Client.OracleConnection DbConn

You can abstract this by using the base class

System.Data.Common.DbConnection DbConn

Now, based on function you need, you can pass either Oracle.ManagedDataAccess.Client.OracleConnection or Oracle.DataAccess.Client.OracleConnection.

Most functions will work with both, versions but BulkCopy will work only with unmanaged. So in your code, just do 

if (DbConn is Oracle.DataAccess.Client.OracleConnection conn)
{
  //. . . . Do your bulk copy
} 
else
    throw new InvalidOperationException("Bulk copy supported only by un-managed ODP");
T.S.
  • 18,195
  • 11
  • 58
  • 78