How to use DataTable (or similar) with Oracle DB

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.

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(); }

Recommended topics

Hot tags