Accessing system tables using ArcObjects?

Question

I have a case to get the maximum of year value from the table schema in GeoDatabase.

More detail: I have a table named SUPPLYMASTER it contains the columns like Supply2000, Supply2001, Supply2002, Supply2003, ... up to Supply2015 and it can be extended in future.

I can write the logic in simple SQL with the system tables as:

SELECT MAX(REPLACE([name], 'Supply', '')) AS [MaxYear] 
FROM sys.columns 
WHERE object_id = object_id('SUPPLYMASTER') AND [name] LIKE 'Supply%'

It filters the column names starting with Supply from the SUPPLYMASTER table, the replace will remove the Supply text from the filtered column names and Max will return the maximum number from the filtered values. So I will get the result as 2015.

I tried the same in ArcObjects, but when trying to access the system table, it throws error.

I tried to access the system table like the below:

Config settings as:

<appSettings>
    <add key="Database" value="DatabaseName"/>
    <add key="Server" value="ServerName"/>
    <add key="UserName" value="UserName"/>
    <add key="Password" value="Password"/>
    <add key="Version" value="DBO.DEFAULT"/>
</appSettings>

OpenTable code as:

IPropertySet connectionProperties = new PropertySetClass();
propertySet.SetProperty("Database", System.Configuration.ConfigurationManager.AppSettings["Database"]);
propertySet.SetProperty("INSTANCE", "sde:sqlserver:" + System.Configuration.ConfigurationManager.AppSettings["Server"]);
propertySet.SetProperty("USER", System.Configuration.ConfigurationManager.AppSettings["UserName"]);
propertySet.SetProperty("PASSWORD", System.Configuration.ConfigurationManager.AppSettings["Password"]);
propertySet.SetProperty("VERSION", System.Configuration.ConfigurationManager.AppSettings["Version"]);

Type factoryType = Type.GetTypeFromProgID("esriDataSourcesGDB.SdeWorkspaceFactory");
IWorkspaceFactory workspaceFactory = (IWorkspaceFactory)Activator.CreateInstance(factoryType);
IWorkspace workspace = workspaceFactory.Open(connectionProperties, 0);

IFeatureWorkspace featureWorkspace = (IFeatureWorkspace)workspace;

ITable sysTable = featureWorkspace.OpenTable("sys.columns");

IQueryFilter sysTableFilter = new QueryFilterClass();
sysTableFilter.SubFields = "name";
sysTableFilter.WhereClause = "object_id = object_id('SUPPLYMASTER') AND [name] LIKE 'Supply%'";

ICursor cursor = sysTable.Search(sysTableFilter, true);
IRow row = null;

while ((row = cursor.NextRow()) != null)
{
  // .... data processing
}

It throws the error in the line ITable sysTable = featureWorkspace.OpenTable("sys.columns");

DBMS table not found

How can I access the system tables using ArcObjects?

Note: I tried with INFORMATION_SCHEMA.COLUMNS also, it throws the same error.

Answer 1

You'll need to ask Esri why, but it is not possible to access any sys tables directly using SE_table_describe, which means that any interface which accesses the system view piecemeal will fail. The underlying ArcSDE functions can create a stream accessing the sys.columns view, and it's possible to generate a Query Layer on sys.columns, but IFeatureWorkspace.OpenTable cannot work on sys views.

You therefore have four options:

1. Fold the system view query into a valid SELECT statement as a subquery, e.g.

   SELECT name = (SELECT name FROM sys.columns WHERE ... ) FROM ...

2. Create a view which manifests a query on the system view, then use OpenTable on that

3. Create a Query Layer to implement your query

4. Use an ODBC connection to access the system view(s) in a SQL prepare.