[GIS] the best approach to using IQueryFilter.WhereClause in ArcObjects

arcobjectsfilterquerysql

What is your preferred / best approach to using IQueryFilter.WhereClause statements in ArcObjects (e.g. when selecting features by attributes)?

You need to check the source type (shapefile / personal / file geodatabase etc.), then, check field types and so on, before you can actually construct the WhereClause statement and find the proper SQL syntax. I find it rather easy to get lost in all those apostrophes, quotation marks etc, especially if multiple fields are used in the query.

What is your best way to easily construct the WhereClause statement that would follow the valid SQL syntax..?

Best Answer

Use the methods available on the ISQLSyntax interface to make your code workspace-independent.

As the help on IQueryFilter.WhereClause explains, use the ISQLSyntax.GetSpecialCharacter method to return the delimited identifier prefix and suffix specific to your workspace and add them to your column identifiers.

Example:

ISQLSyntax sqlSyntax = (ISQLSyntax)((IDataset)table).Workspace;
string fieldPrefixDelimiter = sqlSyntax.GetSpecialCharacter(
    esriSQLSpecialCharacters.esriSQL_DelimitedIdentifierPrefix);
string fieldSuffixDelimiter = sqlSyntax.GetSpecialCharacter(
    esriSQLSpecialCharacters.esriSQL_DelimitedIdentifierSuffix);
whereClause = String.Format("{0}{1}{2} IN({3})",
    fieldPrefixDelimiter,
    fieldName,
    fieldSuffixDelimiter,
    delimitedValueString);

Of course you would still have to check field types to build the part after the operator -- delimitedValueString is produced by some other logic that checks the field type and builds up a list of values to put in the IN statement.

That part is implementation specific as to what field types you want to support. In my case I just wrap strings and GUIDs in single-quotes, pass numeric types through unchanged, and throw an error for other unsupported field types (I wouldn't know how to or want to handle date fields for example).

Related Question