CODEWITHSUNDEEP
c#.netsql-serversql-server-2008
naveed
naveed

Reputation: 1465

Do transactions have to be in Stored Procedures in SQL Server?

I have a bunch of update and insert queries which I call from my C# code that need to be in a transaction. I'd prefer to keep them in C# rather than use Stored Procedures. I use SQLCommand.ExecuteNonQuery() to begin and commit the transaction. It works ok till I try commit the transaction, when I get a message that "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION". I am closing the connection between calls. Is this the problem?

Upvotes: 1

Views: 713

Answers (1)

Jon Raynor
Jon Raynor

Reputation: 3892

No they don't.

One can control the transaction in C# directly on the connection itself or use an ambient transaction using the System.Transactions namespace if you want the transaction to span across database connections or even different databases (Distributed Transaction).

If you are closing the connection between calls, use the System.Transactions namespace to control your transactions. The transaction will then span across connections.

Some basic code to use the System.Transactions namespace:

using (TransactionScope scope = new TransactionScope())
{
     //Do operation 1 on connection 1 (open close connection)
     //Do operation 2 on connection 2 (open close connection)

     scope.Complete();
}

Upvotes: 5

Related Questions