I am developing a .NET 4.0 application that connects to a remote MS SQL database.

Calling tableAdapter.Update() on each table is very slow. From what I have read on the Internet, updating the database within a transaction should be much faster, however all my attempts so far have resulted in errors.

I am looking for the simplest and fastest method to update changes in a dataSet to the remote server -though anything that works would be very much appreciated



What I have tried:


using (TransactionScope tsc = new TransactionScope())
{
projectFamilyFrequenciesTableAdapter.Connection.Open();
projectCorpusCoverageTableAdapter.Update(dataSet1);
projectCorpusCoverageTableAdapter.Connection.Close();

textFamilyFrequenciesTableAdapter.Connection.Open();
textFamilyFrequenciesTableAdapter.Update(dataSet1);
textFamilyFrequenciesTableAdapter.Connection.Close();

//...and so on for each TableAdapter

tsc.Complete();
}



After processing several tables this throws the exception: "Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool."

I don't want users to need to alter their settings just to use my application, so this is out.

Placing only one update per transaction scope to avoid promotion to DTC results in another error:


using (TransactionScope tsc = new TransactionScope())
{
projectCorpusCoverageTableAdapter.Update(dataSet1);

tsc.Complete();
}
//... and so on for each table adapter



On some tables, this throws: "The transaction was aborted" or "The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements."

I have also tried the approach described here: http://weblogs.asp.net/ryanw/archive...commentmessage

I am not even sure I am on the right track. What is the fastest way to update a DataSet to a remote server?