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.
SupplyMaster
table and using those column names, to do some string operations. So trying to get the column names by thesys.objects
table, sincesys.objects
contains the details of all table in the database.