Devart liqconnect and transactionscope - insert many rows (2

[janm]

I use free version of linqconnect http://www.devart.com/dotconnect/oracle ... _linq.html from devart in my WPF app.

I would like use use tranasaction with linqconnect because I insert many row to table (20 000 - 30 000 row).

Here is my class for CRUD operation in Oracle DB.

Code: Select all

[Export (typeof(IDb))]
public class TDb:IDb,
   IDisposable
{
    #region Private fields

    private TDataContext _dc;

    #endregion

    #region Constructor

    public TmobileDb()
    {
        _dc = new TDataContext();
    }

    #endregion

    #region Destructors

    ~TmobileDb()
    {
        Dispose(false);
    }

    #endregion


    #region Implementation of IDb

    public void InsertCalls(IListcalls)
    {
        Mouse.SetCursor(Cursors.Wait);

        using (var scope = new TransactionScope())
        {
            foreach (var call in calls)
            {
                var record = new RCALLSWITHOUTDISCOUNT
                {
        //data

                };

                _dc.RCALLSWITHOUTDISCOUNTs.InsertOnSubmit(record);
            }

            try
            {
                _dc.SubmitChanges();
                scope.Complete();
                Mouse.SetCursor(Cursors.Arrow);

            }
            catch (Exception ex)
            {
                Mouse.SetCursor(Cursors.Arrow);
                throw ex;
            }
        }
    }

    #endregion

    #region Implementation of IDisposable

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing)
    {
        if (disposing) 
        {
            if (_dc != null)
            {
                _dc.Dispose();
                _dc= null;
            }
        }
    }
    #endregion
}

I call method void InsertCalls(IListcalls) from view model class:

- first time my all rows are inserted to database
- I call the same method with same data second time, all rows are inserted
- I call this method third time and I get this error:

Code: Select all

{"The operation is not valid for the state of the transaction."}

sometime I get this error:

Code: Select all

{"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 google it, but I dont know how solve this problem.

Thank for help

[StanislavK]

One of the possible reasons for the issue is that the connection you are working with becomes invalid, but is reused by the connection pool manager. To check this possibility, please try setting the 'Validate Connection' connection string parameter to true or the 'Pooling' one to false. In the first case, the connection will be validated before being re-opened; in the latter case, a new internal connection will be established each time the connection is opened.

If possible, please send us a small test project with which the issue can be reproduced, so that we are able to analyze it in details.

JIC: DataContext is considered to be a light-weight object that should be created for each unit of work (e.g., a single transaction). Thus, generally it is not recommended to perform multiple separated CUD operations via a single DataContext instance.