代码之家 › 专栏 › 技术社区 › Tarta

SqlBulkCopy引发异常:数据源中String类型的给定值无法转换为指定目标列的bigint类型

sqlbulkcopy sql-server sql c#

0

Tarta · 技术社区 · 7 年前

我第一次尝试使用 SqlBulkCopy

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

我尝试以这种方式插入值:

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

我的感觉是插入是为了插入 逻辑标识

2 回复 | 直到 7 年前

1

Tarta 7 年前

找到解决方案后,需要显式列映射:

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

2

0

Jonathan Magnan 7 年前

这个问题通常是在 DataTable 不明确。

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

https://sqlbulkcopy-tutorial.net/type-a-cannot-be-converted-to-type-b