How to make a Bulk Insert using Oracle Managed Data Acess c#

Question

I have a dynamic list object that i want to BulkInsert into my DB, i was using the old OracleBulkCopy method from another lib, but i can't use that lib anymore and on the new lib i don't have this method.

New lib : using Oracle.ManagedDataAccess.Client;

Old lib : Oracle.DataAccess.Client

Does anyone know a easy way to do the Bulk without creating lists or arrays to do it?

What is the new lib? – Mateus Schneiders Dec 22 '17 at 15:24 — Mateus Schneiders, Dec 22 '17 at 15:24
just edit the question and add the libs – Lucio Zenir Dec 22 '17 at 15:26 — Lucio Zenir, Dec 22 '17 at 15:26

Mateus Schneiders · Answer 1 · 2017-12-26T16:01:29.897

11

The Oracle.ManagedDataAccess.Client lib doesn't yet support BulkCopy.

You can compare functionality from both libs in the folowing link: Oracle Managed Driver Comparison

Related Question

Another option would be to use Array Binding.

Example:

using Oracle.ManagedDataAccess.Client;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            string connString = "Data Source=xyz; user id=**; password=**";
            using (var con = new OracleConnection(connString))
            {
                con.Open();
                int[] foos = new int[3] { 1, 2, 3 };
                string[] bars = new string[3] { "A", "B", "C" };

                OracleParameter pFoo = new OracleParameter();
                pFoo.OracleDbType = OracleDbType.Int32;
                pFoo.Value = foos;

                OracleParameter pBar = new OracleParameter();
                pBar.OracleDbType = OracleDbType.Varchar2;
                pBar.Value = bars;

                // create command and set properties
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = "insert into test (foo, bar) values (:1, :2)";
                cmd.ArrayBindCount = foos.Length;
                cmd.Parameters.Add(pFoo);
                cmd.Parameters.Add(pBar);
                cmd.ExecuteNonQuery();
            }
        }
    }
}

edited Dec 26 '17 at 16:01

answered Dec 22 '17 at 15:35

Mateus Schneiders

4,853
3
20
40

I know, that's why i ask for a bulk insert while i'm using this lib, has to be a way – Lucio Zenir Dec 22 '17 at 15:48
@LucioZenir Have you tried array binding? [Link](http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html) – Mateus Schneiders Dec 22 '17 at 17:49
@Mt.Schneiders -- are you sure array binding work with the managed client? For some reason, I thought that was not supported. I believe you, but we've been using DevArt because we thought it wasn't supported. If that's not the case, I'd like to switch to the Oracle client. The article you referenced didn't appear to say one way or the other. – Hambone Dec 24 '17 at 21:20
1

@Hambone, Yep, tested it just now, it works with the managed client. Edited the answer with a simple example. – Mateus Schneiders Dec 26 '17 at 15:33
Amazing... all these years. Thanks for the follow-up. – Hambone Dec 26 '17 at 17:13
This particular example doesn't account scenarios when some data can be NULL – T.S. Oct 01 '19 at 16:12
Overall, this process is working well for me. I just had a situation where the insert is failing for valid reasons. I am trying to add a Try/Catch to the code and it is still crashing because the cmd.ExecuteNonQuery() seems to be running as a background thread? I'm not sure how to catch that error? – cboshdave Oct 28 '20 at 15:00

score 2 · Answer 2 · answered Feb 03 '21 at 15:19

2

Oracle.ManagedDataAccess.Client started to support bulk copy since version 19.10 Try to use it for insert!

answered Feb 03 '21 at 15:19

Sajjad Vahedi

21
1

How to make a Bulk Insert using Oracle Managed Data Acess c#

2 Answers2