CODEWITHSUNDEEP
c#oracle-databasebulkinsert
Lucio Zenir
Lucio Zenir

Reputation: 385

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

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?

Upvotes: 4

Views: 11574

Answers (2)

Sajjad Vahedi
Sajjad Vahedi

Reputation: 31

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

Upvotes: 3

Mateus Schneiders
Mateus Schneiders

Reputation: 4903

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

Upvotes: 12

Related Questions