Bulk insert to Oracle from a DataTable using C#

Question

I've been searching but have not found a good example of what i need to accomplish. The title says it all. This is what i have so far:

            //gather the report details
            DataTable dtReturn = new DataTable();
            DataTable dtResults = new DataTable();
            string strScript = "";
            bool doReturnData = false;

            try
            {
//this function returns my SQL table in to a datatable dtReturn:
                var ii =
                    Utility.db.Connection.EstablishDBConnection("usp_get_data_recap", dtReturn, null, null, true, 60);

//Clear out table before insert
                dtResults = OracleDataPull(9, "TRUNCATE TABLE user.SIGNUP_1");

                OracleParameter myparam = new OracleParameter();
                OracleCommand mycommand = new OracleCommand();
                int n;

//bulk insert to the signup_1 table from the datatable members. Datatable contains the same 4 fields being inserted in to signup_1 on the oracle side:
                mycommand.CommandText = "INSERT INTO user.SIGNUP_1 ([ID], [ACCOUNT_NUMBER], [MAIN_CUSTOMER], [SIGNUP_DATE]) VALUES(?)";
                mycommand.Parameters.Add(myparam);

                for (n = 0; n < 100000; n++)
                {
                    [what do i do here?]
                }
            }

I am not sure if this is correct, or if there is an easier way, but i need to map dtReturn.Rows[n][0-3] to ID, account_number, main_customer, and signup_date respectively.

Help is very apprecaited! Thanks in advance!

edit:

i tried the suggestion below, but am getting an error with the lambda expression: "Cannot convert lambda expression to type 'string' because it is not a delegate type" :

var ii =
    Utility.db.Connection.EstablishDBConnection("usp_get_data", dtReturn, null, null, true, 60);

dtResults = OracleDataPull(9, "TRUNCATE TABLE user.PR_data");

OracleParameter myparam = new OracleParameter();
OracleCommand mycommand = new OracleCommand();

mycommand.ArrayBindCount = dtReturn.Rows.Count;
mycommand.Parameters.Add(":myparam", OracleDbType.Varchar2, dtReturn.Select(c => c.myparam).ToArray(), ParameterDirection.Input);
mycommand.ExecuteNonQuery();

int n;

mycommand.CommandText = "INSERT INTO user.PR_data ([ID], [ACCOUNT_NUMBER], [MAIN_CUSTOMER], [SIGNUP_DATE]) VALUES(?)";
mycommand.Parameters.Add(myparam);

for (n = 0; n < 100000; n++)
{
    myparam.Value = n + 1;
    mycommand.ExecuteNonQuery();
}

dtResults = Utility.db.Connection.oracletoDataTable(strScript, doReturnData);

I am also not sure this is set up to produce the correct results. Can you please advise where i am going wrong here?

where is the fields that you want to insert? is it in your datatable? — Jeric Cruz, Sep 29 '17 at 05:15
Take a look at this post: https://stackoverflow.com/questions/343299/bulk-insert-to-oracle-using-net/797636#797636. The presented issue similar to what you have with minor differences. — Tetsuya Yamamoto, Sep 29 '17 at 07:08
The fields i want to insert are the ones i indicated in my post above which need to go to the Oracle table that i indicated in my post. And yes, i looked at that post. That is where i pulled my logic above that is unfortunately not quite where i need it to be. I need to be able to traverse the datatable (layout described above) for the bulk insert to Oracle. Thanks! — Kevin Henzel, Sep 29 '17 at 17:40

score 3 · Accepted Answer · answered Oct 04 '17 at 02:51

I actually found a more efficient approach to this solution thanks to codeproject.com.

This is my final method that is working great. All i do is call it with my schema.table and datatable and it handles the rest:

public static void WriteToServer(string qualifiedTableName, DataTable dataTable)
            {
                //**************************************************************************************************************************
                //  Summary:  Hit the Oracle DB with the provided datatable. bulk insert data to table.
                //**************************************************************************************************************************
                //  History:
                //   10/03/2017                 Created
                //**************************************************************************************************************************

                try
                {
                    OracleConnection oracleConnection = new OracleConnection(Variables.strOracleCS);

                    oracleConnection.Open();
                    using (OracleBulkCopy bulkCopy = new OracleBulkCopy(oracleConnection))
                    {
                        bulkCopy.DestinationTableName = qualifiedTableName;
                        bulkCopy.WriteToServer(dataTable);
                    }
                    oracleConnection.Close();
                    oracleConnection.Dispose();
                }
                catch (Exception ex)
                {
                    Utility.db.Log.Write(Utility.db.Log.Level.Error, "Utility", "db:WriteToServer: " + ex.Message);
                    throw;
                }
            }

ref: https://www.codeproject.com/Questions/228101/oracle-data-bulk-insert

OracleBulkCopyClass is not good, IMHO. It disables indexes when performing inserts. This means you'll have a serious problems when you''ll insert duplicate key values. Indexes go in unused state. And that is only one problem with It. — Lucy82, Dec 22 '20 at 07:55
Hello @Kevin Henzel ! I am trying to do a bulk insert into oracle table from C# code. However I do not get any error but the data is not getting inserted. I am sure I followed all the necessary configuration. Where could I check more details? — Black Pearl, Aug 28 '22 at 14:27

score 0 · Answer 2 · answered Oct 01 '17 at 05:15

0

mycommand.ArrayBindCount = dtResults.Count;
mycommand.Parameters.Add(":parameterName", OracleDbType.Varchar2, dtResults.Select(c => c.ParameterName).ToArray(), ParameterDirection.Input);
mycommand.ExecuteNonQuery() ;

after CommandText set you may use the code above.

answered Oct 01 '17 at 05:15

Burak Kalafat

68
2
15

This did not work. It is producing an error. Please see my edit above. – Kevin Henzel Oct 04 '17 at 02:31

Bulk insert to Oracle from a DataTable using C#

2 Answers2