Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a small software that connects to a database (SQLServer) reads a few values and transfers them to a SDE Oracle instance (11g).

The problem is: I have over half million records that I need to transfer. My code reaches for the records without an issue, but when I try to insert...(my guess is that you guys already got throught this) I get a lovely COM error, screaming at me about Single Threaded and Multi Threaded apartments.

Here is a snippet:

        IFeatureWorkspace workspace = GetWorkspace() as IFeatureWorkspace;
        ITable newTable = workspace.CreateTable("blevers", fields, uid, null, "");

        IRowBuffer buffer = newTable.CreateRowBuffer();
        ICursor cursor = newTable.Insert(false);

        IMultiuserWorkspaceEdit workspaceEdit = workspace as IMultiuserWorkspaceEdit;
        IWorkspaceEdit2 workspaceEdit2 = workspaceEdit as IWorkspaceEdit2;
        workspaceEdit.StartMultiuserEditing(esriMultiuserEditSessionMode.esriMESMNonVersioned);
        workspaceEdit2.StartEditOperation();

        for (int i = 0; i <= stringList.Count - 1; i++)
        {
            buffer.set_Value(0, stringList[i]);
            cursor.InsertRow(buffer);
            Console.WriteLine("Escrevendo linha " + i.ToString() + " de " + stringList.Count);
        }
        cursor.Flush();


        workspaceEdit2.StopEditOperation();
        workspaceEdit2.StopEditing(true);

I usually get the error inside the for loop. How can I insert big big cursors without problems?

EDIT: i've also tried using a SchemaLock, but a COM error explodes in my face when converting the ITable to ISchemaLock. I'm out of ideas!

share|improve this question
    
Where are you executing the transfer? Could be that it's taking too long to execute and if it's a STA it frezes the application for too long. In that case putting the work in a background worker thread might help. –  MathiasWestin Sep 16 '10 at 15:15
    
Did you confirm that field 0 on blevers table is really the field you wish to populate? –  Kirk Kuykendall Sep 16 '10 at 15:16
    
Hello guys, the field exists, "blevers" it's just an example. Mathias, that is what is happening. I'll look into the background worker thread - i've never done that. Thanks for the help :D –  George Sep 16 '10 at 22:59
    
Might also work if you add a Application.DoEvents(); in the loop so the application can handle some events during the execution. –  MathiasWestin Sep 17 '10 at 6:25
    
I'll try that too. I'm still looking into BackgroundWorker! –  George Sep 17 '10 at 18:17

2 Answers 2

up vote 1 down vote accepted

When you create your insert cursor, you need to have the useBuffering parameter = true. And as others have suggested periodically flush the buffer.

ICursor cursor = newTable.Insert(true);

const int BufferFlushLimit = 500;

ICursor cursor = destinationTable.Insert(true);
IRowBuffer buffer = destinationTable.CreateRowBuffer();

try
{
    int bufferCount = 0;
    IRow sourceRow;

    // loop through the source data
    loop 
    {
        buffer.set_Value(//whatever);
        cursor.InsertRow(buffer);

        bufferCount++;
        if (bufferCount >= BufferFlushLimit)
        {
            cursor.Flush();
            bufferCount = 0;
        }
    }
    //do a final flush
    if (bufferCount > 0)
    {
        cursor.Flush();
    }
}
finally
{
    //Release com objects
    System.Runtime.InteropServices.Marshal.ReleaseComObject(cursor);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(buffer);
}
share|improve this answer

When running this type of operation, typically I flush the cursor every few hundred records.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.