I am using SqlBulkCopy
to bulk insert records into a database.
Below is the code for it. What bothers me is that when I use SqlBulkCopy
I get FOREIGN KEY constraint error and for exact same records if I use UNION ALL
approach it works just fine. What am I doing wrong here?
public partial class Repository
{
public bool InsertResult(List<string> kitIds)
{
if (kitIds == null || kitIds.Count == 0)
return false;
using (var connection = (SqlConnection)_database.CreateConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
using (SqlBulkCopy copy = new SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, transaction))
{
/*This works*/
var sb = new StringBuilder(2048);
sb.AppendLine("INSERT INTO KITSTATUSES (KitId, StatusId, IsActiveStatus) ");
for (int i = 0; i < kitIds.Count; i++)
{
sb.AppendLine($"SELECT '{kitIds[i]}', {(int)StatusOfKit.ResultUploaded}, 1");
sb.AppendLine("UNION ALL ");
}
sb.Remove(sb.Length - 12, 12);
connection.Execute(sb.ToString(), null, transaction);
/*DOES NOT WORK and throws error:
* The INSERT statement conflicted with the FOREIGN KEY constraint "FK_KitStatuses_Kits". The conflict occurred in database "GeneBlueprint", table "dbo.Kits", column 'KitId'.
* The statement has been terminated.
var kitStatuses = kitIds.Select(k => new KitStatus { KitId = k, IsActiveStatus = true, StatusId = (int)StatusOfKit.ResultUploaded }).ToList();
using (var reader = ObjectReader.Create(kitStatuses, "KitId", "StatusId", "IsActiveStatus"))
{
//Verify that reader has right values
//while (reader.Read())
//{
// Debug.WriteLine($"KitId: {reader.GetFieldValue<string>(0)}, StatusId: {reader.GetFieldValue<int>(1)}, IActiveStatus: {reader.GetFieldValue<bool>(2)}, StatusDate: {reader.GetFieldValue<DateTime>(3)}");
//}
copy.DestinationTableName = "KitStatuses";
copy.WriteToServer(reader);
}
*/
/*DOES NOT WORK and throws error
* The INSERT statement conflicted with the FOREIGN KEY constraint "FK_KitStatuses_Kits". The conflict occurred in database "GeneBlueprint", table "dbo.Kits", column 'KitId'.
* The statement has been terminated.
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn() { ColumnName = "KitId", DataType = typeof(string), MaxLength = 15, AllowDBNull = false, AutoIncrement = false });
dt.Columns.Add(new DataColumn() { ColumnName = "StatusId", DataType = typeof(int), AllowDBNull = false, AutoIncrement = false, DefaultValue = 8 });
dt.Columns.Add(new DataColumn() { ColumnName = "IsActiveStatus", DataType = typeof(bool), AllowDBNull = false, AutoIncrement = false, DefaultValue = true });
for (int i = 0; i < kitIds.Count; i++)
{
var row = dt.NewRow();
row[0] = kitIds[i];
row[1] = 8;
row[2] = true;
dt.Rows.Add(row);
}
using (var reader = ObjectReader.Create(kitStatuses, "KitId", "StatusId", "IsActiveStatus", "StatusDate"))
{
copy.DestinationTableName = "KitStatuses";
copy.WriteToServer(dt);
}
*/
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
return true;
}
}
Below is script for tables relevant to this question:
CREATE TABLE [dbo].[Kits]
(
[KitId] [nvarchar](15) NOT NULL,
CONSTRAINT [PK_Kits] PRIMARY KEY CLUSTERED ([KitId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[KitStatuses]
(
[KitStatusId] [int] IDENTITY(1,1) NOT NULL,
[KitId] [nvarchar](15) NOT NULL,
[StatusId] [int] NOT NULL,
[StatusDate] [datetime] NOT NULL,
[IsActiveStatus] [bit] NOT NULL,
CONSTRAINT [PK_KitStatuses] PRIMARY KEY CLUSTERED([KitStatusId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_KitStatuses_KitId_KitStatus]
UNIQUE NONCLUSTERED ([KitId] ASC, [KitStatusId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Statuses]
(
[StatusId] [int] IDENTITY(1,1) NOT NULL,
[StatusName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Statuses] PRIMARY KEY CLUSTERED ([StatusId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KitStatuses]
ADD CONSTRAINT [DF_KitStatuses_StatusDate]
DEFAULT (getdate()) FOR [StatusDate]
ALTER TABLE [dbo].[KitStatuses]
ADD CONSTRAINT [DF_KitStatuses_IsActiveStatus]
DEFAULT ((1)) FOR [IsActiveStatus]
ALTER TABLE [dbo].[KitStatuses] WITH CHECK
ADD CONSTRAINT [FK_KitStatuses_Kits]
FOREIGN KEY([KitId]) REFERENCES [dbo].[Kits] ([KitId])
ALTER TABLE [dbo].[KitStatuses] CHECK CONSTRAINT [FK_KitStatuses_Kits]
ALTER TABLE [dbo].[KitStatuses] WITH CHECK
ADD CONSTRAINT [FK_KitStatuses_Statuses]
FOREIGN KEY([StatusId]) REFERENCES [dbo].[Statuses] ([StatusId])
ALTER TABLE [dbo].[KitStatuses] CHECK CONSTRAINT [FK_KitStatuses_Statuses]
I am using below technologies:
The issue is caused because SqlBulkCopy insert:
AutoMapping is not really smart...
Firstly, all columns are mapped by ordinal:
SqlBulkCopy Code
internal void CreateDefaultMapping(int columnCount)
{
for (int index = 0; index < columnCount; ++index)
this.InnerList.Add((object) new SqlBulkCopyColumnMapping(index, index));
}
Secondly, since the KitStatusId is an identity, the AutoMapping will try to map to the next available column, and since IsActiveStatus doesn't match with the type of StatusId, the AutoMapping will map the KitId to the StatusId column.
If you look at the SQL generated, you will see the IsActiveStatus is not mapped
insert bulk KitStatuses ([KitId] NVarChar(15) COLLATE SQL_Latin1_General_CP1_CI_AS, [StatusId] Int) with (CHECK_CONSTRAINTS)
In short, never trust the AutoMapping this will only lead to some error.
Map your column explicitly instead
copy.ColumnMappings.Add("KitId", "KitId");
copy.ColumnMappings.Add("StatusId", "StatusId");
copy.ColumnMappings.Add("IsActiveStatus", "IsActiveStatus");