Error doing Oracle bulk insert/update - Unable to cast object of type 'System.DateTime[]' to type 'System.IConvertible'

Question

I am trying to do bulk insert and/or update into an Oracle table, using data from a datatable. When I attempt to execute "command.ExecuteNonQuery()" it throws "Unable to cast object of type 'System.DateTime[]' to type 'System.IConvertible'". From what I have seen in other similar posts, it looks like System.DateTime cannot be used as System.IConvertable. Not sure how to fix it.

The watered down version is:

Database table (ACUTALITEMSINBAG):

CREATEDON       DATE
ABC             VARCHAR2(12 BYTE)
ACTUALWEIGHT    FLOAT

Sample data in datatable:

CREATEDON       ABC          ACTUALWEIGHT
2/1/2024     2ABCDEFGHI17    3.9
2/1/2024     2JKLMNOPQR17    6.06


public int InsertActualWeightTable(DataTable dt)
{
    int iRet = 0;
    List<OracleParameter> spParams = new List<OracleParameter>();
    DateTime[] CreatedOn = new DateTime[dt.Rows.Count];
    string[] ABC= new string[dt.Rows.Count];
    decimal[] ActualWeight = new decimal[dt.Rows.Count];

    for (int j = 0; j < dt.Rows.Count; j++)
    {
        CreatedOn[j] = Convert.ToDateTime(dt.Rows[j]["CREATEDON"]);
        ABC[j] = Convert.ToString(dt.Rows[j]["ABC"]);
        ActualWeight[j] = Convert.ToDecimal(dt.Rows[j]["ACTUALWEIGHT"]);
    }
    string sInsertQuery = "INSERT INTO ACUTALITEMSINBAG(CREATEDON, ABC, ACTUALWEIGHT) VALUES(:CREATEDON, :ABC, :ACTUALWEIGHT)";

    OracleParameter opCreatedON = new OracleParameter();
    opCreatedON.OracleDbType = OracleDbType.Date;
    opCreatedON.Value = CreatedOn;
    opCreatedON.ParameterName = "CREATEDON";
    spParams.Add(opCreatedON);

    OracleParameter opABC = new OracleParameter();
    opABC.OracleDbType = OracleDbType.Varchar2;
    opABC.Value = ABC;
    opABC.ParameterName = "ABC";
    spParams.Add(opABC);

    OracleParameter opActualWeight = new OracleParameter();
    opActualWeight.OracleDbType = OracleDbType.Decimal;
    opActualWeight.Value = ActualWeight;
    opActualWeight.ParameterName = "ACTUALWEIGHT";
    spParams.Add(opActualWeight);

    try
    {
        dbContext.Open();
        iRet = dbContext.InsertUsingOracleBulkCopy(sInsertQuery, spParams);
    }
    catch (Exception e)
    {
        throw e;
    }
    finally
    {
        dbContext.Close();
    }
    return iRet;
}


public int InsertUsingOracleBulkCopy(string sInsertQuery, List<OracleParameter> spParams)
{
    int iRet = 0;
    try
    {
        using (OracleCommand command = new OracleCommand())
        {
            command.CommandType = CommandType.Text;
            command.Parameters.AddRange(spParamsToArray<OracleParameter>());
            command.Connection = DbConnection;
            command.CommandText = sInsertQuery;
            command.BindByName = true;
            command.CommandText = sInsertQuery;
            iRet = command.ExecuteNonQuery();  // <--- THROWS ERROR HERE
        }
    }
    catch (Exception ex)
    {
        iRet = -1;
    }
    return iRet;
}

Answer 1

The solution was a one liner. In "InsertUsingOracleBulkCopy" method I had to add an extra parameter (iCount) which was the count of rows in datatable parameter, dt, in InsertActualWeightTable(DataTable dt) function, like so:

int iCount = dt.Rows.Count;
try
{
    dbContext.Open();
    iRet = dbContext.InsertUsingOracleBulkCopy(sInsertQuery, spParams, iCount);
}

I found the needed line in this SOF post. So the change was:

public int InsertUsingOracleBulkCopy(string sInsertQuery, List<OracleParameter> spParams, int iCount)
{
    int iRet = 0;
    try
    {
        using (OracleCommand command = new OracleCommand())
        {
            command.CommandType = CommandType.Text;
            command.Parameters.AddRange(spParams.ToArray<OracleParameter>());
            command.Connection = DbConnection;
            command.CommandText = sInsertQuery;
            command.BindByName = true;
            // This is critical to the process; in order for the command to 
            // recognize and bind arrays, an array bind count must be specified.
            command.ArrayBindCount = iCount;  // <-- This was the added line

            iRet = command.ExecuteNonQuery();
        }
    }
    catch (Exception ex)
    {
        iRet = -1;
        throw ex;
    }
    return iRet;
}

After this change, it worked like a charm.