6

So I have multiple SqlConnections that I want to all be used in one SqlTransaction. I know that I could use just one connection, but ultimately in each connection there is a good amount of newly declared and useless (after the connection is done) variables, and I need the program to stay as fast as possible.

I have each Connection in a using statement.

I am using the latest .NET and SQL Server 2008 R2.

Is it possible?

ALSO, I already viewed:

How to use a single SqlTransaction for multiple SqlConnections in .NET?

Nobody answered the question, the platforms are outdated.

Community
  • 1
  • 1
Ian Best
  • 450
  • 1
  • 11
  • 23
  • can you show how you are instantiating the SqlConnection / SqlTransactions have you thought about naming the `SqlTransaction` objects it takes a name as one of the overloads.. – MethodMan Feb 27 '13 at 22:27
  • I haven't attempted the instantiation of the SqlTransaction yet, because if it can't support multiple connections I already know how to do it. I am just instantiating each connection with a using statement. using (SqlConnection connect = new SqlConnection(connectionString)){ – Ian Best Feb 27 '13 at 22:30
  • have you tried any of the things that are on the link you posted in your answer using multiple using clauses problem is in that example you would need to instantiate an new SqlTransaction after code exits the `using(){}` code block – MethodMan Feb 27 '13 at 22:31
  • Yeah, I know. But is it possible to use one? I need everything to rollback together if anything fails. – Ian Best Feb 27 '13 at 22:35
  • can you show your code.. I have never done Transactions with a single SqlTransaction object I have done it creating multiple Transaction Objects and naming the transaction object so that It was easily maintainable – MethodMan Feb 27 '13 at 22:41
  • `SqlTransaction` is initialized via `SqlConnection.BeginTransaction()` method so I doubt you would have any luck "spanning" it across two or more connections. What about some queuing mechanism? Store your data into queue and dump your queue to DB in batches at regular intervals. – Ramunas Feb 27 '13 at 22:53
  • `in each connection there is a good amount of newly declared and useless ... variables` What do you mean by this? What 'variables' does a SqlConnection have? Why can't you create a single connection, begin a transaction, then use that connection with one or more SqlCommands to perform all the work you need to perform? – Chris Dunaway Feb 27 '13 at 22:56

1 Answers1

13

The question you linked has the answer that I would give you, TransactionScope.

The purpose of this is to have your connections automatically join an already existing transaction automatically.

using(System.Transacation.TransactionScope myScope = new TransactionScope()){
  //all of your sql connections and work in here

  //call this to commit, else everything will rollback
  myScope.Complete();
}

Look into transactionscope more here:

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

If this doesn't answer your question, then I've thoroughly misunderstood it.

Mike C.
  • 2,804
  • 2
  • 18
  • 18
  • 1
    +1 - worth noting that `TransactionScope` will selectively escalate to the DTC, which must be enabled for it to work correctly in those scenarios. – Tim Medora Feb 28 '13 at 01:06
  • @TimMedora I tried searching on the internet, but I couldn't find anything and I really have no idea what that means. Escalate to DTC? – Ian Best Feb 28 '13 at 14:25
  • This is a different question really, but here are two things. 1. Open google and type in your error message. There's lots of resources already that answer this for you. But since I already did it, it seems you might just need to start the service in services.msc. If you don't know how to do that then I suggest you google it, or start a new question, but it's pretty simple, I'm sure you can figure it out easily. You're looking for the 'Distributed Transaction Coordinator' service. – Mike C. Mar 04 '13 at 15:18