CODEWITHSUNDEEP
c#asp.netsql-server
Lord Relix
Lord Relix

Reputation: 942

Rolling Back a SQL Server query

I found a few links regarding rolling back a SQL Server query but have had problems implementing it. Since the information I am inserting and updating needs to always be correct I need to make sure this is working beforehand. Now, I already have a try/catch block, and I know the rollback goes into the catch block. For example, this is my code:

using (SqlConnection conn5 = new SqlConnection(connString)) 
{
    try 
    {
        string query = "INSERT QUERY";
        SqlCommand cmd = new SqlCommand(query, conn5);
        // PARAMETERS

        conn5.open();
        cmd.ExecuteNonQuery();
    }
    catch 
    { 
        cmd.Rollback();
    }
}

I tried cmd.RollBack() but it just blew in my face.

Upvotes: 1

Views: 370

Answers (1)

Anders Abel
Anders Abel

Reputation: 69270

You open a transaction scope. It will automatically perform a rollback unless the Complete() method is called.

using (var tScope = new TransactionScope())
using (SqlConnection conn5 = new SqlConnection(connString)) 
{
  string query = "INSERT QUERY";
  SqlCommand cmd = new SqlCommand(query, conn5);
  PARAMETERS

  conn5.open();
  cmd.ExecuteNonQuery();

  // If an exception is thrown, the call to Complete() will never be reached and the
  // changes will be rolled back.
  tScope.Complete();
}

Upvotes: 5

Related Questions