CODEWITHSUNDEEP
c#sql.netsql-server
Run CMD
Run CMD

Reputation: 3035

How to insert many Child/parent items with a singe query

Suppose we have the following SQL Server table structures:

MainItem
........
ID (int)
Total (money)

SubItem
........
ID (int)
MainItemID (int)
Price (money)

We need to generate thousands of MainItems, with each MainItem having dozens of SubItems, and input this at once in the database. Currently our code looks something like this:

Dictionary<int, int> oldToNewIDs = new Dictionary<int, int>();
foreach(MainItem mainItem in GeneratedMainItems)
{
    int oldID = mainItem.ID;
    mainItem.Update();
    oldToNewIDs.Add(oldID, mainItem.ID);
}
foreach (SubItem subItem in GeneratedSubItems)
{
    fi.MainItemID = oldToNewIDs[fi.MainItemID];
    subItemsToInsert.Add(fi);
}

InsertAllSubItemsAtOnce(subItemsToInsert);

The code for inserting alle subitems at once looks like:

string sql = @"
INSERT INTO SubItems (MainItemID, Price) VALUES (1, 100)
INSERT INTO SubItems (MainItemID, Price) VALUES (1, 50)
INSERT INTO SubItems (MainItemID, Price) VALUES (2, 20)
...
";
UpdateDB(sql);

The problem we currently face, is that in one occasion, only half of the subitems were inserted. So we ended up with wrong totals and subitems. I don't exactly understand how this could happen, but my guess is that something went wrong in sql server?

What we want to achieve, is that we insert everyting (main and subitems) at once, and if something goes wrong, the insert is aborted, so we don't end up with invalid data. How can this be done?

Upvotes: 0

Views: 116

Answers (1)

Mathias F
Mathias F

Reputation: 15931

Put your workload into a transaction

using (var conn = new SqlConnection(connStr))
{
    conn.Open();
    var cmd = new SqlCommand(sql , conn, conn.BeginTransaction());

    try
    {
        cmd.ExecuteNonQuery();
        cmd.Transaction.Commit();
    }
    catch(System.Exception ex)
    {
        cmd.Transaction.Rollback();
        throw ex;
    }

    conn.Close();
}

Upvotes: 2

Related Questions