.NET TransactionScope Timeout Issue/ EF

I have a WinForms project where i have to read massive xml-files (2gb+) and store the data in a MSSQL database.

After 10 minutes i get the error:

"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 figured this had to be a timeout issue, so I did the following:

I my code I use:

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*/
}

To my App.config I added:

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

To my machine.config I added:

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

And in MSSQL i set the remote query timeout to 0开发者_运维技巧

Still this timeout occurs. What am I doing wrong?

I would start by pointing that doing such massive upload in single transaction from EF is wrong. Such operation should be done outside of EF for example with bulk insert into temporary table and merged to main data set.

It is possible also scenario for SSIS (SQL Server integration services).

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

Read xml file outside of using block.

I would suggest to use sqlcommand directly to insert records. Dont use EF as it takes its time to translate objects to records.

Also, I would use SQLTransaction instead of scope . Although, scope will use sqltransaction underneath but you have got more control using sqltrasnaction.

My code would look like