Big IRowBuffer problem

Question

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!

Answer 1

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);
}

Answer 2

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