Bulk data insert from SQL to Oracle using C#.NET

Question

The following code snippet for for bulk insert of data from SQL DB into Oracle database.

I have used stored procedure in oracle database with 2 parameters (int and string)

I am getting the below error while executing the query. Please help me to resolve this. or suggest any good solution for bulk data insert.

My Query:

List<int> arrPersonId = new List<int>();

        foreach (DataRow row in ds.Tables[0].Rows)
        {
            arrPersonId.Add(Convert.ToInt32(row["USER_ID"]));

        }

        List<string> arrPersonName = new List<string>();

        foreach (DataRow row in ds.Tables[0].Rows)
        {
            arrPersonName.Add(row["USERNAME"].ToString());
        }

        OracleConnection connection = new OracleConnection();
        connection.ConnectionString = DAKObj.GetOraConnectionString();

        OracleCommand command = new OracleCommand();
        command.Connection = connection;

        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "sp_InsertByODPNET";

        command.Parameters.Add("@PersonId", OracleDbType.Int32);
        command.Parameters[0].Value = arrPersonId;
        command.Parameters.Add("@PersonName", OracleDbType.Varchar2, 100);
        command.Parameters[1].Value = arrPersonName;
       command.ArrayBindCount = arrPersonId.Count;

       connection.Open();
       command.ExecuteNonQuery();
       connection.Close();

Error:-

An exception of type 'System.InvalidCastException' occurred in Oracle.DataAccess.dll but was not handled in user code

Additional information: Unable to cast object of type 'System.Collections.Generic.List`1[System.Int32]' to type 'System.Array'

Have you tried using Table Valued Parameters for bulk inserts? Could give you massive performance benefits. — Rahul Kishore
– Rahul Kishore, Commented Jan 7, 2017 at 20:01

Sam Bin Ham · Accepted Answer · 2017-01-07 20:42:24Z

The problem appears to be that the .Net provider doesn't like List<int>, and isn't smart enough (or have enough information) to call ToArray to get an array. You might try something like the following:

List<int> lstPersonId = new List<int>();

foreach (DataRow row in ds.Tables[0].Rows)
  {
  lstPersonId.Add(Convert.ToInt32(row["USER_ID"]));
  }

List<string> lstPersonName = new List<string>();

foreach (DataRow row in ds.Tables[0].Rows)
  {
  lstPersonName.Add(row["USERNAME"].ToString());
  }

int[] arrPersonId = lstPersonId.ToArray();
string[] arrPersonName = lstPersonName.ToArray();

OracleConnection connection = new OracleConnection();
connection.ConnectionString = DAKObj.GetOraConnectionString();

OracleCommand command = new OracleCommand();
command.Connection = connection;

command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_InsertByODPNET";

command.Parameters.Add("@PersonId", OracleDbType.Int32);
command.Parameters[0].Value = arrPersonId;
command.Parameters.Add("@PersonName", OracleDbType.Varchar2, 100);
command.Parameters[1].Value = arrPersonName;
command.ArrayBindCount = arrPersonId.Length;

connection.Open();
command.ExecuteNonQuery();
connection.Close();

Not tested on animals - you'll be first! :-)

Collectives™ on Stack Overflow

Bulk data insert from SQL to Oracle using C#.NET

Error:-

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Error:-

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related