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