Solved

Running multiple Transactions in SQL Server from C#

Posted on 2013-06-17
4
1,405 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:James Atkin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:James Atkin
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

626 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