How to use transaction and Commit in three tier architecture?

Question

0.00/5 (No votes)

See more:

, +

Any one can give me link OR Suggest me... How to use transaction and commit in three tier asp.net architecture. And on which layer i can use transaction and commit for multiple insert QUERY ..

Posted 8-Sep-15 23:33pm

Member 10711621

Add a Solution

2 solutions

Solution 1

Transactions should be kept in the same layer as the database access, i.e. the Data Access layer (DAL)

I use a TransactionScope wrapper for all of my transactions:

C#

public delegate void Transaction();

private const int Retries = 3;

public static void TransactionWrapper(Transaction transaction)
{
    var options = new TransactionOptions
    {
        IsolationLevel = IsolationLevel.Snapshot,
        Timeout = TimeSpan.FromSeconds(120)
    };

    for (int x = 0; x < Retries; x++)
    {
        try
        {
            using (var scope = new TransactionScope(TransactionScopeOption.Required, options))
            {
                transaction();

                scope.Complete();

            }

            x = Retries;
        }
        catch (Exception exception)
        {
            //Exception loggedException = new Exception(string.Format("Transaction attempt {0} fail", x), exception);
            //Logging.Logs.LogException(loggedException);
            if (x == Retries - 1)
                throw;
        }
    }
}

Which is used as follows:

C#

//create query
object result;
TransactionWrapper(()=>{
   result = //run the query
});

return result;

My code lends itself to creating all of the query cases on the DAL, including multiple transactions so I can add all of them in the wrapper at the same time.

I hope that helps ^_^
Andy

Posted 9-Sep-15 0:21am

Andy Lanng

Comments

Enemali William 10-Sep-15 10:23am

It is also advisable to use add an isolation level when you are running this to avoid
deadlocks

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sarath kumar.N · Accepted Answer · 2015-09-09T00:27:00

You can INSERT multiple records at once instead of inserting each time.

C#

string conStr=""; //Connection string
using (OleDbConnection con = new OleDbConnection(constr))
         {
             conStr.Open();
             var trans = con.BeginTransaction(); //Begin the transaction
             foreach (DataRow dr in dataTable.rows)
             {
                 //Read the data from datatable and insert
                 string query = ""; //Query

                 OleDbCommand cmd = new OleDbCommand(query, con);
                 cmd.Transaction = trans; //Assign transaction to oledb command
                 cmd.Parameters.AddWithValue("@Status", status);

                 cmd.ExecuteNonQuery();
             }
              trans.Commit(); //Committing transaction at once.
         }

How to use transaction and Commit in three tier architecture?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to use transaction and Commit in three tier architecture?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us