SqlBulkCopy Cannot insert the value NULL into column

.net c# fastmember sql sqlbulkcopy

Question

I'm attempting to bulk insert a certain set of custom objects into my database using sqlbulkcopy and the fastmember (NuGet Fastmember) extension. I get the following problem, though:

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

The Entity Framework produced the following table using Model First:

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 correctly for a different table that likewise has the "functionblockId" and similar additional attributes (does not contain a bit field though).

I carefully verified all of my entries in the functionblock.connections list, paying particular attention to the object's "functionblockId," which was all filled in with a number. Even better, as you can see from the code, I do populate it with hardcoded information before bulk-inserting it.

Does anybody know why this mistake is occurring? I have no idea why it is occurring.

1
3
9/30/2014 4:06:38 PM

Accepted Answer

I created a DataTable manually using test data, but I still received the same issue.

I found this article (codeproject remedy) and used the SqlBulkCopyColumnMapping in my situation.

Code illustration

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);
                                    }   } 
6
10/1/2014 8:35:11 AM

Popular Answer

I'm not really acquainted with this syntax, though: I'm curious as to whether the "item" object is really utilized throughout the transaction. It seems to be an ObjectReader. Regardless of the reference to the "object," create steps using the connections.

You could be stepping on the iterator, so try lowering the transaction count to make sure the right row counts are coming.

Additionally, as a diagnostic tool, you may attempt capturing for strange values of item.functionblockId.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow