How do i use SqlTransaction across multipe methods

Question

Let's assume we have an Object A which can be deleted an Object B which hold a forendkey from A

If you want to deleted A, you have to delete the forendkey from B first and then you can delete A but if something goes wrong it should be rolled back but i also want to use the delete forendkey from B independent but at the moment i don't know how to achieve this

my current idea :

    public void DeleteA(Object a)
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.open();

            using (SqlTransaction tr = con.BeginTransaction())
            {
                try
                {
                    DeleteAfromAllB(a, con, tr);

                    using (SqlCommand cmd = new SqlCommand("STP_A_Delete", con))
                    {
                        cmd.Transaction = tr;

                        // some parameters
                        // some sort of Execute
                        // e.g.: cmd.ExecuteNonQuery();
                    }
                    tr.Commit();
                }
                catch (SqlException ex)
                {
                    //ExceptionHandling
                }
            }
        }
    }

    private void DeleteAfromAllB(Object a, SqlConnection con, SqlTransaction tr)
    {
        try
        {
            using (SqlCommand cmd = new SqlCommand("STP_B_Delete_Referenc_To_A", con))
            {
                cmd.Transaction = tr;

                // some parameters
                // some sort of Execute
                // e.g.: cmd.ExecuteNonQuery();
            }
        }
        catch (SqlException ex)
        {
            //ExceptionHandling
        }
    }       

    public void DeleteAfromAllB(Object a)
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.open();

            using (SqlTransaction tr = con.BeginTransaction())
            {
                DeleteAfromAllB(a,con,tr);

                tr.Commit();
            }
        }
    }

but like you can see this is pretty ugly

Shiv · Accepted Answer · 2019-08-23T02:21:37.333

The call

public void DeleteAfromAllB(Object a)

does not need to pass the SqlConnection as you can reference from tr.Connection. So you just need the SqlTransaction as parameter. So for your original question, yes I think passing in the SqlTransaction is the way to go. Personally I prefer this way because you can easily trace the call stack / scope of the transaction (i.e. where the transaction started/finished).

Another alternative is to use a TransactionScope.

E.g.

private void DeleteAfromAllB(Object a)
{
    using (var con = new SqlConnection())
    {
        con.open();
        using (var cmd = new SqlCommand("STP_B_Delete_Referenc_To_A", con))
        {
            // some parameters
            // some sort of Execute
            // e.g.: cmd.ExecuteNonQuery();
        }
    }
}

public void DeleteAfromAllB_TopLevel(Object a)
{
    using (var scope = new TransactionScope())
    {
        try
        {
            DeleteAfromAllB(a);

            // The Complete method commits the transaction. If an exception has been thrown, 
            // Complete is not  called and the transaction is rolled back.
            scope.Complete();
        }
        catch (Exception)
        {
            //ExceptionHandling
        }
    }
}

Actually, they way you have it written, if an exception is thrown in DeleteAFromAllB, it doesn't get rethrown, and scope.Complete gets called in the TopLevel call... I believe the exception handling should be done in the TopLevel call. — John Kroetch, Aug 21 '19 at 16:29
Hi John yes you are correct the handling in the child should be removed and moved to parent. Fixed thanks. Looked like I had a bad copy pasta in there... the try and catch weren't lined up! — Shiv, Aug 23 '19 at 02:20

How do i use SqlTransaction across multipe methods

1 Answers1