Oracle C# Multiple line insertion using arrays of DateTime

Question

I am trying to simply and quickly inserts 1000s of rows into an Oracle table using a C# application.

I thought to use this method of insertion, where an array of data passed through as parameters instead of creating my won long command string containing INTOs: https://www.c-sharpcorner.com/article/two-ways-to-insert-bulk-data-into-oracle-database-using-c-sharp/

My issue is that I am using DateTime values, which needs to be specified, can I do that for the full array?

Code:

public System.TimeSpan oracleInsertData(string tableName, List<DateTime> dates, List<double> data, OracleConnection con)
        {
            try
            {
                DateTime start = DateTime.Now;
                //first empty the table
                oracleEmptyTable(tableName, con);

                con.Open();

                // create command and set properties  
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = "INSERT INTO " + tableName + " (DateStamp, SensorData) VALUES (:1, :2)";
                cmd.ArrayBindCount = dates.Count;
                cmd.Parameters.Add(dates.ToArray());
                cmd.Parameters.Add(data.ToArray());
                cmd.ExecuteNonQuery();  

                con.Close();
                DateTime finish = DateTime.Now;

                return finish - start;
            }
            catch (OracleException ex)
            {
                con.Close();

                Console.WriteLine("Error: {0}", ex.ToString());
                return new TimeSpan(0);
            }
        }

I get the following error:


Additional information: Unable to cast object of type 'System.DateTime[]' to type 'Oracle.DataAccess.Client.OracleParameter'.

Edit: honey_ramgarhia pointed out my obvious mistake... with the udpated code it works. Code to hopefully help for some people:

        public System.TimeSpan oracleInsertData(string tableName, List<DateTime> dates, List<double> data, OracleConnection con)
        {
            try
            {
                DateTime start = DateTime.Now;
                //first empty the table
                oracleEmptyTable(tableName, con);

                con.Open();

                OracleParameter oracleDates = new OracleParameter();
                oracleDates.OracleDbType = OracleDbType.TimeStamp;
                oracleDates.Value = dates.ToArray();

                OracleParameter oracleData = new OracleParameter();
                oracleData.OracleDbType = OracleDbType.Double;
                oracleData.Value = data.ToArray();  

                // create command and set properties  
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = "INSERT INTO " + tableName + " (DateStamp, SensorData) VALUES (:1, :2)";
                cmd.ArrayBindCount = dates.Count;
                cmd.Parameters.Add(oracleDates);
                cmd.Parameters.Add(oracleData);
                cmd.ExecuteNonQuery();  

                con.Close();
                DateTime finish = DateTime.Now;

                return finish - start;
            }
            catch (OracleException ex)
            {
                try
                {
                    con.Close();
                }
                catch { }
                Console.WriteLine("Error: {0}", ex.ToString());
                return new TimeSpan(0);
            }
        }

Just updated my question, they are Lists, which I convert to arrays. — Daniel, May 16 '21 at 14:59
So according to the link that you mentioned, they are passing OracleParameter in `cmd.Parameters.Add(id)` method. They doing are this `id.OracleDbType = OracleDbType.Int32;` and assigning values like this `id.Value = ids;`. Could this be the reason? — honey_ramgarhia, May 16 '21 at 15:05
Long Sunday... You are totally right, it was the issue. Values needs to be passed as arrays not lists, but then it works. Thank you for your help! — Daniel, May 16 '21 at 15:11

score 0 · Accepted Answer · answered May 16 '21 at 15:18

Here you are trying to add DateTime directly into OracleCommand.Parameters rather creating OracleParameter first with defined data type in the property of OracleDbType then add.

Here you can go :

Each date value should be typecast with “(Oracle.DataAccess.Types.OracleTimeStamp)” in dates Array before assign it to OracleParameter. (refer Link below)

OracleParameter dateParam = new OracleParameter();  
dateParam.OracleDbType = OracleDbType. TimeStamp;  
dateParam.Value = dates;

LINK : How to pass DateTime parameter in SQL query for Oracle DB

Oracle C# Multiple line insertion using arrays of DateTime

1 Answers1