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

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

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.
–
blah238Nov 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.
–
NathanusNov 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_bNov 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 KuykendallJan 9 '12 at 23:33

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.

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.
–
blah238Jan 9 '12 at 22:41