SqlBulkCopy Cannot insert the value NULL into column

Question

I am trying to Bulkinsert a certain list of custom objects into my database, using the fastmember (Fastmember NuGet) extension and sqlbulkcopy. However it gives me the following error:

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.

Code:

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

Using Model First, the Entity Framework generated the following table:

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

The code works fine for a different table which also contains that same "functionblockId" and the same sort of other fields (does not contain a bit field though).

I manually did check all my values in functionblock.connections list and then in specific the object its 'functionblockId', it was all filled in with a number. Even better, as you see in the code I actually do fill it hardcoded before I bulkinsert it.

I have no clue on why this error is raised, does anyone have an idea?

Sferics

Answer 1

After manually making a DataTable with test data it still gave me the same error.

I came across this post (codeproject solution), and applied the SqlBulkCopyColumnMapping to my case.

Code example:

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

Sferics

You have to skip the identity column by using the column mappings. Does your other table not have an identity column? – Patchouli

It does have an identity column, later I found out if I do this: ObjectReader.Create(functionblock.connections, "Id", "step", "transition","steptotrans", "functionblockId")) it inserts also without the column mapping. – Sferics

Answer 2

Not terribly familiar w/ this syntax, however: I wonder if the 'item' object actually gets used in the transaction. It looks like the ObjectReader.Create steps through the connections, independent of the 'item' reference.

Try reducing the transaction count to confirm the correct row counts are arriving, because you may be stepping on the iterator.

also, perhaps you could try trapping for unexpected values of item.functionblockId, as a diagnostic tool.

Mortimer