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.

why would you be using a transaction for a select statement only? Is this just an example of your code? Do you actually have some data manipulation peppered in here? There is no reason to transact only a select statement. — Kritner, Jun 17 '15 at 15:44
Fyi, if you use the `using`-statement you don't need to call `locationConnection.Dispose` or `locationConnection.Close` explicitly. — Tim Schmelter, Jun 17 '15 at 15:45
I'm selecting a result set before I make the changes so that I can log what I change. — ReddShepherd, Jun 17 '15 at 15:45
You don't need a separate select and transaction for that. You can use an OUTPUT clause. Also, that `SqlTransaction` should be in a `using` block, and so should the `SqlCommand`. And why do you think you have to set it to `null` first? — John Saunders, Jun 17 '15 at 15:50
I'm more focused on the issue, but I moved everything to have the using statements you mentioned and the issue persists. — ReddShepherd, Jun 17 '15 at 16:01
@Kritner selects need to be transacted if you require certain consistency guarantees. — usr, Jun 18 '15 at 13:52
@usr true... but that would require an isolation level aside from the default wouldn't it? From the original question (at the time of my comment) there was only a single select statement and no changes to isolation level in the transaction. I *think* the comment still applies in that specific scenario, but if not sorry for the misinformation. — Kritner, Jun 18 '15 at 14:01
OK, a single statement is always equivalent with and without explicit transaction. — usr, Jun 18 '15 at 14:02

score 1 · Answer 1 · answered Jun 17 '15 at 16:59

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.

score 0 · Answer 2 · answered Jun 17 '15 at 15:58

0

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

answered Jun 17 '15 at 15:58

Rahul

76,197
13
71
125

If you aren't able to use a transaction with a SqlDataReader, then I would accept that as the answer. I can just split the commands up in C# so that I select before I even worry about a transaction. I would just prefer to write it this way because it is what makes sense the most based on the rest of the code source. – ReddShepherd Jun 17 '15 at 16:06

Using SqlTransaction with SqlDataReader

2 Answers2