[Solved] 100K insert problem

[Ashifur Rahaman]

Hello,
I'm new to OpenAccess ORM; trying to save 100K rows in a table but it is throwing exception but it is working using ADO.Net Entity Diagram Model. Actually I'm doing RnD to choose ORM for our next project. Sometimes we need to insert/update or both around 200K rows of about 4/5 tables. That is why I am testing on single table.

Here is my code snippet:

try
{
    Cct cct;               
    context.Transaction.Begin();
    
    for (int i = 1; i < 100001; ++i)
    {
        cct = new Cct();
        cct.CctID = i;
        cct.Code = i.ToString("D7");
        cct.CreatedBy = 1;
        cct.CreatedDate = DateTime.Now;
        cct.IsActive = true;
        cct.IsCentral = i == 1;
        cct.Name = string.Format("CCT {0}", i);

        context.Add(cct);
    }

    context.Transaction.Commit();
}
catch (Exception ex)
{
    context.Transaction.Rollback();
    throw ex;
}
finally
{
}

Exception Message:

Unable to reach database server on host ''.
Error Details:Insert of '783774595-98313' failed: Telerik.OpenAccess.RT.sql.SQLException: A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeUpdate()
   at OpenAccessRuntime.Relational.RelationalStorageManager.ExecuteStoredProcedure(CallableStatement cs, RelationalProcedure proc, Dictionary`2 outParams)
   at OpenAccessRuntime.Relational.RelationalStorageManager.InsertWithStoredProcedure(Connection con, PersistGraph graph, ClassMetaData cmd, Int32 tableNo, Int32 startIndex, Int32 count, Int32[] fieldNos, Int32 numFieldNos, Boolean fillFieldsFromOid, Boolean useKeyGenPost, Boolean useKeyGenPre, Object[] oidData, IntArray toUpdateIndexes)
'SP_CctInsert'
(set event logging to all to see parameter values)

StackTrace:

   at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)
   at Telerik.OpenAccess.RT.ExceptionWrapper.Throw()
   at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.handleException(Exception x)
   at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.internalCommit(Boolean phase)
   at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.commit()
   at OpenAccessRuntime.DataObjects.UnsynchronizedPMProxy.commit()
   at Telerik.OpenAccess.RT.TransactionImpl.Commit()
   at StsTranService.CctDA.CreateCct2(IObjectScope context) in C:\Users\Ashifur Rahaman\Desktop\TheStsTest Transaction\StsTranService\DataAccess\CctDA.cs:line 70


Please give me the solution for this.
Regards,
Ashifur

[Alexander]

Hello Ashifur Rahaman,

This seems like a connection timeout problem. By default OpenAccess keeps the connections active for two minutes. Normally inserting 100k objects should not take so much time but please check how long your code runs before the exception is thrown. If the time is more than two minutes you can try increasing the connection timeout. This setting can be changed from the Telerik -> OpenAccess -> Configuration -> Backend Configuration Settings dialog, under the Connection section. Hope that helps.

Kind regards,
Alexander
the Telerik team