Solved

Running multiple Transactions in SQL Server from C#

Posted on 2013-06-17
4
1,296 Views
Last Modified: 2013-06-18
Hi all,

I have an application that uses a MS SQL 2012 server.
There are a bunch of functions, each of which use a single connection and execute updates, inserts, queries etc...
If I run these in quick succession I get errors with a running multiple Transactions on a single connection.
What is the best way to work around this?
I don't really want to open multiple connections if I can avoid it, but is there a way to "queue" transactions?
Also, is there a way to check if a connection is already assigned to a running transaction?
Any help would really be appreciated.
I am running under Visual Studio 2012 targeting .NET Framework 4.5
Thanks,
James
0
Comment
Question by:jatkin
  • 2
4 Comments
 
LVL 13

Expert Comment

by:jonnidip
ID: 39253168
As for my experience, a transaction should not opened multiple times, but should be "passed" (if it already exists) to the next execution.
I personally use the System.Transactions dll, that lets you open a "TransactionScope" where you can add the execution of multiple functions.
Please note that this needs to be explicitly committed:
using (TransactionScope ts = new TransactionScope())
{
   // your code
   ts.Complete();
}

Open in new window

0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
ID: 39253644
This is what you are looking for (please read below link):

http://msdn.microsoft.com/en-us/library/h32h3abf.aspx

Implementing MARS will enable to not have to bother with session transaction coupling while using the same physical session for multiple transactions.

Hope this helps...
0
 
LVL 4

Author Closing Comment

by:jatkin
ID: 39255438
That looks like exactly what I am after - Thanks :-)
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39255448
You are welcome.  Glad it helped.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now