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'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?

share|improve this question
    
You might try flushing the insert cursor every 10000 features or so. –  Petr Krebs Jan 12 '11 at 15:22
    
I'm flushing the cursor every 10% of features, if the number of features is bigger than 10.000. –  George Jan 12 '11 at 15:38
    
If the File Geodatabase is created or exists the append command might be a better solution. Would copy the entire data first and then delete any unwanted files after - filtering slows the process down. –  Mapperz Jan 12 '11 at 16:55
    
I agree with Kirk's answer, the row buffer should originate from CreateRowBuffer call on the table which you are inserting into. Now, you are using the source table's row buffer (the line: buffer = row as IRowBuffer). –  Petr Krebs Jan 12 '11 at 17:24
add comment

3 Answers 3

up vote 4 down vote accepted

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).

share|improve this answer
    
The path is always from sde to filegdb. This is not a problem, I guess. The big issue here is that I need to select a lot of things, it could be done with geoprocessing. I'll think a bit bout this. –  George Jan 12 '11 at 15:44
    
The size of the selection wouldn't matter if it were done via where clause –  jburka Jan 12 '11 at 15:55
add comment

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.

share|improve this answer
    
I'd advise against the usage of IFeatureClassWrite, it is intended to be used in custom features implementations. Instead, for feature classes, I had good results with temporarily putting the class in load-only mode using IFeatureClassLoad.LoadOnlyMode. In this mode, frequent spatial index updates are bypassed and postponed until the original mode is restored. –  Petr Krebs Jan 12 '11 at 17:31
    
I'll try to loop through the fields, but when I tried before using a simple for loop, it gave me "field is not editable" error. –  George Jan 12 '11 at 18:00
    
That's correct, you only need to copy the fields where IField.Editable is true. –  Kirk Kuykendall Jan 12 '11 at 18:21
add comment

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;
        }
    }
share|improve this answer
2  
I think it is very risky to assume that the i'th value of buffer corresponds to the i'th value of selected. Instead, I'd recommend setting up some sort of dictionary that maps which field from the selected row corresponds to which field in the buffer. –  Kirk Kuykendall Jan 12 '11 at 19:52
    
Hello Kirk. This is true, but the schemas are exactly the same. There might be differences of names, etc, but they are equal. If I was setting up something more complex, I would use a dict to map the differences, but in this case it's not necessary. Thank you for remininding me and letting this note for posterity. –  George Jan 12 '11 at 20:11
add comment

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.