SqlBulkCopy Cannot insert null value into column
I'm trying to Bulkinsert to point a specific list of custom objects to my database using the fastmember ( Fastmember NuGet ) extension and sqlbulkcopy. However, this 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, 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 another table that also contains the same "functionblockId" and the same kind of other fields (but does not contain a bit field).
I manually checked all my values in the block.connections function list and then in a specific object its 'functionblockId' everything was filled with a number. Better yet, as you can see in the code, I actually fill it with hardcoded before I start embedding it.
I don't know why this error occurs, does anyone have any ideas?
source to share
After manually processing the DataTable with test data, it still gave me the same error.
I came across this post ( codeproject solution ) and applied SqlBulkCopyColumnMapping to my case.
Sample code:
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);
} }
source to share
Not very familiar with this syntax: I wonder if the "item" object is actually used in a transaction. This is similar to the ObjectReader.Create steps through connections, independent "item" references.
Try to reduce the number of transactions to confirm that the row count is correct, because you might step on the iterator.
Also, maybe you can try capturing for unexpected item.functionblockId values as a diagnostic tool.
source to share