Return value of ExecuteNonQuery after rollback

Question

Assuming that we have a stored procedure that does like something this:

BEGIN TRANSACTION
    UPDATE sometable SET aField = 0 WHERE anotherField = 1;       
    UPDATE sometable SET aField = 1 WHERE anotherField = 2;
ROLLBACK TRANSACTION;

And from C# we have something like this:

using (var connection = new SqlConnection("connection string")) 
{
    connection.Open();
    var cmd = connection.CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "my_procedure";
    var res = cmd.ExecuteNonQuery();                
}

Why I'm not getting getting res == -1? I'm still getting the number of affected rows. When the documentation states "If a rollback occurs, the return value is also -1"

What I'm missing here?

Interestingly if you just run this code in SSMS, you get "1 row(s) affected" or similar. (Or 2, or whatever - but not 0 even though it's rolled back.) — Rawling, Feb 25 '13 at 16:27
Sounds like the function is summing the rowcount messages. You can try and turn them off add set nocount on to your procedure, but this might stop the rowcounts from being returned as well...Looks like either the documentation is wrong or a bug. — JoshBerke, Feb 25 '13 at 17:18
The documentation for @@ROWCOUNT doesn't mention ROLLBACK when it talks about resetting back to 0 http://technet.microsoft.com/en-us/library/ms187316.aspx — Peter Ritchie, Feb 25 '13 at 17:36
@JoshBerke yes its returning the number of affected rows. Not sure of what's happening under the hood, if @@ROWCOUNT is being used or some other tracking variable. Peter Ritchie, perhaps I should have used a better title. My problem is not on MSSQL side. That's fine. It's ExecuteNonQuery (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx) that's returning the number of affected rows when it should return -1 — coffeeyesplease, Feb 25 '13 at 17:47
Its not using @@rowcount, run it in SSMS and select @@rowcount after you rollback it will be 0 (change your rollback to a commit it will still be 0). I believe the ExecuteNonQuery is using the info messages. — JoshBerke, Feb 25 '13 at 18:05
Can you provide your entire SP, also as people here discussed/mentioned, the number that you're supposed to get from `ExecuteNonQuery` comes from the [message provided by sql](http://msdn.microsoft.com/en-us/library/ms189837.aspx) — YavgenyP, Feb 25 '13 at 18:30
I'm afraid I'm not at work anymore. But we created a test environment with the query above (adding CREATE PROCEDURE..., obviously) where there's no commit just a ROLLBACK since that's what's bugging us. The database behaves properly (rolling back) we just don't get the expected value on code side. And it's not because of SET NOCOUNT. We've tested that, as well. — coffeeyesplease, Feb 25 '13 at 18:46

Sacrilege · Answer 1 · 2013-02-27T18:04:11.310

It appears that the return value of ExecuteNonQuery is unaffected by a rollback even though the documentation clearly states that is does. Here are some possible workarounds.

1) Use ExecuteScalar

SP:

DECLARE @RowCount INT
DECLARE @Error INT

BEGIN TRAN

UPDATE Table1 SET Value1 = NULL

SELECT @RowCount = @@ROWCOUNT, @Error = @@ERROR

IF @Error <> 0 BEGIN
    ROLLBACK TRAN
    SELECT -1
END ELSE BEGIN
    COMMIT TRAN
    SELECT @RowCount
END

C#

using (SqlConnection dbConnection = new SqlConnection("Data Source=.;Initial Catalog=Database1;Integrated Security=True;MultipleActiveResultSets=True"))
{
    dbConnection.Open();

    using (SqlCommand command = dbConnection.CreateCommand())
    {
        command.CommandText = "QuickTest";
        command.CommandType = CommandType.StoredProcedure;

        rowsAffected = command.ExecuteScalar();
    }
}

2) Use a return/output parameter

SP: DECLARE @RowCount INT DECLARE @Error INT

BEGIN TRAN

UPDATE Table1 SET Value1 = NULL

SELECT @RowCount = @@ROWCOUNT, @Error = @@ERROR

IF @Error <> 0 BEGIN
    ROLLBACK TRAN
    RETURN -1
END ELSE BEGIN
    COMMIT TRAN
    RETURN @RowCount
END

C#

using (SqlConnection dbConnection = new SqlConnection("Data Source=.;Initial Catalog=Database1;Integrated Security=True;MultipleActiveResultSets=True"))
{
    dbConnection.Open();

    using (SqlCommand command = dbConnection.CreateCommand())
    {
        command.Parameters.Add(new SqlParameter() {Direction = ParameterDirection.ReturnValue });
        command.CommandText = "QuickTest";
        command.CommandType = CommandType.StoredProcedure;

        command.ExecuteNonQuery();
        rowsAffected = command.Parameters[0].Value;
    }
}

3) Move the rollback/commit logic into the code

This would give you the ability to determine if a rollback occurred and output a value of -1 when necessary. The transaction statement would need to removed from the sproc.

SP:

UPDATE Table1 SET Value1 = NULL

C#:

using (SqlConnection dbConnection = new SqlConnection("Data Source=.;Initial Catalog=Database1;Integrated Security=True;MultipleActiveResultSets=True"))
{
    dbConnection.Open();

    using (SqlTransaction tran = dbConnection.BeginTransaction())
    {
        using (SqlCommand command = dbConnection.CreateCommand())
        {
            command.Transaction = tran;

            try
            {
                command.Parameters.Add(new SqlParameter() {Direction = ParameterDirection.ReturnValue });
                command.CommandText = "QuickTest";
                command.CommandType = CommandType.StoredProcedure;

                rowsAffected = command.ExecuteNonQuery();
            }

            catch (Exception)
            {
                rowsAffected = -1;
                throw;
            }

            tran.Commit();
        }
    }
}

As noted previously, the @@ROWCOUNT value and the ExecuteNonQuery result are both affected by triggers.

One thing to note is that if there is a trigger - those rows will be added - so the OP needs to watch out for those too. — Mike Perrenoud, Feb 26 '13 at 14:58
Hi. Thanks but that's not what I'm looking for. I DO want the number of affected rows when the query goes okay. As I want -1 if not. By setting NO COUNT ON I will always get -1 as the result of ExecuteNonQuery — coffeeyesplease, Feb 26 '13 at 15:14
And that's not what the documentation states. It says "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1." — coffeeyesplease, Feb 26 '13 at 15:17
I know I can OUTPUT a value in order to continue my processing, but I just can't believe that such fundamental function/method has a bug or someone screw up the documentation. And I'd be more than happy for anyone to (please) prove me wrong. — coffeeyesplease, Feb 26 '13 at 15:21
Ok, that problem is different than what you expressed though. Your question was "why am I not getting -1 here". — Sacrilege, Feb 26 '13 at 15:35
@Sacrilege precisely. If you look at my stored procedure it will always rollback. "If a rollback occurs, the return value is also -1", meaning I should be getting -1. But no, in my example I get 2 when, technically, no rows were changed. — coffeeyesplease, Feb 26 '13 at 15:42
What logic do you use to determine whether a rollback should occur? — Sacrilege, Feb 26 '13 at 15:48
@Sacrilege, not following. What you mean? Are you talking about my real query? Listen I'd be more than happy to continue this discussion but in some other medium. Since this can escalate to a comments nightmare. If you're able to help me I'll comeback and lavish you in praise :-) — coffeeyesplease, Feb 26 '13 at 16:06
Let's continue in chat then. [chat room](http://chat.stackoverflow.com/rooms/25168/return-value-of-executenonquery-after-rollback) — Sacrilege, Feb 26 '13 at 17:07
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/25213/discussion-between-coffeeyesplease-and-sacrilege) — coffeeyesplease, Feb 27 '13 at 10:25

Return value of ExecuteNonQuery after rollback

1 Answers1

Linked