FOREIGN KEY制約エラーを返すSqlBulkCopy

sqlbulkcopy sql-server-2016

質問

私はSqlBulkCopyを使用してレコードをデータベースに一括挿入しています。

以下はそのコードです。私が気になるのは、 SqlBulkCopyを使用するとFOREIGN KEY制約エラーが発生し、 UNION ALLアプローチを使用するとまったく同じレコードが得られます。私はここで間違って何をしていますか?

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

以下はこの質問に関連するテーブルのスクリプトです:

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]

私は以下の技術を使用しています:

  • DB接続用のエンタープライズライブラリのデータアクセスブロック。
  • リストをDataReaderに変換する高速メンバー

受け入れられた回答

この問題はSqlBulkCopyが次を挿入するために発生します。

  • StatusId列のKitId
  • KitId列のStatusId

オートマッピングは本当にスマートではありません...

まず、すべての列が序数によってマッピングされます。

  • TableColumn 0にマップされたDataColumn 0(KitId)(KitStatusId)
  • TableColumn1(KitIt)にマップされたDataColumn 1(StatusId)
  • TableColumn2(StatusId)にマップされたDataColumn 2(IsActiveStatus)

SqlBulkCopyコード

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

次に、KitStatusIdがIDなので、AutoMappingは次の使用可能な列にマップしようとします。そして、IsActiveStatusがStatusIdの型と一致しないので、AutoMappingはKitIdをStatusId列にマッピングします。

生成されたSQLを見ると、IsActiveStatusがマップされていないことがわかります

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

要するに、AutoMappingを信用していないと、これは何らかのエラーにつながるだけです。

代わりに明示的に列をマップする

copy.ColumnMappings.Add("KitId", "KitId");
copy.ColumnMappings.Add("StatusId", "StatusId");
copy.ColumnMappings.Add("IsActiveStatus", "IsActiveStatus");


ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ
ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ