Transfering a lot of features from geodatabases

Question

I'm having a problem where I need to export a large amount of content from 2 geodatabases of different nature (SDE -> File).

I have three options: Spatial Select, a regular SQL select and the full feature class/table.

What are the best practices to handle these large transfers?

I'm having issues with the following code:

    public void ExportAll(IGisEntity entity)
    {
        ITable table = _Context.GetEntityTable(entity.EntityName);
        IFeatureWorkspace workspace = _OutWorkspace as IFeatureWorkspace;
        ITable outTable = workspace.OpenTable(entity.EntityFeatureClass);

        if (table == null)
            throw new ArgumentException("A tabela de origem não existe.");

        if (outTable == null)
            throw new ArgumentException("A tabela de destino não existe.");

        ICursor selectCursor = table.Search(null, false);
        IRow selectRow = null;

        ICursor insertCursor = outTable.Insert(true);
        IRowBuffer buffer = outTable.CreateRowBuffer();

        int recordCount = table.RowCount(null);
        int count = 0;
        int step = recordCount;
        if (recordCount > 10000)
            step = Convert.ToInt32(Math.Round(recordCount * .1, 0));

        int remainder = 0;

        while ((selectRow = selectCursor.NextRow()) != null)
        {
            buffer = selectRow;
            insertCursor.InsertRow(buffer);
            count++;
            Math.DivRem(count, step, out remainder);
            if (remainder == 0)
                insertCursor.Flush();
        }
        insertCursor.Flush();

        CustomComReleaser.ReleaseComObject(insertCursor);
        CustomComReleaser.ReleaseComObject(selectCursor);
    }

    public void ExportSelect(IGisEntity entity, int sectorNo)
    {
        ITable table = _Context.GetEntityTable(entity.EntityName);
        IFeatureWorkspace outWork = _OutWorkspace as IFeatureWorkspace;
        ITable outTable = outWork.OpenTable(entity.EntityFeatureClass);

        if (table == null)
            throw new ArgumentException("A tabela de origem não existe.");

        if (outTable == null)
            throw new ArgumentException("A tabela de destino não existe.");

        IQueryFilter filter = new QueryFilterClass();
        filter.WhereClause = String.Format(@"{0}_GR_IDENTIFICADOR LIKE '1{1}%'", entity.EntityCode.Substring(1, 3), sectorNo.ToString());

        ICursor selectCursor = table.Search(filter, false);
        IRow selectRow = null;

        IRowBuffer buffer = outTable.CreateRowBuffer();
        ICursor insertCursor = outTable.Insert(true);

        int recordCount = table.RowCount(filter);
        int count = 0;
        int step = recordCount;
        if (recordCount > 10000)
            step = Convert.ToInt32(Math.Round(recordCount * .1,0));

        int remainder = 0;

        while ((selectRow = selectCursor.NextRow()) != null)
        {
            buffer = selectRow;
            insertCursor.InsertRow(buffer);
            count++;
            Math.DivRem(count, step, out remainder);
            if (remainder == 0)
                insertCursor.Flush();
        }
        insertCursor.Flush();

        CustomComReleaser.ReleaseComObject(insertCursor);
        CustomComReleaser.ReleaseComObject(selectCursor);
    }

When one operating on ArcSDE systems these methods tend to hang and gives me I/O errors.

I look into IGeodatabaseBridge interface and IGeoDbDataTransfer, but none of them gives me the option to export a cursor (product of a select, for example).

This is now a major issue: everytime I'm getting a RPC_ERROR_SERVERFAULT with this snippet.

        while ((row = select.NextRow()) != null)
        {
            buffer = row as IRowBuffer;
            insert.InsertRow(buffer);
            count++;
            if (count == 4999)
            {
                insert.Flush(); // this is where the error explodes at my face!
                count = 0;
            }
        }

Any choices?

Answer 1

You might try using one of the GP copy tools (Copy, Copy Rows, Copy Features). I've found some of esri's push toward more coarse-grained operations annoying, but I've also found that the GP tools can be more reliable in some situations than straight c# coding.

Obviously, one thing to watch for is the differing limitations placed on different geodatabase formats (e.g. file gdb fields can't always go directly into an SQL Server SDE because of the length limit on field names).

Answer 2

I think it would be much safer to copy individual field values from selectRow to buffer instead of just assigning buffer = selectRow. If performance is still slow, you might try bypassing the OnStore events using IFeatureClassWrite (I've never tried it though).

Update:

Quite certain this is not good:

buffer = row as IRowBuffer;

Instead, loop through each field and copy. If you're going to a shapefile from arcsde, you should probably use IFieldsChecker to figure out what field from the input row goes to which field in the output buffer.

Answer 3

I'm answering this because this is a mix of answers that made it all work.

1. Have a fast LAN with your SDE server.
2. Never assign a buffer to be equal some row. It changes the origin of the row to be source row, therefore, it won't insert into the destination table.
3. Always use IFeatureClassLoad.LoadOnlyMode. It makes things fast. This will also work with tables (by the docs I'm unsure if this only works with tables in File/Personal GDBs - it might work with SDE too - if anyone can comment on this...)
4. As a corolary to the use of IFeatureClassLoad use ISchemaLock to lock the table you're inserting into.
5. Balance between the number of "flushes" to the database. The flush operation seems expensive. Don't over do it and don't under do it.
6. Always check ESRIs documentation. There is an interface that already DOES what I need: IFeatureDataConverter and IFeatureDataConverter2; (it does what I need, but it creates a new Table...so, my code is still "half" useful")

Thanks for all who helped!

This is the final snippet:

    public void Export(ITable inTable,ITable outTable,IQueryFilter filter)
    {
        // i've got my tables from somewhere...

        if (inTable == null)
            throw new ArgumentException("A tabela de origem não existe.");

        if (outTable == null)
            throw new ArgumentException("A tabela de destino não existe.");

        ISchemaLock schemaLock = outTable as ISchemaLock;
        IFeatureClassLoad load = outTable as IFeatureClassLoad;

        try
        {
            schemaLock.ChangeSchemaLock(esriSchemaLock.esriExclusiveSchemaLock);

            load.LoadOnlyMode = true;

            ICursor select = inTable.Search(filter, false);
            IRow selected = null;

            ICursor insert = outTable.Insert(true);
            IRowBuffer buffer = outTable.CreateRowBuffer();

            int count = 0;
            int recordCount = inTable.RowCount(filter);
            int step = recordCount;
            if (recordCount > 10000)
                step = Convert.ToInt32(Math.Round(recordCount * .1, 0));

            int remainder = 0;

            while ((selected = select.NextRow()) != null)
            {
                for (int i = 0; i <= buffer.Fields.FieldCount - 1; i++)
                {
                    if (buffer.Fields.get_Field(i).Editable == true)
                        buffer.set_Value(i, selected.get_Value(i));
                }

                insert.InsertRow(buffer);
                count++;
                Math.DivRem(count, step, out remainder);
                if (remainder == 0)
                    insert.Flush();
            }
            insert.Flush();

            CustomComReleaser.ReleaseComObject(insert);
            CustomComReleaser.ReleaseComObject(select);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            schemaLock.ChangeSchemaLock(esriSchemaLock.esriSharedSchemaLock);
            load.LoadOnlyMode = false;
        }
    }