Using SqlTransaction with SqlDataReader

Question

There are plenty of people talking about it online, but this just doesn't seem to work. This is the exception that I get:

 This SqlTransaction has completed; it is no longer usable.

Here is the code

 using (SqlConnection locationConnection = new SqlConnection(connectionString))
        {
            locationConnection.Open();
            SqlTransaction transaction = locationConnection.BeginTransaction();
            SqlCommand cmd = new SqlCommand("
 Select stuff from table A
 Insert stuff into table B
 Delete stuff from table A", locationConnection, transaction); 

            using(SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            //Doesn't matter
                        }                            
                    }

            //Exception happens here
            transaction.Commit();       
        }

Can anyone shed light on why this is happening? If I move the commit inside the scope of SqlDataReader I get the exception that the datareader needs to close first.

EDIT: I've answered my question and will try to remember to come accept it in a couple days when I'm allowed to.

Answer 1

The problem was that I was getting a SqlException with my DELETE statement (foreign key issue). This was closing the sqlconnection and ending the transaction, but it was not throwing an exception in my code because the SELECT statement was working fine. I have resolved the Sql issues and the code works fine. If anyone else runs into this, they should be able to do it in the same way that I am.

Answer 2

If those SQL's are individual in nature then you can wrap them in a stored procedure and use transaction inside procedure. Then call the procedure in your C# code rather. An alternative solution.

CREATE PROCEDURE usp_TransTest
AS
BEGIN

DECLARE @err INT

BEGIN TRAN
    Insert stuff into table B

    SELECT @err = @@ERROR
    IF (@err <> 0) GOTO ERR

    Delete stuff from table A

    SELECT @err = @@ERROR
    IF (@err <> 0) GOTO ERR
COMMIT TRAN

ERR:
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END