SQL transaction always caught

Question

I have 3 delete queries executed by my sql command. The last of the three queries can only execute if there is no reference to it in other tables. It fails to execute if there is a reference. So, if the last delete fails, I want to rollback to not lose the data that would be deleted in the previous 2 commands. This is why I need to use a SqlTransaction.

The following is what I was able to come up with. It in fact prevents the loss of any data, however, I am unable to delete anything and am always getting the popup (meaning every time it is executed, it is caught).

protected void editDelete(object sender, DirectEventArgs e)
{
    SqlConnection MyConnection = new SqlConnection();
    MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

    MyConnection.Open();

    // Start a local transaction.
    SqlTransaction sqlTran = MyConnection.BeginTransaction();

    try
    {
        SqlCommand MyCommand = new SqlCommand();
        MyCommand.CommandText = /* 3 queries removed to shorten code */;
        MyCommand.CommandType = CommandType.Text;
        MyCommand.Connection = MyConnection;

        SqlParameter p1 = new SqlParameter("@id", this.accountidEdit.Value);
        MyCommand.Parameters.Add(p1);

        MyCommand.ExecuteNonQuery();
        MyConnection.Close();


        /* reload and notification calls removed to shorten code */
    }
    catch (Exception)
    {
        sqlTran.Rollback();
        X.Msg.Alert("Error", "Error.").Show();
    }

}

In addition, I tried to find the problem by using:

catch (Exception sqlexception)
    {
        Console.WriteLine(sqlexception.Message);
        sqlTran.Rollback();
        X.Msg.Alert("Error", "Error.").Show();
    }

but nothing ever wrote to the console.. weird?

Is this the proper way to execute a SqlTransaction? I followed a guide from the microsoft site to do this. Why is it always being caught?

Thank you!

You need to commit the SqlTransaction before you close the connection in the happy `try` block. The exception (or InnerException) should mention this. — StuartLC, Oct 16 '14 at 15:20
Additionally I think you need to set the `SqlCommand.Transaction` to `sqlTran`. — juharr, Oct 16 '14 at 15:22
@StuartLC Great observation! I did not commit. However, placing `sqlTran.Commit();` on the line directly after `MyCommand.ExecuteNonQuery();` does not fix the issue — starvator, Oct 16 '14 at 15:22
@juharr yes! That was the missing piece of the puzzle! Thank you! — starvator, Oct 16 '14 at 15:24

score 2 · Accepted Answer · edited Oct 16 '14 at 15:41

2

You have to commit the transaction sqlTran.Commit();.

Also, set MyCommand.Transaction = sqlTran; as @juharr pointed out

edited Oct 16 '14 at 15:41

starvator

989
1
11
26

answered Oct 16 '14 at 15:17

brz

5,926
1
18
18

Great observation! I did not commit. However, placing `sqlTran.Commit();` on the line directly after `MyCommand.ExecuteNonQuery();` does not fix the issue – starvator Oct 16 '14 at 15:20
The remaining issue, as @juharr pointed out, was that I also need to set `MyCommand.Transaction = sqlTran;` – starvator Oct 16 '14 at 15:25

score 1 · Answer 2 · answered Oct 16 '14 at 15:33

The problems are a missing transaction commit and setting the transaction for the command. Here's how I would do it with using statements for the connect, transaction and command that will make sure the transaction is rolled back and the connection is closed in the case of an exception without having to explicitly call Rollback or Close. The Commit has to be called after executing the command. By creating the command from the connection you don't have to set the connection, but I think you still have to set the transaction. This can be even further simlified if you don't need the try-catch that shows the pop-up box.

protected void editDelete(object sender, DirectEventArgs e)
{
    using (
        var MyConnection =
            new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
    {
        MyConnection.Open();

        // Start a local transaction.
        using (var sqlTran = MyConnection.BeginTransaction())
        {
            try
            {
                using(var MyCommand = MyConnection.CreateCommand())
                {
                    MyCommand.CommandText = /* 3 queries removed to shorten code */;
                    MyCommand.CommandType = CommandType.Text;
                    MyCommand.Transaction = sqlTran;
                    SqlParameter p1 = new SqlParameter("@id", this.accountidEdit.Value);
                    MyCommand.Parameters.Add(p1);

                    MyCommand.ExecuteNonQuery();
                    sqlTran.Commit();
                }


                /* reload and notification calls removed to shorten code */
            }
            catch (Exception)
            {
                X.Msg.Alert("Error", "Error.").Show();
            }
        }
    }
}

SQL transaction always caught

2 Answers2