How can I implement the "transaction, roll back" coding ?

Question

I have a N-Tier C# desktop application and the application runs "ok".  However,  the current design issue comes with some transaction issues,
and I am wondering how I can implement the "transaction, roll back...etc" coding.

=== UI Layer ===
MyContainerForm.cs
Item1UserControl.cs
Item2UserControl.cs
Item3UserControl.cs

=== DAL Layer === (used for sql statement)
Item1DAL.cs
Item2DAL.cs
Item3DAL.cs

=== Object Layer === (used for GET or SET property)
Item1OL.cs
Item2OL.cs
Item3OL.cs

=== Database Tables ===
item1_table
item2_table
item3_table

Right now, I put all sql statement in DAL layers (Item1DAL, Item2DAL and Item3DAL to handle the corresponding UserControl and Tables)
and since they are "seperated", and the data must be inserted "all together", in case one of the tables/data got some problems, it must be rolled back, and I do not know where I can put the "transaction, roll back coding". 

Any Suggestion?

Answer 1

You may take a look at System.Transactions. btw, how is your object layer designed?

Answer 2

If we suppose that you access database “manually” using classes like SqlCommand, then use the variant that takes SqlTransaction as a parameter. The transaction can be obtained from SqlConnection.BeginTransaction(). If everything is OK (during ExecuteNonQuery etc.) then call transaction.Commit(). In case of exceptions call transaction.Rollback(), ignoring any other exceptions.

Answer 3

Please check following link. It contains detail description along with sample example.

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

More detail

http://msdn.microsoft.com/en-us/library/ms345266%28v=sql.90%29.aspx

Answer 4

You can use TransactionScope to perform transactions.

---

Welcome to MSDN Forums. Feel free to ask your questions and Please Note to Vote helpful topics and Mark answering posts. Sudhakar

Answer 5

If we suppose that you access database “manually” using classes like SqlCommand, then use the variant that takes SqlTransaction as a parameter. The transaction can be obtained from SqlConnection.BeginTransaction(). If everything is OK (during ExecuteNonQuery etc.) then call transaction.Commit(). In case of exceptions call transaction.Rollback(), ignoring any other exceptions.

Thanks Viorel ~ And yes, I access the database manually.

I got your main idea behind.  Since the sql statement (e.g. sql insert statement) are located in the DAL layer.

=== DAL Layer ===
Item1DAL.cs  (e.g. sql insert statement for item1_table)
Item2DAL.cs  (e.g. sql insert statement for item2_table)
Item3DAL.cs  (e.g. sql insert statement for item3_table)

I don't know about the "starting point" for SqlConnection.BeginTransactioin(), and which one is the ending point of "Transaction.Rollback()".

Please advice

Answer 6

Using Transactions in ADO.NET

http://www.codeproject.com/Articles/10223/Using-Transactions-in-ADO-NET

Regards,

Narendran Ponpandiyan

Answer 7

For me there is only one thing important with Transactions. 

A transaction must be an not interrupted continuous sequence of commands to the DataBaseServer. 

With any error the first thing which should be done is a roll back.

With not any error the first thing which should be done after the full complete of a transaction sequence is a commit. 

The reason from above: A transaction locks your database.

---

Success
Cor

Answer 8

Thanks Viorel ~

And yes, I access the database manually.

I got your main idea behind.  Since the sql statement (e.g. sql insert statement) are located in the DAL layer.

=== DAL Layer ===
Item1DAL.cs  (e.g. sql insert statement for item1_table)
Item2DAL.cs  (e.g. sql insert statement for item2_table)
Item3DAL.cs  (e.g. sql insert statement for item3_table)

I don't know about the "starting point" for SqlConnection.BeginTransactioin(), and which one is the ending point of "Transaction.Rollback()".

Please advice