How to use the ESRI OLE DB provider with a geodatabase

Summary This article explains how to perform basic operations using the ESRI OLE DB (Object Linking and Embedding database) provider.

Development licensing Deployment licensing
ArcView ArcView
ArcEditor ArcEditor
ArcInfo ArcInfo
Engine Developer Kit Engine Runtime

To use the code in this article, the following namespace must be referenced via the using (C#) or Imports (VB.NET) statements. It is also necessary to add the corresponding reference to the project in order to gain access to the API.

In this topic
 

Using the ESRI OLE DB provider with a geodatabase
In some cases, it may be useful to connect to a geodatabase directly with customized applications. ArcGIS is equipped with a minimum (level 0) OLE DB provider to facilitate this type of approach.
 
The ESRI OLE DB provider currently supports the ActiveX® Data Objects (ADO) by Microsoft. For more information on the ADO object and programming model, see the ADO Programmer's Guide on the MSDN website.
 
The ESRI OLE DB provider only works with simple feature classes (i.e. containing points, polylines, polygons) and tables.
 
The following examples show how to use the ESRI OLE DB provider in a .NET environment and establish a connection to one of the five supported ESRI workspace factories:
 

[C#] 
// For example, server = "balrog"
// userId = "vtest"
// password = "go"
// geometry = "WKB"
// instance = "5150"
// version = "SDE.DEFAULT"
public OleDbConnection ArcSdeConnectionWithString(String server, String userId,
  String password, String geometry, String instance, String version)
{
  // Build the connection string.
  String blankSdeConnectionString = 
    "Provider=ESRI.GeoDB.OleDB.1;Location={0};Data Source=sde;User Id={1};Password={2};" + "Extended Properties=workspacetype=esriDataSourcesGDB.SdeWorkspaceFactory.1;Geometry={3};Instance={4};Version={5}";
  String connectionString = String.Format(blankSdeConnectionString, server,
    userId, password, geometry, instance, version);

  // Create the connection and return it.
  OleDbConnection sdeConn = new OleDbConnection();
  sdeConn.ConnectionString = connectionString;
  return sdeConn;
}

[VB.NET] 
' For example, server = "balrog"
' userId = "vtest"
' password = "go"
' geometry = "WKB"
' instance = "5150"
' version = "SDE.DEFAULT"

Public Function ArcSdeConnectionWithString(ByVal server As String, ByVal userId As String, ByVal password As String, ByVal geometry As String, ByVal instance As String, ByVal Version As String) As OleDbConnection
    
    ' Build the connection string.
    Dim blankSdeConnectionString As String = "Provider=ESRI.GeoDB.OleDB.1;Location={0};Data Source=sde;User Id={1};Password={2};" & "Extended Properties=workspacetype=esriDataSourcesGDB.SdeWorkspaceFactory.1;Geometry={3};Instance={4};Version={5}"
    Dim connectionString As String = String.Format(blankSdeConnectionString, server, userId, password, geometry, instance, Version)
    
    ' Create the connection and return it.
    Dim sdeConn As OleDbConnection = New OleDbConnection()
    sdeConn.ConnectionString = connectionString
    
    Return sdeConn
    
End Function

[C#] 
// For example, connectionFile = "C:\\Temp\\MySdeConnection.sde"
public OleDbConnection ArcSdeConnectionWithFile(String connectionFile)
{
  // Build the connection string.
  String sdeStringTemplate = 
    "Provider=ESRI.GeoDB.OleDB.1;Extended Properties=workspacetype=" + 
    "esriDataSourcesGDB.SdeWorkspaceFactory.1;ConnectionFile={0}";
  String connectionString = String.Format(sdeStringTemplate, connectionFile);

  // Create the connection and return it.
  OleDbConnection sdeConn = new OleDbConnection();
  sdeConn.ConnectionString = connectionString;
  return sdeConn;
}

[VB.NET] 
' For example, connectionFile = "C:\Temp\MySdeConnection.sde"

Public Function ArcSdeConnectionWithFile(ByVal connectionFile As String) As OleDbConnection
    
    ' Build the connection string.
    Dim sdeStringTemplate As String = "Provider=ESRI.GeoDB.OleDB.1;Extended Properties=workspacetype=" & "esriDataSourcesGDB.SdeWorkspaceFactory.1;ConnectionFile={0}"
    Dim connectionString As String = String.Format(sdeStringTemplate, connectionFile)
    
    ' Create the connection and return it.
    Dim sdeConn As OleDbConnection = New OleDbConnection()
    sdeConn.ConnectionString = connectionString
    
    Return sdeConn
    
End Function

Connecting to other ESRI data sources

The version parameter is optional in the connection string—a list of available ArcSDE versions will be returned if it is not included.
For connections to an ArcSDE service running on an Oracle platform, this parameter is case sensitive. If the version parameter is entered incorrectly, the request to open the connection fails.
A successful connection to the ArcSDE service returns all the available databases (for SQL Server platforms) and database objects. Access to these objects is controlled by database management system (DBMS) level user permissions.

The data source parameter is only used to connect to ArcSDE on a SQL Server platform with multiple databases. If the data source parameter is included, database objects can be referenced by the schema object name; for example, "select * from us_states." If the data source parameter is omitted, the fully qualified name of the schema object must be supplied—database name.schema name.object name; for example, "select * from world.world.us_states."

For ArcSDE connection to an Oracle database, again all available schema objects will be returned. Assuming the connected user has the appropriate permissions and schema object synonyms, it is not necessary to fully qualify the database object name. Without the correct schema object synonyms, the object must be identified using schema name.object name. The user name and password required for an ArcSDE connection are the relational database management system (RDBMS) user name and password.

Personal geodatabase (Access .mdb file)
The Access workspace factory requires the path to and the name of a Microsoft Access® file (.mdb). See the following:

Provider=ESRI.GeoDB.OLEDB.1;Data Source=access file(.mdb);
Extended Properties=WorkspaceType= esriDataSourcesGDB.AccessWorkspaceFactory.1;Geometry=WKB|OBJECT
 

[C#] 
// For example, path = "C:\\arcgis\\ArcTutor\\DatabaseServers\\buildings.mdb"
// geometry = "WKB"
public OleDbConnection AccessConnection(String path, String geometry)
{
  // Build the connection string.
  String accessStringTemplate = "Provider=ESRI.GeoDB.OleDB.1;Data Source={0};" 
    + 
    "Extended Properties=workspacetype=esriDataSourcesGDB.AccessWorkspaceFactory.1;Geometry={1};";
  String connectionString = String.Format(accessStringTemplate, path, geometry);

  // Create the connection and return it.
  OleDbConnection accessConn = new OleDbConnection();
  accessConn.ConnectionString = connectionString;
  return accessConn;
}

[VB.NET] 
' For example, path = "C:\arcgis\ArcTutor\DatabaseServers\buildings.mdb"
' geometry = "WKB"

Public Function AccessConnection(ByVal Path As String, ByVal geometry As String) As OleDbConnection
    
    ' Build the connection string.
    Dim accessStringTemplate As String = "Provider=ESRI.GeoDB.OleDB.1;Data Source={0};" & "Extended Properties=workspacetype=esriDataSourcesGDB.AccessWorkspaceFactory.1;Geometry={1};"
    Dim connectionString As String = String.Format(accessStringTemplate, Path, geometry)
    
    ' Create the connection and return it.
    Dim accessConn As OleDbConnection = New OleDbConnection()
    accessConn.ConnectionString = connectionString
    
    Return accessConn
    
End Function

File geodatabase (file geodatabase .gdb file)
The file geodatabase workspace factory requires the path to and the name of a file geodatabase directory (.gdb). See the following:

Provider=ESRI.GeoDB.OLEDB.1;Data Source=file geodatabase directory(.gdb);
Extended Properties=WorkspaceType= esriDataSourcesGDB.FileGDBWorkspaceFactory.1;Geometry=WKB|OBJECT
 

[C#] 
// For example, path = @"C:\arcgis\ArcTutor\Animation in ArcMap\PopulationData.gdb"
// geometry = "WKB"
public OleDbConnection FileGdbConnection(String path, String geometry)
{
  // Build the connection string.
  String fileGdbStringTemplate = "Provider=ESRI.GeoDB.OleDB.1;Data Source={0};"
    + 
    "Extended Properties=workspacetype=esriDataSourcesGDB.FileGDBWorkspaceFactory.1;Geometry={1}";
  String connectionString = String.Format(fileGdbStringTemplate, path, geometry)
    ;

  // Create the connection and return it.
  OleDbConnection fileGdbConn = new OleDbConnection();
  fileGdbConn.ConnectionString = connectionString;
  return fileGdbConn;
}

[VB.NET] 
' For example, path = "C:\arcgis\ArcTutor\Animation in ArcMap\PopulationData.gdb"
' geometry = "WKB"

Public Function FileGdbConnection(ByVal Path As String, ByVal geometry As String) As OleDbConnection
    
    ' Build the connection string.
    Dim fileGdbStringTemplate As String = "Provider=ESRI.GeoDB.OleDB.1;Data Source={0};" & "Extended Properties=workspacetype=esriDataSourcesGDB.FileGDBWorkspaceFactory.1;Geometry={1}"
    Dim connectionString As String = String.Format(fileGdbStringTemplate, Path, geometry)
    
    ' Create the connection and return it.
    Dim fileGdbConn As OleDbConnection = New OleDbConnection()
    fileGdbConn.ConnectionString = connectionString
    
    Return fileGdbConn
    
End Function

Shapefiles
The shapefile workspace factory requires the path to a directory that contains shapefile data, not a specific shapefile. See the following:

Provider=ESRI.GeoDB.OLEDB.1;Data Source=path_to_shapefile directory;
Extended Properties=WorkspaceType=esriDataSourcesFile.ShapefileWorkspaceFactory.1;Geometry=WKB|OBJECT
 

[C#] 
// For example, path = @"C:\arcgis\ArcTutor\ArcReader and Publisher\Data"
// geometry = "WKB"
public OleDbConnection ShapefileConnection(String path, String geometry)
{
  // Build the connection string.
  String shapefileStringTemplate = 
    "Provider=ESRI.GeoDB.OleDB.1;Data Source={0};" + 
    "Extended Properties=workspacetype=esriDataSourcesFile.ShapefileWorkspaceFactory.1;Geometry={1}";
  String connectionString = String.Format(shapefileStringTemplate, path,
    geometry);

  // Create the connection and return it.
  OleDbConnection shapefileConn = new OleDbConnection();
  shapefileConn.ConnectionString = connectionString;
  return shapefileConn;
}

[VB.NET] 
' For example, path = "C:\arcgis\ArcTutor\ArcReader and Publisher\Data"
' geometry = "WKB"

Public Function ShapefileConnection(ByVal Path As String, ByVal geometry As String) As OleDbConnection
    
    ' Build the connection string.
    Dim shapefileStringTemplate As String = "Provider=ESRI.GeoDB.OleDB.1;Data Source={0};" & "Extended Properties=workspacetype=esriDataSourcesFile.ShapefileWorkspaceFactory.1;Geometry={1}"
    Dim connectionString As String = String.Format(shapefileStringTemplate, Path, geometry)
    
    ' Create the connection and return it.
    Dim shapefileConn As OleDbConnection = New OleDbConnection()
    shapefileConn.ConnectionString = connectionString
    
    Return shapefileConn
    
End Function

Coverages
The coverage workspace factory also requires a path to a coverage directory, not a specific coverage. See the following:

Provider=ESRI.GeoDB.Oledb.1;Data Source=path to A/I coverage directory;
Extended Properties=workspacetype=esriDataSourcesFile.ArcInfoWorkspaceFactory.1;Geometry=WKB|OBJECT
 

[C#] 
// For example, path = @"C:\arcgis\ArcTutor\Catalog\Yellowstone"
// geometry = "WKB"
public OleDbConnection CoverageConnection(String path, String geometry)
{
  // Build the connection string.
  String coverageStringTemplate = 
    "Provider=ESRI.GeoDB.OleDB.1;Data Source={0};" + 
    "Extended Properties=workspacetype=esriDataSourcesFile.ArcInfoWorkspaceFactory.1;Geometry={1}";
  String connectionString = String.Format(coverageStringTemplate, path,
    geometry);

  // Create the connection and return it.
  OleDbConnection coverageConn = new OleDbConnection();
  coverageConn.ConnectionString = connectionString;
  return coverageConn;
}

[VB.NET] 
' For example, path = "C:\arcgis\ArcTutor\Catalog\Yellowstone"
' geometry = "WKB"

Public Function CoverageConnection(ByVal Path As String, ByVal geometry As String) As OleDbConnection
    
    ' Build the connection string.
    Dim coverageStringTemplate As String = "Provider=ESRI.GeoDB.OleDB.1;Data Source={0};" & "Extended Properties=workspacetype=esriDataSourcesFile.ArcInfoWorkspaceFactory.1;Geometry={1}"
    Dim connectionString As String = String.Format(coverageStringTemplate, Path, geometry)
    
    ' Create the connection and return it.
    Dim coverageConn As OleDbConnection = New OleDbConnection()
    coverageConn.ConnectionString = connectionString
    
    Return coverageConn
    
End Function

ADO programming considerations for the ESRI OLE DB provider
The following recordset object.open method parameters are supported:
 
Unregistered tables in a geodatabase cannot be edited with the ESRI OLE DB provider. The table can either be registered with the geodatabase or a native OLE DB provider for the RDBMS data source can be used.
 
DBMS Insert/Update/Delete SQL operations applied directly against an ADO connection to a personal or ArcSDE geodatabase feature class or registered table are not supported and are not recommended unless there is a great deal of familiarity with the data structure at source (such as associated indexes to be maintained).
To edit registered feature classes or tables, all updates must be applied against the ADO recordset. However, such operations are supported against unregistered tables. The ADO command syntax for this operation is: connection obj.Execute(SQL statement).

Neither of the file-based data sources (shapefiles and coverages) supports Insert/Update/Delete commands applied against the ADO connection. All updates to these data sources should be undertaken via the ADO recordset.

For best results when applying a number of updates to a recordset, always use an edit transaction. This significantly improves the performance for updating. The edit transaction is opened on the connection object—connection object.begintrans.

To successfully disconnect a recordset from a connection, set the cursor location to asUseClient and set the lock type to adLockBatchOptimistic. Propagating any changes back to the data source when the recordset is reassigned to an active connection requires support for the recordset updatebatch method. Currently, the ESRI OLE DB provider does not support this functionality.

To graphically represent the tabular recordset, use an ADO/OLEDB recordset with an MSChart object in Visual Basic. For best results, set the recordset cursor location to adUseClient and set the MSChart object's data source property to be the ADO recordset.

Simple recordset display

The following code example connects to a personal geodatabase, issues a SQL select statement, and displays the results:

[C#] 
// For example, path = @"C:\Program Files\ArcGIS\DeveloperKit\SamplesNET\data\GulfOfStLawrence\data"
// shapefile = "Can_Mjr_Cities"
public void DisplayNamesFromShapefile(String path, String shapefile)
{
  // Build the connection string.
  String shapefileStringTemplate = 
    "Provider=ESRI.GeoDB.OleDB.1;Data Source={0};" + 
    "Extended Properties=workspacetype=esriDataSourcesFile.ShapefileWorkspaceFactory.1;Geometry=WKB";
  String connectionString = String.Format(shapefileStringTemplate, path);

  // Create the connection.
  OleDbConnection oleDbConnection = new OleDbConnection();
  oleDbConnection.ConnectionString = connectionString;

  // Create the command.
  String sqlQuery = String.Format("SELECT NAME FROM {0}", shapefile);
  OleDbCommand oleDbCommand = new OleDbCommand(sqlQuery, oleDbConnection);

  // Open the connection and create a reader.
  oleDbConnection.Open();
  OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader();

  // Display the values from the NAME field.
  while (oleDbDataReader.Read())
  {
    Console.WriteLine(oleDbDataReader[0].ToString());
  }
}

[VB.NET] 
' For example, path = "C:\Program Files\ArcGIS\DeveloperKit\SamplesNET\data\GulfOfStLawrence\data"
' shapefile = "Can_Mjr_Cities"

Public Sub DisplayNamesFromShapefile(ByVal Path As String, ByVal shapefile As String)
    
    ' Build the connection string.
    Dim shapefileStringTemplate As String = "Provider=ESRI.GeoDB.OleDB.1;Data Source={0};" & "Extended Properties=workspacetype=esriDataSourcesFile.ShapefileWorkspaceFactory.1;Geometry=WKB"
    Dim connectionString As String = String.Format(shapefileStringTemplate, Path)
    
    ' Create the connection.
    Dim oleDbConnection As OleDbConnection = New OleDbConnection()
    oleDbConnection.ConnectionString = connectionString
    
    ' Create the command.
    Dim sqlQuery As String = String.Format("SELECT NAME FROM {0}", shapefile)
    Dim oleDbCommand As OleDbCommand = New OleDbCommand(sqlQuery, oleDbConnection)
    
    ' Open the connection and create a reader.
    oleDbConnection.Open()
    Dim oleDbDataReader As OleDbDataReader = oleDbCommand.ExecuteReader()
    
    ' Display the values from the NAME field.
    Do While oleDbDataReader.Read()
        Console.WriteLine(oleDbDataReader(0).ToString())
    Loop
    
End Sub