C# SQL BulkCopy datetime insert issue

Question

I'm having an issue bulk inserting a data table containing a C# DateTime object into a SQL Server database, and keep getting the following error:

The given value of type String from the data source cannot be converted to type datetime of the specified target column

I ran a stack trace to see what the table was trying to insert (picture below)

Datatable insert

Here's the code I'm using to parse the date

 SInstance s = new SInstance {                    
                Timestamp = DateTime.ParseExact(values[0],"yyyy-MM-dd",null),
               //other property initializations                   
            };

where

values[0] = "2018-08-08"

Most of the other answers say to manually configure the data table with the appropriate formats, but these tables are generated dynamically at run-time using class properties so hard-coding anything is out of the question. Here's the code for the bulk copy snippet

DataTable dt = DBOHelper.GenerateDataTable(dbObjects);

using (SqlBulkCopy sqlBulk = _database.BulkCopySQL())
{
    sqlBulk.DestinationTableName = table;
    sqlBulk.BatchSize = commitBatchSize;

    try
    {
        sqlBulk.WriteToServer(dt);
        dt.Dispose();
    }
    catch (Exception e)
    {
        AuditEngine.Instance.LogError("DatabaseEngine", "SQLBulkInsert", e.Message);
        return 0;
    }
}

and table generator function:

public static DataTable GenerateDataTable<T>(DBOCollection<T> dBOCollection)
        where T : DbObject
{
    DataTable dt = GenerateEmptyDataTable<T>();

    List<PropertyInfo> props = GetDBODataMemberProperties<T>();

    foreach (DbObject dbo in dBOCollection)
    {
        DataRow row = dt.NewRow();

        for (int i = 0; i < props.Count; i++)
        {
            row[i] = props[i].GetValue(dbo);
        }

        dt.Rows.Add(row);
    }

    return dt;
}

public static DataTable GenerateEmptyDataTable<T>()
        where T : DbObject
{
    DataTable dt = new DataTable();

    List<PropertyInfo> properties = GetDBODataMemberProperties<T>();

    foreach (PropertyInfo p in properties)
    {              
        dt.Columns.Add(p.Name, p.PropertyType);
    }

    return dt;
}

Everything works fine for objects without date fields, but for some reason it throws an error with all the date formats I've tried. Thanks in advance for any suggestions.

Is it because you are converting to Timestamp and not System.DateTime? I'm not sure where this line `Timestamp = DateTime.ParseExact(values[0],"yyyy-MM-dd",null)` is — TomC, Aug 22 '18 at 02:18
Sorry for the confusion, Timestamp is a property name of an object in an ORM scheme, not the type of object being written to. I'll update the question. — bruce_M, Aug 22 '18 at 15:03

score 0 · Answer 1 · answered Aug 22 '18 at 05:17

You are using TimeStamp, time stamp will result in string, meaning that if you will execute the following:

Timestamp = DateTime.ParseExact(values[0],"yyyy-MM-dd",null)

You will get a string. Just for reference, here is a method to generate time stamp:

public static String GetTimestamp(this DateTime value)
{
    return value.ToString("yyyyMMddHHmmssfff");
}

All you need to do is change it to:

DateTime = DateTime.ParseExact(values[0],"yyyy-MM-dd",null);

I'd recommend:

DateTime date; 
 if(DateTime.TryParseExact(values[0], "yyyy-MM-dd", 
        System.Globalization.CultureInfo.InvariantCulture,
        System.Globalization.DateTimeStyles.None, out date)
{
   //succeeded, date contains the value
}
else
{
  //error
}

Sorry Barr J I didn't give enough information in the question, ""Timestamp" is the name of a property not a class in this context. I've updated the question accordingly. — bruce_M, Aug 22 '18 at 15:09

score 0 · Answer 2 · answered Aug 22 '18 at 15:30

The answer was to generate a list of property names at run-time, and then map those to the SqlBulkCopy object (this only works if the database column names and property names match)

DataTable dt = DBOHelper.GenerateDataTable(dbObjects, out List<string> propertyNames);

        using (SqlBulkCopy sqlBulk = _database.BulkCopySQL())
        {
            sqlBulk.DestinationTableName = table;
            sqlBulk.BatchSize = commitBatchSize;

            foreach (string s in propertyNames)
            {
                sqlBulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(s, s));
            }

            try
            {
                sqlBulk.WriteToServer(dt);
                dt.Dispose();
            }
            catch (Exception e)
            {
                AuditEngine.Instance.LogError("DatabaseEngine", "SQLBulkInsert", e.Message);
                return 0;
            }
        }

C# SQL BulkCopy datetime insert issue

2 Answers2