CODEWITHSUNDEEP
c#asp.netsql-servertransactionsfinance
tomsv
tomsv

Reputation: 7277

Enforce transactions for write operations in SQL Server

How do I ensure and enforce that all write operations to my MS SQL Server DB are transactions-based?

The reason I want to do this is that the DB contains financial account data and operations that fail halfway through some set of changes should not be able to mess up the database by mistake or by crashing.

The application is written in ASP.NET C#.

Thanks.

Edit: There is a DAL but it does not (yet) require transactions for changes. We wrote the DAL ourselves. We did not use ORM.

I know how to perform one transaction from ASP.NET.

I want to ensure that all changes are made in transaction form so that if the application throws some exception in the middle, there will be no change to the database.

Upvotes: 0

Views: 602

Answers (3)

UserControl
UserControl

Reputation: 15179

Any SQL-statement is atomic by default, but often you need to perform two or more of them and be sure the whole set is atomic. So you need to manually start and commit a transaction. If you use ADO.NET in your application the code can look like this:

using (SqlConnection db = new SqlConnection("connectionstring"))
{
      db.Open();
      SqlTransaction transaction = db.BeginTransaction();
      try 
      {
         new SqlCommand("update statement", db, transaction).ExecuteNonQuery();
         new SqlCommand("another statement", db, transaction).ExecuteNonQuery();
         // additional operations here
         transaction.Commit();
      } 
      catch (SqlException sqlError) 
      {
         transaction.Rollback();
      }
}

Upvotes: 2

ceth
ceth

Reputation: 45325

All operations in MS SQL are transactions-based. But trasactions can be explicit and implicit.

Upvotes: 0

Mark PM
Mark PM

Reputation: 2919

You might want to look at setting SQL Server to use implicit transactions: http://msdn.microsoft.com/en-us/library/ms188317.aspx

Upvotes: 1

Related Questions