• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1725
  • Last Modified:

Running multiple Transactions in SQL Server from C#

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
James Atkin
Asked:
James Atkin
  • 2
1 Solution
 
jonnidipCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
James AtkinSenior Principle Software EngineerAuthor Commented:
That looks like exactly what I am after - Thanks :-)
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
You are welcome.  Glad it helped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now