0

Possible Duplicate:
Transactions in .net

I just wanted to know about Transaction in .net using c# language. I have gone through different articles on the net. However, I came to know about transaction theoretically but I wanted to know the exact use of it in real time. For example when exactly should I use transactions in real time. Let's suppose, I am writing code where I am doing some action on the click event of a link. Lets say, I am hitting the SQL connection to get some values. Should I use transaction there? If I am writing simple code, where I fetch values without using sql connection, should I use transactions there? What are the pros and cons of using Transactions. Getting theoritical knowledge is different, but I want to know the exact use of it. When to use when not to use. Can transactions be used in simple code? Any responses or links for even basic stuff about transactions are welcome.

Community
  • 1
  • 1
Running Rabbit
  • 2,634
  • 15
  • 48
  • 69

5 Answers5

3

I am hitting the SQL connection to get some values. Should I use transaction there?

No, there are not need to use transactions, when you are not alter data in database.

What are the pros and cons of using Transactions.

As you said, you have learned various articles, So may be you have figure out the reason of using the transactions. Look all of these in concern of database.

The advantages of three-tier applications in creating scalable and robust applications are made feasible by transaction processing systems. The ability to distribute the components that make up applications amongst separate servers without explicitly having to develop for that architecture is another advantage of transaction server processing. Transaction processing systems also ensure that transactions are atomic, consistent, isolated, and durable. This alleviates the developer from having to support these characteristics explicitly.

Why Do We Need Transaction Processing?
The Advantages Of Transaction Processing  

Can transactions be used in simple code?

Yes, you can simply write code in C# using ADO.Net. (e.g. SQLTransaction class)

e.g.

SqlConnection db = new SqlConnection("connstringhere");
      SqlTransaction transaction;

      db.Open();
      transaction = db.BeginTransaction();
      try 
      {
         new SqlCommand("INSERT INTO TransactionDemo " +
            "(Text) VALUES ('Row1');", db, transaction)
            .ExecuteNonQuery();
         new SqlCommand("INSERT INTO TransactionDemo " +
            "(Text) VALUES ('Row2');", db, transaction)
            .ExecuteNonQuery();
         new SqlCommand("INSERT INTO CrashMeNow VALUES " +
            "('Die', 'Die', 'Die');", db, transaction)
            .ExecuteNonQuery();
         transaction.Commit();

Reference:
Performing a Transaction Using ADO.NET
.NET 2.0 transaction model

Niranjan Singh
  • 18,017
  • 2
  • 42
  • 75
0

If you are serious about using Transactions you can also read some Articles about the TransactionScope class in .NET . It's very simple to implement Transactions this way.

http://simpleverse.wordpress.com/2008/08/05/using-transactionscope-for-handling-transactions/

Example:

using ( var transaction = new TransactionScope() )
{
     // My Database Operations. It doesn't matter what database Type

     transaction.Complete();
}
Mohnkuchenzentrale
  • 5,745
  • 4
  • 30
  • 41
0

The essence of transaction is to make sure that one or more changes that represent a single process get to database once and if one of them fail, the others should be reversed. If you are transferring money from one bank account to another, the deduction from one account and the depositing to the other account must be successful altogether. Otherwise, money would be lost in transit

using(SqlConnection conn = new SqlConnection())
{
   try
   {
    conn.Open();
    SqlTransaction tran = conn.BeginTransaction();

    //command to remove from account A
    //command to deposit into account B

    tran.Commit(); //both are successful
   }
   catch(Exception ex)
   {
      //if error occurred, reverse all actions. By this, your data consistent and correct
      tran.Rollback();
   }
}

Another alternative is TransactionScope, System.Transactions

codingbiz
  • 26,179
  • 8
  • 59
  • 96
0

generally you as most have suggested should probably be using TransactionScope, although this does come with some bits to be aware of.

using(TransactionScope ts = TransactionUtils.CreateTransactionScope()){
  using(SqlConnection conn = new SqlConnection(connString)){
    conn.Open();
    using(SqlCommand cmd = new SqlCommand("DELETE FROM tableName WHERE somethingorother", conn)){
    cmd....

    }
    using(SqlCommand cmd ....) ...
    thingy.Save();//uses another command/connection possibly
   }

//all above Sql Calls will be done at this point. all or nothing
  ts.Complete();
}

depending on how it is used (and what DB/version you are using), TransactionScope may escalate the transaction to MSDTC, which would need setting up on the machine running the app (dcomcnfg from the run prompt). ( TransactionScope automatically escalating to MSDTC on some machines? )

also worth having a read of this http://blogs.msdn.com/b/dbrowne/archive/2010/06/03/using-new-transactionscope-considered-harmful.aspx rather than blankly using a new TransactionScope() - that article suggests making a static method to generate one with some more helpful defaults.

public class TransactionUtils {
  public static TransactionScope CreateTransactionScope()
  {
    var transactionOptions = new TransactionOptions();
    transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted;
    transactionOptions.Timeout = TransactionManager.MaximumTimeout;
    return new TransactionScope(TransactionScopeOption.Required, transactionOptions);
  }
}

hth

Community
  • 1
  • 1
nat
  • 2,185
  • 5
  • 32
  • 64
0

I can tell you that using transactions depends on the business rules.

Technically, you may use transactions only if you are modifying data (Update, Delete, Insert) on the systems. When you are just getting values from a source and you are 100% sure that no data gets modified/produced, then don't include this step as part of your transaction.

To keep it simple, use transactions if you answer yes to any of these scenarios:

a) Affect more than one table in your click.

b) Affect more than one system in your click. This includes databases located in different servers, even if you have two different instances in the same server it counts as a different system. Web service calls count as another system as well.

c) Have an scenario like: "perform step a, perform step b, if everything OK then return OK else revert step b, revert step a then return error".

Now how to use transactions in real world. Well, if you are using only one database in your model then use the ADO.NET transaction model. http://adotutorial.blogspot.de/2008/11/transaction-management-in-adonet.html

If you however, are calling different instances of databases in the same server, or if you you are mixing different technologies (SQL, Oracle, Web Services), transaction management will be 1000 times more painful. Then you need to use transaction scope make it a little bit simpler. In C# in .NET 2.0 you have TransactionScope. This is the way that you can tell the run-time to help us manage transactions.

Check this tutorial, it may help. http://codingcramp.blogspot.de/2009/06/how-to-setup-and-use-transactionscope.html

If you are using microsoft technologies and if you are working on the same computer, then your code wil run fine. However, if you are running in a networked environment, or if you are mixing different vendors, then another key component enter in the play it is called a "Transaction Monitor". If that is your case then you may need to check if Microsoft DTC is enabled in your environment as it will be the default choice used for coordinating your transactions.

Adrian Salazar
  • 5,279
  • 34
  • 51