Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1647
  • 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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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