OLE DB Programming Overview and Architecture

OLE DB is a COM based object model used for access to various relational data sources as well as structured, not relational data (such as folders in an email store, or a file system). This ability to access both relational and structured data is one of the major features that sets OLE DB apart from ODBC. However, there are many more differences between the two API’s. OLE DB was developed as a successor to ODBC, and has continued to gain increasing importance as the core of Microsoft’s data access strategy. (Although with the release of .NET, OLE DB’s importance has diminished some to being only the core of the unmanaged, or native, environment.) Both ODBC and OLE DB API’s provide very fast access to data. Although benchmark results vary from test to test, generally OLE DB and ODBC perform similarly well in most tasks.

The fundamental concepts of OLE DB are that of the Consumer and the Provider. In a general sense, consumers are applications or components which take and use data from a data source. A provider is a piece of software which works to present data to consumers. The ODBC equivalent of an OLE DB provider is the driver. In OLE DB, providers are written for each individual data source, as ODBC drivers are also written for each data source. An OLE DB provider exposes data to an OLE DB consumer. The architecture of the consumer-provider system is quite flexible, and in some cases a consumer can talk to a provider, which itself acts as a consumer to another provider, and so on. However, most of the time a data source is exposed by a single provider with which the consumer, usually a client application, communicates. The diagram below shows the standard flow of communication in OLE DB, in this example from a client application to a SQL Server database.

The OLE DB Service Components is a set of services provided in OLEDB32.DLL. The service components are frequently loaded along with a data provider by OLE DB consumers. The OLE DB Service Components (also known as Core Services) provide such facilities as pooling and the data links, which is discussed next. Loading the service components is also necessary to use client side cursors, which is also discussed later in this course. (The client cursor engine, however, is not in OLEDB32.DLL, but rather in MSDACE.DLL).

Data Links

Similar to the ODBC DSN facility, OLE DB does provide a way for connection information to be stored separate from the compiled client code. This mechanism, however, is entirely file-based, and does not use the registry as ODBC DSN’s can. The mechanism which allows OLE DB consumers to use connection information stored in files is called the Data Links Service, and is provided in the OLE DB Core Services. The files used to store this information are known as “UDL” files, and are saved with a “.UDL” extension.

The following demonstration will show how a UDL file can be created, and used as a troubleshooting tool to test OLE DB connectivity.

Demonstration:

1. Make sure in Windows Explorer, Tools->Folder Options, View Tab, that “Hide file extensions for known file types” is not checked.

2. Right click on Windows desktop, and select New->Text File.

3. Name the file “Test.udl”. The icon for the file should now be the special “UDL” icon.

4. Double click the file to open the Data Links dialog.

5. Click on the “Provider” tab. Select “Microsoft OLE DB Provider for SQL Server”.

6. Click on the “Connection” tab. In the first dialog box (server name) type “(local)”.

7. Click the radio button for “Use NT Integrated Security”.

8. Click on this “Advanced” and “All” tabs. These are additional options available to set on the provider at the time you connect.

9. Go back to the “Connection” tab and click “Test Connection”. You should see “Test Connection Succeeded”. This is a good way to verify if basic OLE DB connectivity works to a given data source.

10. Now click ‘OK’ to close the dialog. Then open the “Test.udl” file with notepad. What you see is the “Connection String” for the information specified in the dialogs. The Data Links interfaces in OLEDB allow a consumer to pass connection strings formatted such as this to establish a connection to a data source. You can also use these connection strings in ADO. It is a helpful trick to cut and paste the connection string from a UDL file such as this into your ADO code. (we will cover ADO in much more detail later).

Another benefit Data Links provides is that consumer applications can launch this pre-built dialog to allow users to enter connection information for a data source at run time.