SqlBulkCopy giving FOREIGN KEY constraint error

sqlbulkcopy sql-server-2016

Question

I'm mass inserting entries into a database using SqlBulkCopy.

The code is provided below. It annoys me because when I utilizeSqlBulkCopy If I utilize the same records, I get a FOREIGN KEY constraint error.UNION ALL method it uses quite well. Why am I doing this wrong?

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

The table script for this issue is provided below:

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 use the following technologies:

  • Data Access block for DB Connections in Enterprise Library.
  • To change List to DataReader, enter Quick member.
1
0
9/25/2016 7:59:54 AM

Accepted Answer

The problem is brought on by SqlBulkCopy insert:

  • The StatusId column's KitId
  • The StatusId shown under KitId

AutoMapping is not very intelligent...

First, the ordinal mapping is applied to all columns:

  • TableColumn 0 is mapped to DataColumn 0 (KitId) (KitStatusId)
  • TableColumn1 was mapped to DataColumn 1 (StatusId) (KitIt)
  • TableColumn2 is mapped to DataColumn 2 (IsActiveStatus) (StatusId)

Code SqlBulkCopy

internal void CreateDefaultMapping(int columnCount)
{
  for (int index = 0; index < columnCount; ++index)
    this.InnerList.Add((object) new SqlBulkCopyColumnMapping(index, index));
}

Second, because IsActiveStatus does not fit the type of StatusId and KitStatusId is an identity, the AutoMapping will attempt to map to the next available column by mapping KitId to the StatusId column.

The IsActiveStatus is not mapped, as can be seen by looking at the SQL that was created.

insert bulk KitStatuses ([KitId] NVarChar(15) COLLATE SQL_Latin1_General_CP1_CI_AS, [StatusId] Int) with (CHECK_CONSTRAINTS)

Simply said, never trust the AutoMapping; it will always result in errors.

Instead, map your column directly.

copy.ColumnMappings.Add("KitId", "KitId");
copy.ColumnMappings.Add("StatusId", "StatusId");
copy.ColumnMappings.Add("IsActiveStatus", "IsActiveStatus");
1
9/27/2016 3:03:11 PM


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