TransactionScope appears not to be working

Question

I'm trying to make sure that both my insert and delete below work completely or not at all. I have my connection object outside of my transaction scope which I believe is correct by not 100% sure.

I do know that this code is not working as I intent. After the first part (the insert runs) and then I abort by terminating on a break point, the rows are indeed inserted even though I never called scope.complete.

Please point out the flaw in my thinking and logic here.

                sqlConnection.Open();

                int numFound = 1;
                int max = 99;
                int iteration = 0;
                while (iteration < max && numFound > 0)
                {
                    iteration++;
                    var ids = new List<int>();
                    using (var sqlCommand0 = new SqlCommand(sql0, sqlConnection))
                    {
                        using (SqlDataReader reader1 = sqlCommand0.ExecuteReader())
                        {
                            while (reader1.Read())
                            {
                                ids.Add(reader1.GetInt32(0));
                            }
                        }
                    }
                    numFound = ids.Count;
                    if (numFound > 0)
                    {
                        using (var scope = new TransactionScope())
                        {
                            string whereClause = $"WHERE Id IN ({string.Join(",", ids)})";

                            string sql1 = string.Format(sqlTemplate1, whereClause);
                            using (var sqlCommand1 = new SqlCommand(sql1, sqlConnection))
                            {
                                sqlCommand1.ExecuteNonQuery();
                            }

                            // BREAK POINT HERE - ABORTED PROGRAM AND sql1 had been committed.

                            var sql2 = "DELETE FROM SendGridEventRaw " + whereClause;
                            using (var sqlCommand2 = new SqlCommand(sql2, sqlConnection))
                            {
                                sqlCommand2.ExecuteNonQuery();
                            }
                            scope.Complete();
                            total += numFound;
                            Console.WriteLine("deleted: " + whereClause);
                        }
                    }
                }
            }

Answer 1

Just from what I am seeing and from what I am assuming is what you intend to happen is this: If your first query gets some records, then the next query executes, hence the statement:

if (numFound > 0)

If that is the case, and where you put your breakpoint is true, of course the insert statement will fire. Reason is:

 using (var sqlCommand1 = new SqlCommand(sql1, sqlConnection))
                            {
                                sqlCommand1.ExecuteNonQuery();
                            }

is within that if statement. You're saying "if there are any rows, execute the insert query."

If you're trying to actually get the scope object to do the query, then you're going to have to have all of the query construction happening within the object and then having scope.complete() doing the execution.

For example:

//In TransactionScope class

public string Complete(var ids, int numFound, SqlConnection sqlConnection, string sqlTemplate1)
{
    string whereClause = $"WHERE Id IN ({string.Join(",", ids)})";

                            string sql1 = string.Format(sqlTemplate1, whereClause);
                            using (var sqlCommand1 = new SqlCommand(sql1, sqlConnection))
                            {
                                sqlCommand1.ExecuteNonQuery();
                            }

                            var sql2 = "DELETE FROM SendGridEventRaw " + whereClause;
                            using (var sqlCommand2 = new SqlCommand(sql2, sqlConnection))
                            {
                                sqlCommand2.ExecuteNonQuery();
                            }
                            return whereClause;
}


//in your Main class

if (num > 0)
{
    string whereClause = scope.Complete(ids, numFound, sqlConnection, sqlTemplate1);
    Console.WriteLine("deleted" + whereClause"." );
}

I am of course just going off of the assumptions I stated above. If I am incorrect, please let me know.

Hope it helps.

Answer 2

I think it's because you open your connection before starting your transaction. You could try to fix your issue by first starting your transaction and then opening your connection.