How to use DataTable (or similar) with Oracle DB

Question

I've recently been dealing with some performance problems and am trying to figure out how to exponentially increase the performance of some calls to an Oracle DB.

Technologies:

• .NET Core 2
• Oracle DB
• Dapper (optional)
• Devart (optional)
• OracleManaged (optional, beta)

I am familiar with the use of DataTable and Table-Valued Parameters for Dapper and SQL Server and wish to replicate that with the technologies above. I have not yet been able to reproduce the solution below to work with Devart nor OracleManaged:

The code below is not what I'm running... It's a paraphrased example. I just need something that works with Oracle to pass a DataTable or array of objects to be used in a query/insert.

SQL Server:

CREATE TYPE MyCustomerInfo AS TABLE
(
    Id BIGINT NOT NULL,
    --Name NVARCHAR(32) NOT NULL,
    --...
);

C# for SQL Server:

const string getCustomersSql = @"
    SELECT
        c.Id,
        --c.Name
        --...
    FROM @myCustomers mc
    LEFT JOIN Customers c
        ON c.Id = mc.Id";

var myCustomers = new DataTable();
myCustomers.Columns.Add("Id", typeof(long));
//...

myCustomers.Rows.Add(1);
myCustomers.Rows.Add(2);

var customers = await sqlDbConnection.QueryAsync<Customer>(getCustomersSql, new { myCustomers = myCustomers.AsTableValuedParameter("MyCustomerInfo") });

Oracle DB (PL/SQL):

CREATE TYPE MY_CUSTOMER_INFO AS OBJECT
(
    ID BIGINT,
    --NAME VARCHAR2(32),
    --...
);

CREATE TYPE MY_CUSTOMER_INFO_ARRAY AS TABLE OF MY_CUSTOMER_INFO;

C# for Oracle DB:

I made a similar approach as with SQL Server but with both Devart and OracleManaged and neither worked. I also [very-unhappily] tried using OracleCommand and OracleParameter directly - again with both Devart and OracleManaged - to no avail.

My results with Devart seem to indicate that the functionality is intentionally prevented. My results with OracleManaged seem to indicate that it hasn't been implemented yet, which isn't surprising since it's beta (supposedly released Q3 this year).

My next approach may be to use associated arrays (in which I have very little experience or desire to learn) with Devart. At this point I'm just probing for something that will work with similar performance gains...

EDIT: Using an array-per-parameter approach is possible but is awfully inconvenient as large classes end up with 12+ arrays. I'd like an alternative to this approach.

Answer 1

@Kody this post is a little old, so this probably won't help you but for others this may help. Although I don't know of any way to use a datatable/UDT with the Managed Oracle Client, if you strictly just want to reduce the number of round trips to the DB and perform a bunch of inserts/deletes/updates with one call to the DB you could try this approach:

using (var dbConn = ManagedOracleHelper.GetConnection())
{
    dbConn.Open();
    var cmd = dbConn.CreateCommand();
    var udtList = GetUDTList(); // A dummy method to get a collection of Model 
                                // objects you want to use for the bulk operation.
                                // This could be a dataset too, you would just need
                                // to change the code within the for-loop to iterate
                                // over rows and access the columns by name.

    var firstNameArr = new string[udtList.Count];
    var lastNameArr  = new string[udtList.Count];
    var emailArr     = new string[udtList.Count];

    for (var i = 0; i < udtList.Count; i++)
    {
        firstNameArr[i] = udtList[i].FirstName;
        lastNameArr[i]  = udtList[i].LastName;
        emailArr[i]     = udtList[i].Email;
    }

    cmd.CommandText = @"INSERT INTO CUSTOMERS(FIRST_NAME, LAST_NAME, EMAIL)
                        VALUES(:FirstName, :LastName, :Email)";;
    cmd.BindByName  = true;

    cmd.Parameters.Add("FirstName", OracleDbType.Varchar2, ParameterDirection.Input);
    cmd.Parameters.Add("LastName", OracleDbType.Varchar2, ParameterDirection.Input);
    cmd.Parameters.Add("Email", OracleDbType.Varchar2, ParameterDirection.Input);

    cmd.ArrayBindCount = udtList.Count;

    cmd.Parameters["FirstName"].Value = firstNameArr;
    cmd.Parameters["LastName"].Value  = lastNameArr;
    cmd.Parameters["Email"].Value     = emailArr;

    cmd.ExecuteNonQuery();
}

Not sure how performant this would be if you were inserting/updating hundreds of millions of records, but I have tested this with inserting 80K records and my import feature went from taking multiple minutes (previously someone coded this as a simple loop calling an insert for each record) down to a few seconds. I don't have exact numbers because once the import time went down by almost 2 orders of magnitude, I was happy enough to move on with other things.