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?

Tarta · Accepted Answer · 2018-11-06 09:37:38Z

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);
    }
}

Jonathan Magnan · Accepted Answer · 2018-10-31 21:03:47Z

0

That issue is often caused before the column type in the DataTable are not explicit.

By example, setting the column type might fix that error:

var dt = new DataTable();
dt.Columns.Add("ScreeningId", typeof(long));
dt.Columns.Add("Message");
dt.Columns.Add("Time", typeof(long));

More documentation about this issue here: https://sqlbulkcopy-tutorial.net/type-a-cannot-be-converted-to-type-b

answered Oct 31, 2018 at 21:03

Jonathan Magnan

11.4k2 gold badges46 silver badges65 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related