How to continue sequence of INSERTs if a single INSERT fails

I am trying to insert data from a file into an SQL table, and my program has a validation mode in which you can run all the inserts without committing to the database. I use transactions in ADO.NET.

I would like to be able to insert all the rows except for those that cause an error: e.g. wrong data type - and after all the inserts commit or rollback the entire transaction. However, if a single insert fails, the connection in the transaction object becomes null and any inserts after that no longer use the transaction.

For example, in the code below, the 23a causes a failed INSERT. The table should be empty after the code is executed due to the Rollback() in the finally block, but instead it has a 8 and 678 in it. If I change the Rollback() to a Commit() in the finally, then I get an error that the transaction is no longer usable. The desired result would be to have 5, 8, and 678 in the table.

Any idea how to make this work?

Code:

---

string[] valuesToInsert = { "5", "23a", "8", "678" };

            try
            {

                conn.Open();
                tran = conn.BeginTransaction();

                foreach (string item in valuesToInsert)
                {
                    try
                    {
                        cmd = new SqlCommand(String.Format("insert into MyTable(IntColumn) values ('{0}')", item), conn, tran);
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception x)
                    {
                        MessageBox.Show("INSERT failed: " + x.Message);
                    }
                }

            }
            catch (Exception)
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
            }
            finally
            {
                if (tran != null)
                {
                    tran.Rollback();
                }
            }

---

Why are you rolling back in your finally block?

The Finally block ALWAYS runs, exception or not. Or is it just part of your testing?

Anyways... open your connection and start the transaction outside of the try block. If you look at the MSDN example, that's how they did it...

-tg

TechGnome - the code in the finally was part of my testing

What I need is something like nested transactions, e.g.

1. Start an outer transaction for the entire loop of inserts
2. Start an inner transaction for each insert - if it fails due to a data error, only roll back the inner transaction, but not the outer
3. At the end of the loop, if "Validate" mode was selected, return table to original state before the loop. If "Commit" mode was selected, only the rows that had good data should have been inserted.

However, if I try to use two transactions I get an error, "Parallel transactions are not supported"

You don't need an inner transaction... only the outer one. All you should need to do is trap the error. Attempt the insert, if it succeeds, move on to the next. If it doesn't, flag it, tag it, log it, and then move on to the next. There isn't anything to roll back... it's a simple insert it either happens or it doesn't. At the end of the loop, commit the transaction... actually, I'm not even sure you need a transaction in the first place. Transactions are for "all or nothing" scenarios... this isn't one of those cases.

-tg

TechGnome,

The problem is that SQL Server is internally rolling back the entire transaction if a single INSERT fails with the wrong data type. When I try to do a Commit() or Rollback() on the entire sequence, the C# code at the end of the sequence, it gives an error that the transaction no longer exists.

In "Validation mode" the inserted rows should be displayed as a "dirty read" and loaded into my DataGrid control, and then it should be immediately rolled back.

In "Commit mode" the inserted rows should be displayed and committed to the database.

I have also noticed that a string value that is too long will generate a "String or binary data would be truncated error" on the server, but in this case the SQL Server does NOT internally roll it back and I can continue the sequence in the C# code.

The SQL Server errors for bad data type vs. string overflow are both severity level 16, so I don't undrstand why the server would be internally rolling back the wrong data type, but not the string overflow.

Thanks in advance

Because it can handle the string overflow... it truncates it. It even tells you that's what's going on. Bad data types it doesn't necessarily know how to deal with it.

I still don't see why you need (or think you need) transactions at all.

Dump the data into a staging table that is capable of handling the data, even if it's not accurate. When possible, use the correct data type if you can. When you want to commit it, take it from the staging table and insert it into it's final table. That's typically how it's done.

Or... create a properly type specific datatable that mirrors your table... dump the data into that and then to commit it, dump the data into the final table.

What's happening is that your transaction is turning into a distributed transaction... it's getting passed along your connection to SQL server, so when it encounters an error, it sees that there is a transaction and does an implicit rollback... this takes everything out.

Again though, I'll re-iterate that there isn't a need for the transactions in the first place, especially for the kind of result you're aiming for.

-tg