Using SqlTransaction with SqlDataReader

Question

There are many people who talk about this on the Internet, but this does not seem to work. This is the exception 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 some light on why this is happening? If I move the commit inside the SqlDataReader area, I get an exception that the datareader should close.

EDIT: I answered my question and try not to forget to come, accept it in a couple of days when they let me.

Answer 1

, SqlException DELETE ( ). sqlconnection , , SELECT . Sql, . - , , .

Answer 2

SQL , . #. .

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