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

  • Question

  • HI,

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

    I have user storedprocedure 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();

    Thanks ,

    Salman

    Saturday, January 7, 2017 7:24 PM

All replies

  • Hi salmanpc,

    Based on your description, it seems that you want to pass a list to oracle store procedure. please try to modify your code like this:

    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.ToArray();
           command.ArrayBindCount = arrPersonId.Count;
    

    For more information, please refer to:

    http://stackoverflow.com/questions/9700173/odp-net-how-to-pass-array-of-strings-to-an-oracle-stored-procedure

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 9, 2017 7:59 AM
    Moderator
  • Greetings,

    just to tap the mismatch between the bulk-loading and the code-snippet.

    If you want faster insert and use bulkloading (which is a tad faster) you would need the Oracle-dlls as per:

    https://docs.oracle.com/cd/E17666_01/doc/win.112/e17357/OracleBulkCopyClass.htm
    download for 11g( > ) http://download.oracle.com/docs/html/E10927_01/OracleBulkCopyClass.htm..

    and for my knowledge, brend_k made a nice post in 2011 as example for an implementation in C#:
    http://stackoverflow.com/a/4918808

    However if Cole-Wu's answer is the one you're looking for; be carefull with calling commit.. that is the part that hardens your insert so it can be read and survive restores but also takes the most time. So doing it after inserting X rows instead of on each will improve the speed a bit.

    with regards,
    Sebastian

    Wednesday, January 11, 2017 8:48 AM