The transaction must be disposed before the connection can be used to execute sql statements

Question

I am getting this error

The transaction must be disposed before the connection can be used to execute sql statements.

i have an Excel file that contains about 6000 rows and I uploaded these file into Data table in typed dataset, then I am trying to apply my business logic on these rows in dt.

The exception throws from the second loop and I have to do two loops; why does this exception occur, and how can I solve it?

Here is my code:

try
{
    using (TransactionScope scope = SysInfo.BeginTransaction(IsolationLevel.Serializable))
    {

        //Here is my Typed dataset

        //Method Looping through row in Datatable & Calling DB

        //another Method Looping through row in Datatable & Calling DB

        scope.Complete();
    }
}
catch (Exception ex) { throw ex; }

Without some code of your inner loops it is difficult to say what's wrong here. And please, don't catch exception and rethrow the same. It destroys precious informations. — Steve, Jun 28 '12 at 16:55

score 8 · Accepted Answer · edited Jul 30 '18 at 08:58

8

I solved it by adding the below lines in App.config:

<configuration>
    <system.transactions>
        <defaultSettings timeout="00:01:30" />
    </system.transactions>
</configuration>

and this in Machine.config:

<configuration>
    <system.transactions>
        <machineSettings maxTimeout="00:01:30" />
    </system.transactions>
</configuration>

As this process takes a very long time greater than 10 minutes, so I found that I need to overwrite this value with a higher one.

edited Jul 30 '18 at 08:58

halfer

19,824
17
99
186

answered Jul 04 '12 at 12:10

hatem

373
3
12
25

1

Path of Machine Config %windir%\Microsoft.NET\Framework64\[version]\config\machine.config – taher chhabrawala Oct 10 '14 at 14:44

The transaction must be disposed before the connection can be used to execute sql statements

1 Answers1

Linked