rollback sql transactions if any failed in c#

Question

I have a windows forms application that executes some sql scripts in a database at the click of a button.These sql scripts do user creation, and giving some permissions like data execute, data read/write etc.. Here is my sample c# code for doing this:

script1 = "CREATE USER " + username + " FROM LOGIN " + username;
script2 = @"CREATE ROLE [db_execute] AUTHORIZATION [dbo]
                        GRANT EXECUTE TO [db_execute]";

script3 = @"DECLARE @rolename varchar(max)
                    SET @rolename ='{0}'
                    EXEC sp_addrolemember N'db_execute',@rolename
                    EXEC sp_addrolemember N'db_datareader', @rolename
                    EXEC sp_addrolemember N'db_datawriter', @rolename";

And executing this in the database like:

SqlCmd.CommandText = script1;
SqlCmd.Connection = oConnection;
var ans = SqlCmd.ExecuteNonQuery();

if (!ans.Equals(0))
{
    //some task
} else
{
    //messagebox
}

SqlCmd.CommandText = script2;
SqlCmd.Connection = oConnection;
var answer = SqlCmd.ExecuteNonQuery();

if (!answer.Equals(0))
{
    //some task
} else
{
    //messagebox
}

SqlCmd.CommandText = script3;
SqlCmd.Connection = oConnection;
var answ = SqlCmd.ExecuteNonQuery();

if (!answ.Equals(0))
{
    //some task
} else
{
    //messagebox
}

I am executing all these scripts at a time by the press of a button,so in my understanding I am doing this as a single transaction. What I want to do is that, if in case any of these scripts fail in between I should be able to rollback fully without doing any change.

For giving you a better picture,if in case the execution of script3 got failed for some reason, it should automatically rollback whatever the script1 and script2 done in the database.

How can we do this? I googled it and found some irrelevant posts for this.

Any help or ideas would be really appreciated..

Nadeem_MK · Answer 1 · 2013-09-24T12:10:57.503

2

Use the SqlConnection and SqlTransaction objects. Try this;

   {       
     // declarations

     try
     {
        // open connection
        trans = conn.BeginTransaction();
        cmd.Transaction = trans;   // Includes this cmd as part of the trans

        SqlCmd.CommandText = script1;
        SqlCmd.Connection = oConnection;
        var ans = SqlCmd.ExecuteNonQuery();


        if (!answ.Equals(0))
        {
           //some task
        } else
        {
           //messagebox
        }

       // Your other queries

       trans.Commit(); // commit transaction

     }
     catch (Exception e){
        trans.Rollback();
        throw e;
     }
     finally{
        conn.Close();
   }

edited Sep 24 '13 at 12:10

answered Sep 24 '13 at 12:04

Nadeem_MK

7,533
7
50
61

1

`throw` would be better than `throw e` here – Marc Gravell Sep 24 '13 at 12:08
Your "throw e" should be "throw" so as not to lose the stack trace. But in any case I'd omit the catch block altogether, and wrap the SqlTransaction in a using block. When SqlTransaction.Dispose is called, the transaction will be automatically be rolled back if it hasn't been committed - no need for an explicit rollback. – Joe Sep 24 '13 at 12:10
@Joe:can you give me a sample for what you said? – vysakh Sep 24 '13 at 12:11
Yeah you can remove the exception e and throw everything. – Nadeem_MK Sep 24 '13 at 12:12
throws the same error:CREATE LOGIN statement not allowed within multi-statement transaction – vysakh Sep 25 '13 at 05:42
@Nadeem_MK In store procedure i have setup to do commit and Rollback. Should i still write Commit and Rollback logic in C# when i call that SPROC? – dev Jun 17 '15 at 17:33
@Mvcdev You either do it in C# upon calling the stored proc or in the stored proc itself. Both will do the same thing, so why do it twice? – Nadeem_MK Jun 18 '15 at 04:51

score 1 · Answer 2 · answered Sep 24 '13 at 12:03

Depending on your database system, only DML (data manipulation, SELECT/INSERT/UPDATE/DELETE) can be transactional and therefor rolled back. DDL (data definition, like ALTER or CREATE) cannot be part of a transaction. In fact, depending on DBMS, those statements will implicitely commit all prior DML statements on execution.

If you need transactions (because your DBMS is different or you actually have CRUD statements), then you need to call BeginTransaction on your SqlConnection and use the resulting object to either commit or roll back.

Steven Wood · Answer 3 · 2013-09-24T12:18:59.503

1

I use System.Transactions.TransactionScope, if you encase it in a using statement, put your code inside then use scope.complete.

edited Sep 24 '13 at 12:18

answered Sep 24 '13 at 12:04

Steven Wood

2,675
3
26
51

1

`TransactionScope` pre-dates EF by several versions - it was 2.0; EF was 3.5 SP 1. `TransactionScope` would be fine here - the comment about EF is a bit misleading. – Marc Gravell Sep 24 '13 at 12:15
Ok I wasnt sure if it was an EF thing, thanks for telling me, I shall update my answer – Steven Wood Sep 24 '13 at 12:18
using(var scope = new System.Transactions.TransactionScope()){ cmd.ExecuteNonQuery(); scope.Complete();} This is assuming you already have a command object set up. see http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx for more info. I use this a lot for unit testing update database methods as you can test that it works without actually committing. The key statement is the Scope.Complete(); statement which actually commits the action to the database. – Steven Wood Sep 24 '13 at 13:12
I tried your's by trying to create a user which already exists.But I got an exception like the user already exists while executing the statement cmd.executenonquery();Means the control didnt reach scope.complete().So, then what it will do?will it rollback? – vysakh Sep 24 '13 at 13:20
In entity framework, the model on the application side is updated first, checked for validation errors, and then written to the database. I am not 100% sure on ADO but I think that it would roll back the transaction. The best way to find out is to mess about with the different options. Also look at the SQLTransaction within system.data as another option – Steven Wood Sep 24 '13 at 14:21

score 0 · Answer 4 · answered Sep 24 '13 at 12:06

0

Use SqlTransaction Class as explained in this MSDN reference

answered Sep 24 '13 at 12:06

rajeemcariazo

2,476
5
36
62

tried yours..but getting exception while committing: Commit Failure Message: CREATE LOGIN statement not allowed within multi-statement transaction. – vysakh Sep 24 '13 at 12:54
well, some limitations when using transactions – rajeemcariazo Sep 24 '13 at 15:53

score 0 · Answer 5 · answered Sep 24 '13 at 12:24

I would recommend taking care of the transaction inside SQL and return proper message to the user. Reason is so you have two separate layers Data Access Layer (DAL) and UI.

You can read more about DAL HERE

Transactions have the following four standard properties, usually referred to by the acronym ACID:

Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.

Consistency: ensures that the database properly changes states upon a successfully committed transaction.

Isolation: enables transactions to operate independently of and transparent to each other.

Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

rollback sql transactions if any failed in c#

5 Answers5

Linked