c# - 从 C# 列表批量插入 SQL Server 到具有外键约束的多个表中

Question

我对这个问题一无所知，非常感谢任何帮助:

我有两张表，一张是主数据表(Table A)，另一张表(Table B)与Table A中的一个条目有多个条目(具体18个)的外键关系。 .

我正在获取列表中的数据，并希望将其插入到 SQL Server 数据库中。

我目前正在使用以下模式，但在 Table A 中插入 100 行需要花费 14 分钟和 Table B 中对应的 18*100 行.

using (SqlConnection conn = new SqlConnection(conStr))
{
    foreach (var ticket in Tickets)
    {
        sql = string.Format(@"INSERT INTO dbo.Tickets([ColumnA], [ColumnB] ,..." + @")
                              VALUES(@ColumnA, @ColumnB,@ColumnC, @ColumnD, .... +
                            @"SELECT SCOPE_IDENTITY();");

        using (cmd = new SqlCommand(sql, conn))
        {
            cmd.Parameters.AddWithValue("@ColumnA", (object)ticket.Id ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@ColumnB", (object)ticket.Address ?? DBNull.Value);
            cmd.Parameters.AddWithValue("@ColumnC", (object)ticket.Status?? DBNull.Value);
            ....

            conn.Open();
            TableA_TicketId = Convert.ToInt32(cmd.ExecuteScalar());
        }
    }
} 

我使用 SCOPE_IDENTITY()从表 A 中获取每条插入记录的最新标识，并将其用于第二个表中的插入

sql = string.Format(@"INSERT INTO Tickets_Fields ([TableA_TicketId], [FieldName], [Key],[Value]) 
                      VALUES (@TableA_TicketId, @FieldName, @Key, @Value);");

using (cmd = new SqlCommand(sql, conn))
{
    foreach (var customField in ticket.CustomFields)
    {
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@TableA_TicketId", (object)TicketId ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@FieldName", (object)"CustomField" ?? DBNull.Value);
        ...
        cmd.ExecuteNonQuery();
    }
}

conn.Close();

请建议我是否可以通过任何方式提高这段代码的性能。或者他们有更好/更快的方法吗？

Answer 1

一些想法:

1. 在整个批量插入过程中保持同一个连接打开。一开始就打开它，然后只有在完成后才关闭它。

2. 不要在每次循环迭代期间重新创建 SqlCommand。在最开始创建一次，然后只更新参数的值:cmd.Parameters["@x"].Value = …;。

3. 您将通过插入单个记录的 foreach 循环插入第二个表 (B)。您可以考虑将其替换为单个 INSERT INTO TableB (x, y, z) SELECT x, y, z FROM @tvp，其中 @tvp 是 table-valued parameter 。本质上，这意味着您可以填充例如一个 DataTable，其中包含要插入到第二个表中的行，然后将该 DataTable 作为 @tvp 传递。从 SQL Server 2008 开始支持 TVP，IIRC。第一次设置其中一个需要稍微研究一下。

   (我不太确定上面的 INSERT 语句是否真的有效，或者 TVP 是否仅作为存储过程的参数(see e.g. this example)。)

4. 比#3 更进一步，将对表 A 和 B 的插入移动到数据库存储过程中。该 SP 将具有进入表 A 的值作为参数，以及具有进入表 B 的记录的表值参数。