2

At the moment I am searching for the most effective method of searching an ITable with one or more conditions (like where clauses).

I can perform it manually by iterating through each row and proof manually if the field "xy" has the value "ab". However, because the table has tens of thousands of row this will not be really effective. Does anyone has a effective and elegant solution?

Thanks for your help!

3
  • 2
    Did you try ITable.Search and pass it an IQueryFilter with a Where set to "xy = 'ab'"? Feb 2, 2013 at 20:06
  • Does the IQueryFilter also work with two or more arguments for a few columns?
    – FredFloete
    Feb 2, 2013 at 20:08
  • 1
    Yes, more info here. Feb 2, 2013 at 20:15

1 Answer 1

4

As Kirk Kuykendall said, pass a IQueryFilter to ITable.Search (or ITable.Update):

ITable table = …;
IQueryFilter what = new QueryFilter { WhereClause = "FieldA IS NULL AND FieldB > 10" };
ICursor rowCursor = table.Search(what, true);
try                            //^^^^
{
    for ((IRow row = rowCursor.NextRow(); row != null; row = rowCursor.NextRow())
    {
        …  // do something with row, which will match the above condition
    }
}
finally
{
    Marshal.ReleaseComObject(rowCursor);
}

Note: The exact capabilities of IQueryFilter are dependent on the workspace of your ITable. For example, some workspaces support the ORDER BY clause through the PostfixClause property, and some don't. Concerning the WhereClause property, be aware that the exact syntax rules can vary from one workspace type to the other. IIRC, the workspace's implementation of the ISQLSyntax interface can help you to format a correct WhereClause string.

Optimization hints:

When processing a large number of rows, it can help to...

  • reduce the amount of data processed as early as possible; that is, instead of filtering out each row manually, use the query filter so that uninteresting rows won't even have to be fetched at all.

  • same thing, but with fields: Only make ArcGIS fetch the fields that you're actually interested in. You can do this by setting the SubFields property of the IQueryFilter to something like FieldA, FieldB, …. (Don't forget about the Object ID field if you need it!)

  • use a recycling cursor if you can; that is, if you strictly process one row at a time, and you don't need to keep references to previously processed rows.

  • follow the COM interop performance advice (see the checklist linked to above); most importantly, keep the number of ArcObjects method calls and property accesses to a minimum, and prefer chunky method calls when possible.

2
  • I've a relationship class (1:1) added between the shape file and the table. How can I query the class to get the advanced informations?
    – FredFloete
    Feb 4, 2013 at 16:21
  • @FredFloete: This sounds like a different question to me, so answering in the comment section seems inappropriate. I suggest that you post your question, well, as a question, not as a comment. (Also, be somewhat more specific. What "advanced informations" are you interested in? Why did you put the relationship class into place?)
    – stakx
    Feb 4, 2013 at 20:17

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