从C＃列表批量插入SQL Server到具有外键约束的多个表中 [英] Bulk insert from C# list into SQL Server into multiple tables with foreign key constraints

问题描述

我对这个问题一无所知，将不胜感激：

我有两个表，一个是主数据表（表A ），另一个表（表B ）具有外键关系，该外键关系具有多个条目（具体为18），用于其中的一个条目表A 。

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

我当前正在使用以下模式，但是在表A 14分钟 $ c>和表B 中相应的18 * 100行。

 使用（SqlConnection conn =新的SqlConnection（conStr））
 {
 foreach（票证中的可变票证）
 {
 sql = string.Format（@ INSERT INTO dbo.Tickets（ [ColumnA]，[ColumnB]，... + @）
 VALUES（@ ColumnA，@ ColumnB，@ ColumnC，@ ColumnD，...。+ 
 @ SELECT SCOPE_IDENTITY（）; ）; 
 
使用（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]）
值（@TableA_TicketId，@FieldName，@ Key，@值）;）; 
 
使用（cmd = new SqlCommand（sql，conn））
 {
 foreach（ticket.CustomFields中的var customField）
 {
 cmd.Parameters 。明确（）; 
 cmd.Parameters.AddWithValue（ @ TableA_TicketId，（object）TicketId ?? DBNull.Value）; 
 cmd.Parameters.AddWithValue（ @ FieldName，（对象） CustomField ?? DBNull.Value）; 
 ... 
 cmd.ExecuteNonQuery（）; 
} 
} 
 
 conn.Close（）; 
  

请建议我是否可以通过任何方式提高此代码的性能。还是他们的方法更好/更快？

解决方案

一些想法：

1. 在整个批处理插入期间保持相同的连接打开。

   
   



2. 不要重新创建 SqlCommand，请在开始时将其打开。

   

s在每个循环迭代期间。首先创建一次，然后只更新参数的值： cmd.Parameters [ @ x]。Value =…; 。




3. 您正在通过 foreach 循环（插入单个记录）将其插入第二个表（B）。您可以考虑用单个插入到TableB中来替换它（x，y，z）SELECT x，y，z FROM @tvp ，其中 @ tvp 是表值参数 。本质上，这意味着您可以填充例如 DataTable ，其中要插入第二个表中的行，然后将该 DataTable 传递为 @tvp 。从SQL Server 2008开始，IIRC支持TVP。设置其中一个设置需要进行一些首次研究。

   
   

   （我不确定上面的 INSERT 语句是否会真正起作用，或者TVP是否仅充当存储过程的参数（请参见例如本示例）。

   $ b＃b



4. 要比＃3进一步，将插入到表A和B中的内容移动到DB存储过程中。该SP将具有进入表A的值作为参数，以及具有进入表B的记录的表值参数。

   
   




I am completely clueless with this problem, Any help would be highly appreciated:

I have two tables, one is the master data table (Table A), the other table (Table B) has a foreign key relationship with multiple entries (to be specific 18) for one entry in Table A.

I am getting the data in a list and wish to insert it in SQL Server database.

I am currently using the below pattern but is taking 14 minutes for inserting 100 rows in Table A and corresponding 18*100 rows in Table B.

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

I use SCOPE_IDENTITY() to get the latest identity from table A for each record inserted and use it for insertion in second table

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();

Please suggest if I can improve the performance of this code by any means. Or is their any better/faster way of doing it?

解决方案

Some ideas:

1. Keep the same connection open during the whole batch insertion. Open it at the beginning, then only close it when you're done.

2. Don't recreate the SqlCommands during each loop iteration. Create them once at the very beginning, then only update the parameters' values: cmd.Parameters["@x"].Value = …;.

3. You're inserting into the 2nd table (B) via a foreach loop that inserts single records. You could look into replacing this with a single INSERT INTO TableB (x, y, z) SELECT x, y, z FROM @tvp, where @tvp is a table-valued parameter. Essentially, this means that you can populate e.g. a DataTable with rows you want to insert into the 2nd table, then pass that DataTable over as @tvp. TVPs are supported from SQL Server 2008 onwards, IIRC. Setting one of these up takes a little study the first time.

   (I'm not quite certain if the above INSERT statement would actually work, or whether TVPs only work as parameters to stored procedures (see e.g. this example).)

4. Going further than #3, move the insertions into tables A and B into a DB stored procedure. This SP would have as parameters the values that go into table A, as well as a table-valued parameter with the records that go into table B.

这篇关于从C＃列表批量插入SQL Server到具有外键约束的多个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！