SqlBulkCopy throws exception: The given value of type String from the data source cannot be converted to type bigint of the specified target column

Question

I'm trying for the first time to use SqlBulkCopy. I have a table created this way:

IF NOT EXISTS(SELECT 1 FROM sys.tables 
              WHERE Name = N'DetectionLoggingTime' 
                AND Object_ID = Object_ID(N'dbo.DetectionLoggingTime'))
BEGIN
    PRINT N'Creating [dbo].[DetectionLoggingTime]...';

    CREATE TABLE [dbo].[DetectionLoggingTime]
    (
        [LogId]       INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
        [ScreeningId] BIGINT        NOT NULL,
        [Message]     NVARCHAR(255) NOT NULL,
        [Time]        BIGINT        NULL
    );
END

I am trying to insert values in this way:

public async Task Handle(DetectionLoggingIntegrationEvent @event)
{
    var dt = new DataTable();
    dt.Columns.Add("ScreeningId");
    dt.Columns.Add("Message");
    dt.Columns.Add("Time");

    @event.OperationTimePairs.ForEach(pair => dt.Rows.Add(@event.ScreeningId, pair.Key, pair.Value));

    using (var sqlBulk = new SqlBulkCopy(_configProvider.MyConnectionString))
    {
        sqlBulk.DestinationTableName = "DetectionLoggingTime";
        sqlBulk.WriteToServer(dt);
    }
}

My feeling is that the insertion is trying to insert the LogId which I would like it to be incremental and automatically generated by SQL Server. What am I doing wrong?

Answer 1

Found the solution, explicit column mappings are required:

public async Task Handle(DetectionLoggingIntegrationEvent @event)
{
    var dt = new DataTable();
    dt.Columns.Add("ScreeningId", typeof(long));
    dt.Columns.Add("Message");
    dt.Columns.Add("Time", typeof(long));

    @event.OperationTimePairs.ForEach(pair => dt.Rows.Add(@event.ScreeningId, pair.Key, pair.Value));

    using (var sqlBulk = new SqlBulkCopy(_configProvider.FdbConnectionString))
    {
        var mapping = new SqlBulkCopyColumnMapping("ScreeningId", "ScreeningId");
        sqlBulk.ColumnMappings.Add(mapping);

        mapping = new SqlBulkCopyColumnMapping("Message", "Message");
        sqlBulk.ColumnMappings.Add(mapping);

        mapping = new SqlBulkCopyColumnMapping("Time", "Time");
        sqlBulk.ColumnMappings.Add(mapping);

        sqlBulk.DestinationTableName = "DetectionLoggingTime";
        sqlBulk.WriteToServer(dt);
    }
}