Querying geodatabase tables

// Create the query filter.
IQueryFilter queryFilter = new QueryFilterClass();

// Select the fields to be returned—the name and address of the businesses.
queryFilter.SubFields = "NAME, ADDRESS";

// Set the filter to return only restaurants.
queryFilter.WhereClause = "TYPE = 'Restaurant'";

// Use the PostfixClause to alphabetically order the set by name.
IQueryFilterDefinition queryFilterDef = (IQueryFilterDefinition)queryFilter;
queryFilterDef.PostfixClause = "ORDER BY NAME";

// Output the returned names and addresses.
int nameIndex = table.FindField("NAME");
int addressIndex = table.FindField("ADDRESS");
using (ComReleaser comReleaser = new ComReleaser())
{
  ICursor cursor = table.Search(queryFilter, true);
  comReleaser.ManageLifetime(cursor);
  IRow row = null;
  while ((row = cursor.NextRow()) != null)
  {
    String name = Convert.ToString(row.get_Value(nameIndex));
    String address = Convert.ToString(row.get_Value(addressIndex));
    Console.WriteLine("{0} - {1}", name, address);
  }
}

' Create the query filter.
Dim queryFilter As IQueryFilter = New QueryFilterClass()

' Select the fields to be returned—the name and address of the businesses.
queryFilter.SubFields = "NAME, ADDRESS"

' Set the filter to return only restaurants.
queryFilter.WhereClause = "TYPE = 'Restaurant'"

' Use the PostfixClause to alphabetically order the set by name.
Dim queryFilterDef As IQueryFilterDefinition = CType(queryFilter, IQueryFilterDefinition)
queryFilterDef.PostfixClause = "ORDER BY NAME"

' Output the returned names and addresses.
Dim nameIndex As Integer = table.FindField("NAME")
Dim addressIndex As Integer = table.FindField("ADDRESS")
Using comReleaser As ComReleaser = New ComReleaser()
  Dim cursor As ICursor = table.Search(queryFilter, True)
  comReleaser.ManageLifetime(cursor)
  Dim row As IRow = cursor.NextRow()
  While Not row Is Nothing
    Dim name As String = Convert.ToString(row.Value(nameIndex))
    Dim address As String = Convert.ToString(row.Value(addressIndex))
    Console.WriteLine("{0} - {1}", name, address)
    row = cursor.NextRow()
  End While
End Using

// Create the envelope and define its position.
IEnvelope envelope = new EnvelopeClass();
envelope.PutCoords(-84.4078, 33.7787, -84.3856, 33.7997);

// Create the spatial filter and set its spatial constraints.
ISpatialFilter spatialFilter = new SpatialFilterClass
{
  Geometry = envelope,
  GeometryField = featureClass.ShapeFieldName,
  SpatialRel = esriSpatialRelEnum.esriSpatialRelIntersects
};

// Set the attribute constraints and subfields.
// You want to exclude ramps, highways and interstates.
spatialFilter.WhereClause = "NAME <> 'Ramp' AND PRE_TYPE NOT IN ('Hwy', 'I')";
spatialFilter.SubFields = "NAME, TYPE";

// Execute the query, displaying the roads returned.
int nameIndex = featureClass.FindField("NAME");
int typeIndex = featureClass.FindField("TYPE");
using (ComReleaser comReleaser = new ComReleaser())
{
  IFeatureCursor featureCursor = featureClass.Search(spatialFilter, true);
  comReleaser.ManageLifetime(featureCursor);
  IFeature feature = null;
  while ((feature = featureCursor.NextFeature()) != null)
  {
    String roadName = Convert.ToString(feature.get_Value(nameIndex));
    String roadType = Convert.ToString(feature.get_Value(typeIndex));
    Console.WriteLine("Name: {0}, Type: {1}", roadName, roadType);
  }
}

' Create the envelope and define its position.
Dim envelope As IEnvelope = New EnvelopeClass()
envelope.PutCoords(-84.4078, 33.7787, -84.3856, 33.7997)

' Create the spatial filter and set its spatial constraints.
Dim spatialFilter As ISpatialFilter = New SpatialFilterClass With _
{ _
  .Geometry = envelope, _
  .GeometryField = featureClass.ShapeFieldName, _
  .SpatialRel = esriSpatialRelEnum.esriSpatialRelIntersects _
}

' Set the attribute constraints and subfields.
' You want to exclude ramps, highways and interstates.
spatialFilter.WhereClause = "NAME <> 'Ramp' AND PRE_TYPE NOT IN ('Hwy', 'I')"
spatialFilter.SubFields = "NAME, TYPE"

' Execute the query, displaying the roads returned.
Dim nameIndex As Integer = featureClass.FindField("NAME")
Dim typeIndex As Integer = featureClass.FindField("TYPE")
Using comReleaser As ComReleaser = New ComReleaser()
  Dim featureCursor As IFeatureCursor = featureClass.Search(spatialFilter, True)
  comReleaser.ManageLifetime(featureCursor)
  Dim feature As IFeature = featureCursor.NextFeature()
  While Not feature Is Nothing
    Dim roadName As String = Convert.ToString(feature.Value(nameIndex))
    Dim roadType As String = Convert.ToString(feature.Value(typeIndex))
    Console.WriteLine("Name: {0}, Type: {1}", roadName, roadType)
    feature = featureCursor.NextFeature()
  End While
End Using

// Cast the bag to the ISpatialIndex interface.
ISpatialIndex spatialIndex = (ISpatialIndex)geometryBag;
spatialIndex.AllowIndexing = true;
spatialIndex.Invalidate();

' Cast the bag to the ISpatialIndex interface.
Dim spatialIndex As ISpatialIndex = CType(geometryBag, ISpatialIndex)
spatialIndex.AllowIndexing = True
spatialIndex.Invalidate()

// Create the QueryDef.
IQueryDef2 queryDef2 = (IQueryDef2)featureWorkspace.CreateQueryDef();

// Specify the table and fields to query.
queryDef2.Tables = "Cities";
queryDef2.SubFields = "Name, Pop1996";
queryDef2.PostfixClause = "ORDER BY Pop1996 DESC";


// Execute the query.
using (ComReleaser comReleaser = new ComReleaser())
{
  ICursor cursor = queryDef2.Evaluate2(true);
  comReleaser.ManageLifetime(cursor);
  int nameIndex = cursor.FindField("Name");
  int pop1996Index = cursor.FindField("Pop1996");
  IRow row = null;
  while ((row = cursor.NextRow()) != null)
  {
    String cityName = Convert.ToString(row.get_Value(nameIndex));
    int population = Convert.ToInt32(row.get_Value(pop1996Index));
    Console.WriteLine("{0}: {1}", cityName, population);
  }
}

' Create the QueryDef.
Dim queryDef2 As IQueryDef2 = CType(featureWorkspace.CreateQueryDef(), IQueryDef2)

' Specify the table and fields to query.
queryDef2.Tables = "Cities"
queryDef2.SubFields = "Name, Pop1996"
queryDef2.PostfixClause = "ORDER BY Pop1996 DESC"

' Execute the query.
Using comReleaser As ComReleaser = New ComReleaser()
  Dim cursor As ICursor = queryDef2.Evaluate2(True)
  comReleaser.ManageLifetime(cursor)
  Dim nameIndex As Integer = cursor.FindField("Name")
  Dim pop1996Index As Integer = cursor.FindField("Pop1996")
  Dim row As IRow = cursor.NextRow()
  While Not row Is Nothing
    Dim cityName As String = Convert.ToString(row.Value(nameIndex))
    Dim population As Integer = Convert.ToInt32(row.Value(pop1996Index))
    Console.WriteLine("{0}: {1}", cityName, population)
    row = cursor.NextRow()
  End While
End Using

// Create a reference to a TableQueryName object.
IQueryName2 queryName2 = new TableQueryNameClass();
queryName2.PrimaryKey = String.Empty;

// Specify the query definition.
queryName2.QueryDef = queryDef;

// Get a name object for the workspace.
IDataset dataset = (IDataset)workspace;
IWorkspaceName workspaceName = (IWorkspaceName)dataset.FullName;

// Cast the TableQueryName object to the IDatasetName interface and open it.
IDatasetName datasetName = (IDatasetName)queryName2;
datasetName.WorkspaceName = workspaceName;
datasetName.Name = tableName;
IName name = (IName)datasetName;

// Open the name object and get a reference to a table object.
ITable table = (ITable)name.Open();

' Create a reference to a TableQueryName object.
Dim queryName2 As IQueryName2 = New TableQueryNameClass()
queryName2.PrimaryKey = String.Empty

' Specify the query definition.
queryName2.QueryDef = queryDef

' Get a name object for the workspace.
Dim dataset As IDataset = CType(workspace, IDataset)
Dim workspaceName As IWorkspaceName = CType(dataset.FullName, IWorkspaceName)

' Cast the TableQueryName object to the IDatasetName interface and open it.
Dim datasetName As IDatasetName = CType(queryName2, IDatasetName)
datasetName.WorkspaceName = workspaceName
datasetName.Name = tableName
Dim name As IName = CType(datasetName, IName)

' Open the name object and get a reference to a table object.
Dim table As ITable = CType(name.Open(), ITable)

// Cast to the ISQLSyntax interface and get the supportedPredicates value.
ISQLSyntax sqlSyntax = (ISQLSyntax)workspace;
int supportedPredicates = sqlSyntax.GetSupportedPredicates();

// Cast the predicate value to an integer and use bitwise arithmetic to check for support.
int predicateValue = (int)esriSQLPredicates.esriSQL_BETWEEN;
int supportedValue = predicateValue & supportedPredicates;
Boolean isSupported = supportedValue > 0;

' Cast to the ISQLSyntax interface and get the supportedPredicates value.
Dim sqlSyntax As ISQLSyntax = CType(workspace, ISQLSyntax)
Dim supportedPredicates As Integer = sqlSyntax.GetSupportedPredicates()

' Cast the predicate value to an integer and use bitwise arithmetic to check for support.
Dim predicateValue As Integer = CInt(esriSQLPredicates.esriSQL_BETWEEN)
Dim supportedValue As Integer = predicateValue And supportedPredicates
Dim isSupported As Boolean = supportedValue > 0

// Cast to the ISQLSyntax interface and get the correct function name.
ISQLSyntax sqlSyntax = (ISQLSyntax)workspace;
String functionName = sqlSyntax.GetFunctionName(esriSQLFunctionName.esriSQL_UPPER);

' Cast to the ISQLSyntax interface and get the correct function name.
Dim sqlSyntax As ISQLSyntax = CType(workspace, ISQLSyntax)
Dim functionName As String = sqlSyntax.GetFunctionName(esriSQLFunctionName.esriSQL_UPPER)