c# - SqlBulkCopy 无法将值 NULL 插入到列中

Question

我正在尝试使用 fastmember ( Fastmember NuGet) 扩展和 sqlbulkcopy 将特定的自定义对象列表批量插入到我的数据库中。但是它给了我以下错误:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Cannot insert the value NULL into column 'functionblockId', table '\path...\DBFHX.MDF.dbo.connections'; column does not allow nulls. INSERT fails.

The statement has been terminated.

代码:

private void insertConnection(functionblock functionblock)
{
    using (var bcp = new SqlBulkCopy(db.Database.Connection.ConnectionString))
    {
        foreach (connection item in functionblock.connections)
                {
                    item.functionblockId = 1;
                }
        using (var creader = ObjectReader.Create(functionblock.connections, "step", "transition","steptotrans", "functionblockId"))
                {
                    bcp.DestinationTableName = "connections";
                    bcp.WriteToServer(creader);
                }   
    }
}

使用模型优先， Entity Framework 生成下表:

CREATE TABLE [dbo].[connections] (
    [Id]              INT            IDENTITY (1, 1) NOT NULL,
    [step]            NVARCHAR (MAX) NOT NULL,
    [transition]      NVARCHAR (MAX) NOT NULL,
    [steptotrans]     BIT            NOT NULL,
    [functionblockId] INT            NOT NULL,
    CONSTRAINT [PK_connections] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_functionblockconnection] FOREIGN KEY ([functionblockId]) REFERENCES [dbo].[functionblocks] ([Id])
);

该代码适用于不同的表，该表也包含相同的“functionblockId”和相同类型的其他字段(尽管不包含位字段)。

我手动确实检查了 functionblock.connections 列表中的所有值，然后在特定对象的“functionblockId”中，它全部填充了一个数字。更好的是，正如您在代码中看到的那样，我实际上在批量插入之前确实对其进行了硬编码。

我不知道为什么会出现这个错误，有人知道吗？

Answer 1

在用测试数据手动制作DataTable后，它仍然给我同样的错误。

我看到这篇文章 ( codeproject solution )，并将 SqlBulkCopyColumnMapping 应用到我的案例中。

代码示例:

using (var bcp = new SqlBulkCopy(fhxm.Database.Connection.ConnectionString))
                                    {
                                    using (var creader = ObjectReader.Create(functionblock.connections, "step", "transition", "steptotrans", "functionblockId"))
                                    {
                                    SqlBulkCopyColumnMapping mapstep = new SqlBulkCopyColumnMapping("step", "step");
                                    SqlBulkCopyColumnMapping maptran = new SqlBulkCopyColumnMapping("transition", "transition");
                                    SqlBulkCopyColumnMapping mapstt = new SqlBulkCopyColumnMapping("steptotrans", "steptotrans");
                                    SqlBulkCopyColumnMapping mapfunc = new SqlBulkCopyColumnMapping("functionblockId", "functionblockId");
                                    bcp.ColumnMappings.Add(mapstep);
                                    bcp.ColumnMappings.Add(maptran);
                                    bcp.ColumnMappings.Add(mapstt);
                                    bcp.ColumnMappings.Add(mapfunc);

                                    bcp.DestinationTableName = "connections";
                                    bcp.WriteToServer(creader);
                                    }   }