DataSourcesOleDB

Supported with:
  • Engine
  • ArcView
  • ArcEditor
  • ArcInfo
  • Server
Library dependencies: Version, System, SystemUI, Geometry, GraphicsCore, Display, Server, Output, Geodatabase, GISClient, DataSourcesFile, DataSourcesGDB

Additional library information: Contents, Object Model Diagram

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):
The DataSourcesOleDB library contains the implementation of the Geodatabase application programming interface (API) for the Microsoft Object Linking and Embedding database (OLE DB) data sources, text file data sources, and Excel data sources. This library also provides a way to use ActiveX Data Objects (ADO) to connect to an existing workspace. The DataSourcesOleDB library is not extended by developers. It is only available on the Microsoft Windows operating system (OS).

See the following sections for more information about this namespace:

About DataSourcesOleDB
The most significant classes in the DataSourcesOleDB library are workspace factories and the FdoAdoConnection class. A workspace factory is a dispenser of workspaces and allows a client to connect to a workspace using a set of connection properties or a path in the file system. A workspace is a container of datasets and in the context of OLEDB data sources, this means tables. The DataSourcesOleDB library has workspace factories for connecting to OLE DB data sources, text files, and Excel workbooks. Workspaces from factories in this library are read-only, as are their datasets.
Workspace factories are singleton objects (a singleton object can only be instantiated once in a process). Each workspace factory maintains a pool of currently connected, active workspaces that are referenced by the application. Connection properties are specified using a PropertySet object and in the case of OLE DB workspaces, can be saved to a connection file.
The code examples in this topic instantiate workspace factories using the Activator.CreateInstance method rather than the new keyword. This is because workspace factories are singleton Component Object Model (COM) classes. For more information, see Interacting with singleton objects.

OleDBWorkspaceFactory
The OleDBWorkspaceFactory class is used to connect to OLE DB data sources given a connection string.
The OleDBWorkspaceFactory can be used to open a wide range of data sources, including relational databases that might have spatial types. Workspaces returned from this type of factory are strictly non-spatial. When connecting to spatial databases, use the SqlWorkspaceFactory class. For more information, see Working with SQL workspaces.
The following code example shows how to open a table from a Structured Query Language (SQL) Express instance:
[C#] 
// Create an OLEDB workspace factory.
Type factoryType = Type.GetTypeFromProgID(
    "esriDataSourcesOleDB.OLEDBWorkspaceFactory");
IWorkspaceFactory workspaceFactory = (IWorkspaceFactory)Activator.CreateInstance
    (factoryType);

// Create a property set to connect to a SQL Express instance.
IPropertySet propertySet = new PropertySetClass();
propertySet.SetProperty("CONNECTSTRING", @
    "Provider=SQLOLEDB;Data Source=tangerine\sqlexpress;Initial Catalog=Kashmir;Integrated Security=SSPI;");

// Open a workspace using the property set.
IWorkspace workspace = workspaceFactory.Open(propertySet, 0);

// Open a table from the workspace.
IFeatureWorkspace featureWorkspace = (IFeatureWorkspace)workspace;
ITable table = featureWorkspace.OpenTable("dbo.BlackMountain");
[VB.NET] 
' Create an OLEDB workspace factory.
Dim factoryType As Type = Type.GetTypeFromProgID("esriDataSourcesOleDB.OLEDBWorkspaceFactory")
Dim workspaceFactory As IWorkspaceFactory = CType(Activator.CreateInstance(factoryType), IWorkspaceFactory)

' Create a property set to connect to a SQL Express instance.
Dim propertySet As IPropertySet = New PropertySetClass()

propertySet.SetProperty("CONNECTSTRING", "Provider=SQLOLEDB;Data Source=tangerine\sqlexpress;Initial Catalog=Kashmir;Integrated Security=SSPI;")
    
    ' Open a workspace using the property set.
    Dim workspace As IWorkspace = workspaceFactory.Open(propertySet, 0)
    
    ' Open a table from the workspace.
    Dim featureWorkspace As IFeatureWorkspace = CType(workspace, IFeatureWorkspace)
    Dim table As ITable = featureWorkspace.OpenTable("dbo.BlackMountain")

TextFileWorkspaceFactory
The TextFileWorkspaceFactory class is used to open text files from the file system as tables. The following code example shows how to open a directory as a workspace, then open a contained Comma Separated Value (CSV) file as a table:
[C#] 
// Create a text file workspace factory.
Type factoryType = Type.GetTypeFromProgID(
    "esriDataSourcesOleDB.TextFileWorkspaceFactory");
IWorkspaceFactory workspaceFactory = (IWorkspaceFactory)Activator.CreateInstance
    (factoryType);

// Open a directory of CSV files using its path.
IWorkspace workspace = workspaceFactory.OpenFromFile(@"C:\Data\CSV", 0);

// Open a CSV file as a table.
IFeatureWorkspace featureWorkspace = (IFeatureWorkspace)workspace;
ITable table = featureWorkspace.OpenTable("Levee.csv");
[VB.NET] 
' Create a text file workspace factory.
Dim factoryType As Type = Type.GetTypeFromProgID("esriDataSourcesOleDB.TextFileWorkspaceFactory")
Dim workspaceFactory As IWorkspaceFactory = CType(Activator.CreateInstance(factoryType), IWorkspaceFactory)

' Open a directory of CSV files using its path.
Dim workspace As IWorkspace = workspaceFactory.OpenFromFile("C:\Data\CSV", 0)

' Open a CSV file as a table.
Dim featureWorkspace As IFeatureWorkspace = CType(workspace, IFeatureWorkspace)
Dim table As ITable = featureWorkspace.OpenTable("Levee.csv")

ExcelWorkspaceFactory
The ExcelWorkspaceFactory class is used to read data from Microsoft Excel files. The following code example shows how to open an Excel workbook as a workspace, then open a contained worksheet as a table; the workbook name is suffixed with a dollar sign ($) symbol:
[C#] 
// Create an Excel workspace factory.
Type factoryType = Type.GetTypeFromProgID(
    "esriDataSourcesOleDB.ExcelWorkspaceFactory");
IWorkspaceFactory workspaceFactory = (IWorkspaceFactory)Activator.CreateInstance
    (factoryType);

// Open an Excel workbook as a workspace.
IWorkspace workspace = workspaceFactory.OpenFromFile(@"C:\Data\Excel\Ocean.xls", 0);

// Open a worksheet as a table.
IFeatureWorkspace featureWorkspace = (IFeatureWorkspace)workspace;
ITable table = featureWorkspace.OpenTable("Ozone$");
[VB.NET] 
' Create an Excel workspace factory.
Dim factoryType As Type = Type.GetTypeFromProgID("esriDataSourcesOleDB.ExcelWorkspaceFactory")
Dim workspaceFactory As IWorkspaceFactory = CType(Activator.CreateInstance(factoryType), IWorkspaceFactory)

' Open an Excel workbook as a workspace.
Dim workspace As IWorkspace = workspaceFactory.OpenFromFile("C:\Data\Excel\Ocean.xls", 0)

' Open a worksheet as a table.
Dim featureWorkspace As IFeatureWorkspace = CType(workspace, IFeatureWorkspace)
Dim table As ITable = featureWorkspace.OpenTable("Ozone$")

FdoAdoConnection
Use the FdoAdoConnection object to create or connect to an ADO connection object from an existing workspace object. The following code example shows how to create an ADO connection for a file geodatabase workspace:
[C#] 
// Open a file geodatabase workspace.
Type factoryType = Type.GetTypeFromProgID(
    "esriDataSourcesGDB.FileGDBWorkspaceFactory");
IWorkspaceFactory workspaceFactory = (IWorkspaceFactory)Activator.CreateInstance
    (factoryType);
IWorkspace workspace = workspaceFactory.OpenFromFile(@"C:\Data\California.gdb", 0);

// Create an ADO connection for the workspace.
IFDOToADOConnection fdoToadoConnection = new FdoAdoConnectionClass();
ADODB.Connection adoConnection = (ADODB.Connection)
    fdoToadoConnection.CreateADOConnection(workspace);

// Query the workspace.
ADODB.Recordset recordSet = new ADODB.Recordset();
recordSet.Open("Select * from Cities", adoConnection,
    ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, 0);
[VB.NET] 
' Open a file geodatabase workspace.
Dim factoryType As Type = Type.GetTypeFromProgID("esriDataSourcesGDB.FileGDBWorkspaceFactory")
Dim workspaceFactory As IWorkspaceFactory = CType(Activator.CreateInstance(factoryType), IWorkspaceFactory)
Dim workspace As IWorkspace = workspaceFactory.OpenFromFile("C:\Data\California.gdb", 0)

' Create an ADO connection for the workspace.
Dim fdoToadoConnection As IFDOToADOConnection = New FdoAdoConnectionClass()
Dim adoConnection As ADODB.Connection = CType(fdoToadoConnection.CreateADOConnection(workspace), ADODB.Connection)

' Query the workspace.
Dim recordSet As ADODB.Recordset = New ADODB.Recordset()
recordSet.Open("Select * from Cities", adoConnection, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, 0)