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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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.
         }