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.

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

share|improve this question
add comment

2 Answers

up vote 7 down vote accepted

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

share|improve this answer
    
How about an example, professor? :) –  Nathanus Nov 17 '11 at 21:03
    
I guess the original answer didn't do this fairly tricky issue justice, and probably still doesn't. I don't know how to handle date fields in a workspace-independent way for example, but this is a start. –  blah238 Nov 17 '11 at 22:57
1  
+1 I think it's a great explanation to get things going, especially considering the amount of rage the ESRI SDK docs often incite in my nubile mind. –  Nathanus Nov 17 '11 at 22:58
1  
Great example, thanks! Wouldn't be useful, though, if ESRI would add a method for wrapping values respecting the field type..? Then it would be pretty straightforward to build a safe WhereClause. –  jan_b Nov 18 '11 at 7:06
1  
+1 I'd also like to know of a way at run time to determine the maximum allowable length of the WhereClause string, which I suspect is configured by the DBMS. –  Kirk Kuykendall Jan 9 '12 at 23:33
add comment

I understand the head ache when constructing a whereclause but in VB I have recently adopted the approach of using the VB function CHR() when dealing with File geodatabase fields which need to be enclosed in double quotes.

So to make the clause string: ""ID" = 6" I would do it as:

pQueryFilter.WhereClause = chr(34) & "ID" & chr(34) & " = 6"

Duncan

share|improve this answer
    
By doing so you're hardcoding it to the file geodatabase/shapefile field delimiter and your code would fail on personal and SDE geodatabases as well as Excel and text file tables. Best to use the workspace-independent functions on ISQLSyntax to make your code more robust. Of course if you only ever use shapefiles and file geodatabases you can probably get away with this. –  blah238 Jan 9 '12 at 22:41
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.