Working with SQL workspaces

Summary This topic discusses connecting to, and obtaining information about, Structured Query Language (SQL) workspaces.

In this topic

Connecting to a SQL workspace

Connecting to a SQL workspace involves creating a SqlWorkspaceFactory and using one of the IWorkspaceFactory or IWorkspaceFactory2 Open methods to provide the workspace factory with connection properties.
The following code sample shows how to connect to an Oracle 11g instance using the IWorkspaceFactory.Open method:
[C#] 
// Create the workspace factory.
Type factoryType = Type.GetTypeFromProgID("esriDataSourcesGDB.SqlWorkspaceFactory");
IWorkspaceFactory workspaceFactory = (IWorkspaceFactory)Activator.CreateInstance
    (factoryType);

// Create the connection properties.
IPropertySet connectionProps = new PropertySetClass();
connectionProps.SetProperty("dbclient", "Oracle11g");
connectionProps.SetProperty("serverinstance", "MyServer");
connectionProps.SetProperty("authentication_mode", "DBMS");
connectionProps.SetProperty("user", "MyLogin");
connectionProps.SetProperty("password", "MyPassword");

// Open the workspace.
IWorkspace workspace = workspaceFactory.Open(connectionProps, 0);
[VB.NET] 
' Create the workspace factory.
Dim factoryType As Type = Type.GetTypeFromProgID("esriDataSourcesGDB.SqlWorkspaceFactory")
Dim workspaceFactory As IWorkspaceFactory = CType(Activator.CreateInstance(factoryType), IWorkspaceFactory)

' Create the connection properties.
Dim connectionProps As IPropertySet = New PropertySetClass()
connectionProps.SetProperty("dbclient", "Oracle11g")
connectionProps.SetProperty("serverinstance", "MyServer")
connectionProps.SetProperty("authentication_mode", "DBMS")
connectionProps.SetProperty("user", "MyLogin")
connectionProps.SetProperty("password", "MyPassword")

' Open the workspace.
Dim workspace As IWorkspace = workspaceFactory.Open(connectionProps, 0)
The connection properties recognized by the SqlWorkspaceFactory are as follows:
  • dbclient—The type of database management system (DBMS) to connect to. Valid values include the following:
    • SQLServer
    • Oracle10g
    • Oracle11g
    • PostgreSQL
    • Informix
    • DB2
    • DB2ZOS
  • serverinstance—The server where the DBMS is located. Typically, a machine name is used (that is, MyServer). However, for SQL Server Express, use the machine_name\instance_name, (that is, MyServer\SQLExpress).
  • authentication_mode—The type of authentication to use. Valid values are DBMS and operating systems authentication (OSA). DBMS is the default mode and is not required.
  • database—The name of the database to connect to. This is only required for DBMSs that support multiple databases.
  • user—The username used to log into the DBMS. This is only required for DBMS authentication.
  • password—The password used to log into the DBMS. This is only required for DBMS authentication.
The same connection properties can be provided to the workspace factory as a semicolon delimited string by using the IWorkspaceFactory2.OpenFromString method as shown in the following code sample:
[C#] 
// Create the workspace factory.
Type factoryType = Type.GetTypeFromProgID("esriDataSourcesGDB.SqlWorkspaceFactory");
IWorkspaceFactory2 workspaceFactory2 = (IWorkspaceFactory2)Activator.CreateInstance
    (factoryType);

// Build a connection string.
String[] connectionProps = 
{
    "dbclient=SQLServer", "serverinstance=MyMachine\\SqlExpress", 
        "database=MyDatabase", "authentication_mode=OSA"
};
String connString = String.Join(";", connectionProps);

// Open the workspace.
IWorkspace workspace = workspaceFactory2.OpenFromString(connString, 0);
[VB.NET] 
' Create the workspace factory.
Dim factoryType As Type = Type.GetTypeFromProgID("esriDataSourcesGDB.SqlWorkspaceFactory")
Dim workspaceFactory2 As IWorkspaceFactory2 = CType(Activator.CreateInstance(factoryType), IWorkspaceFactory2)

' Build a connection string.
Dim connectionProps As String() = _
                              { _
                              "dbclient=SQLServer", _
                              "serverinstance=MyMachine\SqlExpress", _
                              "database=MyDatabase", _
                              "authentication_mode=OSA" _
                              }
Dim connString As String = String.Join(";", connectionProps)

' Open the workspace.
Dim workspace As IWorkspace = workspaceFactory2.OpenFromString(connString, 0)
Connection properties can also be persisted as a connection file in the same way that ArcSDE connection properties can be saved as an SDE file. To create a new connection file, use the IWorkspaceFactory.Create method as shown in the following code:
[C#] 
workspaceFactory.Create(@"C:\MyConnections", "QueryLayerConn", connectionProps, 0);
[VB.NET] 
workspaceFactory.Create("C:\MyConnections", "QueryLayerConn", connectionProps, 0)
Once a connection file has been created, the OpenFromFile method can be used to open a workspace using the persisted connection properties as shown in the following code:
[C#] 
IWorkspace workspace = workspaceFactory.OpenFromFile(@
    "C:\MyConnections\QueryLayerConn.qcf", 0);
[VB.NET] 
Dim workspace As IWorkspace = workspaceFactory.OpenFromFile("C:\MyConnections\QueryLayerConn.qcf", 0)

Querying the tables of a SQL workspace

Once a workspace has been opened, the ISqlWorkspace interface can be used to query the database for a list of tables. These tables can be used to construct SQL queries, which can then be passed to the ISqlWorkspace.GetQueryDescription and ISqlWorkspace.OpenQueryCursor methods.
The following code sample shows how to display a list of table names in a console application:
[C#] 
// Display each table name to the console.
ISqlWorkspace sqlWorkspace = (ISqlWorkspace)workspace;
IStringArray tableNames = sqlWorkspace.GetTables();
for (int i = 0; i < tableNames.Count; i++)
{
    String tableName = tableNames.get_Element(i);
    Console.WriteLine(tableName);
}
[VB.NET] 
' Display each table name to the console.
Dim sqlWorkspace As ISqlWorkspace = CType(workspace, ISqlWorkspace)
Dim tableNames As IStringArray = sqlWorkspace.GetTables()
Dim i As Integer = 0
For i = 0 To tableNames.Count - 1
    Dim tableName As String = tableNames.Element(i)
    Console.WriteLine(tableName)
Next
The typical approach to finding the datasets in a workspace—using the IWorkspace.DatasetNames property—behaves slightly differently in a SQL workspace than in other types of workspaces. The dataset names returned from a SQL workspace reflect those tables that can be opened as a table or a feature class without a modified query description. For more information about query descriptions, see Working with query classes.
The ISqlWorkspace.GetColumns method can be used to query the properties of each table's columns. The properties returned by this method (through a set of outbound array parameters) include:
  • Column names
  • Column types
  • Whether columns can contain null values
  • Column sizes (if applicable)
  • Column scales (if applicable)
  • Column precisions (if applicable)
See the following example:
[C#] 
// Prepare the outbound variables then call GetColumns.
IStringArray columnNames = null;
IStringArray columnTypes = null;
IVariantArray areNullable = null;
ILongArray columnSizes = null;
ILongArray columnPrecisions = null;
ILongArray columnScales = null;
sqlWorkspace.GetColumns("Parcels", out columnNames, out columnTypes, out areNullable,
    out columnSizes, out columnPrecisions, out columnScales);

// The arrays are all the same size, so this is safe.
for (int i = 0; i < columnNames.Count; i++)
{
    Console.WriteLine("Name: {0}", columnNames.get_Element(i));
    Console.WriteLine("Type: {0}", columnTypes.get_Element(i));
    Console.WriteLine("Is Nullable: {0}", areNullable.get_Element(i));
    Console.WriteLine("Size: {0}", columnSizes.get_Element(i));
    Console.WriteLine("Precision: {0}", columnPrecisions.get_Element(i));
    Console.WriteLine("Scale: {0}", columnScales.get_Element(i));
}
[VB.NET] 
' Prepare the outbound variables then call GetColumns.
Dim columnNames As IStringArray = Nothing
Dim columnTypes As IStringArray = Nothing
Dim areNullable As IVariantArray = Nothing
Dim columnSizes As ILongArray = Nothing
Dim columnPrecisions As ILongArray = Nothing
Dim columnScales As ILongArray = Nothing
sqlWorkspace.GetColumns("Parcels", columnNames, columnTypes, areNullable, _
                        columnSizes, columnPrecisions, columnScales)

' The arrays are all the same size, so this is safe.
Dim i As Integer = 0
For i = 0 To columnNames.Count - 1
    Console.WriteLine("Name: {0}", columnNames.Element(i))
    Console.WriteLine("Type: {0}", columnTypes.Element(i))
    Console.WriteLine("Is Nullable: {0}", areNullable.Element(i))
    Console.WriteLine("Size: {0}", columnSizes.Element(i))
    Console.WriteLine("Precision: {0}", columnPrecisions.Element(i))
    Console.WriteLine("Scale: {0}", columnScales.Element(i))
Next


See Also:

Working with query classes
Working with query cursors
Query classes and cursors



To use the code in this topic, reference the following assemblies in your Visual Studio project. In the code files, you will need using (C#) or Imports (VB .NET) directives for the corresponding namespaces (given in parenthesis below if different from the assembly name):
Development licensing Deployment licensing
ArcView ArcView
ArcEditor ArcEditor
ArcInfo ArcInfo
Engine Developer Kit Engine Runtime