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 · Accepted Answer · 2013-09-24 12:10:57Z

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, 2013 at 12:10

answered Sep 24, 2013 at 12:04

Nadeem_MK

7,7217 gold badges53 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Marc Gravell Over a year ago

throw would be better than throw e here

to StackOverflow Over a year ago

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.

vysakh Over a year ago

@Joe:can you give me a sample for what you said?

Nadeem_MK Over a year ago

Yeah you can remove the exception e and throw everything.

vysakh Over a year ago

throws the same error:CREATE LOGIN statement not allowed within multi-statement transaction

|

nvoigt · Accepted Answer · 2013-09-24 12:03:20Z

1

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.

answered Sep 24, 2013 at 12:03

nvoigt

77.8k28 gold badges103 silver badges156 bronze badges

Comments

Steven Wood · Accepted Answer · 2013-09-24 12:18:59Z

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, 2013 at 12:18

answered Sep 24, 2013 at 12:04

Steven Wood

2,8053 gold badges31 silver badges52 bronze badges

5 Comments

Marc Gravell Over a year ago

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.

Steven Wood Over a year ago

Ok I wasnt sure if it was an EF thing, thanks for telling me, I shall update my answer

Steven Wood Over a year ago

using(var scope = new System.Transactions.TransactionScope()){ cmd.ExecuteNonQuery(); scope.Complete();} This is assuming you already have a command object set up. see msdn.microsoft.com/en-us/library/… 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.

vysakh Over a year ago

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?

Steven Wood Over a year ago

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

rajeemcariazo · Accepted Answer · 2013-09-24 12:06:46Z

0

Use SqlTransaction Class as explained in this MSDN reference

answered Sep 24, 2013 at 12:06

rajeemcariazo

2,5445 gold badges39 silver badges63 bronze badges

2 Comments

vysakh Over a year ago

tried yours..but getting exception while committing: Commit Failure Message: CREATE LOGIN statement not allowed within multi-statement transaction.

rajeemcariazo Over a year ago

well, some limitations when using transactions

Lucky Lefty · Accepted Answer · 2013-09-24 12:24:32Z

0

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.

2 Comments

vysakh Over a year ago

so, what you suggest in my case? how can I achieve what I said?

Lucky Lefty Over a year ago

Db doesn't matter, I would go with pl sql procedure and put rollback statements inside procedure.

Collectives™ on Stack Overflow

rollback sql transactions if any failed in c#

5 Answers 5

7 Comments

Comments

5 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

Comments

5 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related