[RESOLVED] TransactionScope with SqlDataAdapter

[vishalmarya]

I want to use transaction based updates for my application.

i use SqlDataAdapter and SqlCommandBuilder for inserts and updates.

And the sqlconnection is created at application startup.

But the Transactions.TransactionScope works if the sqlconnection is created inside the scope.

I there any other way i can implement transactions.

[vishalmarya]

ok I can use EnlistTransaction with my existing Connection Object

MyConnObject.EnlistTransaction(System.Transactions.Transaction.Current).

http://connect.microsoft.com/VisualS...edbackID=97722

[mendhak]

You shouldn't be creating a connection on application startup anyways as that's bad practice. You ideally want to create the connection as late as possible and then pass that to the adapter.

[vishalmarya]

You shouldn't be creating a connection on application startup anyways as that's bad practice. You ideally want to create the connection as late as possible and then pass that to the adapter.

thanks mendhak.

It means i should open the connection every time i need it ?

Is using EnlistTransaction recommended ?

Actually my application deals with multiple databases.
when i used EnlistTransaction for another database within the same transaction scope , it gave the MSDTC error.
i enabled it from the control panel.

But is this the right way to do it or i am unnecessarily creating a distributed transaction.

( my requirement is to update multiple tables in a single transaction scope and these tables reside across databases )

Please guide.