.NET TransactionScope / EF Transaction Timeout

Question

I have a WinForms project where I have to read massive xml files (2gb +) and store data in an MSSQL database.
After 10 minutes, I get an error message:
"The transaction associated with the current connection has been completed but has not been deleted. The transaction must be deleted before the connection is used to execute SQL statements."

I realized that this should be a timeout problem, so I did the following:

I use my code:

using (tran = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.Serializable, Timeout = TimeSpan.Zero //TimeSpan.FromHours(2) })) { /*Here I read out xml, and use EF to write to DB*/ } 

In my App.config, I added:

 <system.transactions> <defaultSettings timeout="2:00:00"/> </system.transactions> 

In my machine.config, I added:

 <system.transactions> <machineSettings maxTimeout="2:00:00"/> </system.transactions> 

And in MSSQL I set the remote request timeout to 0

This timeout is still happening. What am I doing wrong?

Answer 1

I would say that doing such a bulk load in one transaction from EF is not so. Such an operation must be performed outside of EF, for example, with bulk insertion into a temporary table and combined with the main data set.

A script is also possible for SSIS (SQL Server Integration Services).

EF is not a tool for data transfer or synchronization, and its performance in such scenarios is tragic.

Answer 2

Read the xml file outside the used block.

I would suggest using sqlcommand directly to insert records. Do not use EF because it takes time to translate objects to records.

Also, I would use SQLTransaction instead of scope. Although, the scope will use sqltransaction under it, but you will get more control using sqltrasnaction.

My code will look like