Using SqlConnection object with TransactionScope

S

Scott

I am using SQL Express 2005 SP 2 with Windows XP SP3, Developer Studio 2005.

A SqlConnection object is created as a member in a class and then used by
other methods.
This connection object is held opened during the many calls to methods that
save to the database.
For storage purpose TableAdapters are being used.

What is desired is to use TransactionScope with TableAdapters however
according to .NET documentation the creation of the connection object needs
to be done within the TransactionScope block of code. If the connection
object is done outside the TransactionScope block the roll back of the
transaction does not occur (tested by not calling
TransactionScope.Complete()). See code snippet below:
SqlConnection connection = new SqlConnection(); // Member of class
public void saveToDatabase()
{
using (TransactionScope scope = new
TransactionScope(TransactionScopeOption.RequiresNew))
{
// Would create the SqlConnection object here

myTableAdapter.Connection = connection;
myDataAdapter.Update(myDatSet);
scope.Complete(); // Not calling this does not cause a roll back
}
}

I realize that .NET promotes the concept of disconnected record sets but in
this case it is not is desired.
It seems that TableAdapters are limited to using TransactionScope for
database transaction.
Is there a way to use TransactionScope with a connection object when it is
defined outside of the TransactionScope block or is there some other way to
provide a transaction?
 
C

Cor Ligthert[MVP]

Scott,

I use the transaction locking methods.

Be aware that you have nothing between the locks, that can stop processing,
You are in trouble and the best thing you can get then is a timeout.

A reason to use a transaction can be as you have related data that has to be
written together to keep everything right.

http://msdn.microsoft.com/en-us/library/86773566.aspx

(The record set is more a kind of class from the Com time, I assume you mean
a result set)


Cor
 
S

Scott

Cor,

The application uses TableAdapters, after using SqlTransaction with a call
to connection.BeginTransaction() causes the TableAdpater to throw an
exception during it's Update().
I am looking for a solution to continue to use TableAdapters with a
transaction but the TransactionScope will not work due to the connection
object is defined as a member of the class.

Scott
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top