?
Solved

Running multiple Transactions in SQL Server from C#

Posted on 2013-06-17
4
Medium Priority
?
1,597 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
  • 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 2000 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

601 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