Connecting to an OLE DB Data Source Using ADO.NET

  • Article

The .NET Framework Data Provider for OLE DB provides connectivity to data sources exposed using OLE DB and to Microsoft SQL Server version 6.x or earlier (through SQLOLEDB, the OLE DB Provider for SQL Server), using the OleDbConnection object.

For the .NET Framework Data Provider for OLE DB, the connection string format is identical to the connection string format used in ADO, with the following exceptions:

  • The Provider keyword is required.
  • The URL, Remote Provider, and Remote Server keywords are not supported.

For more information about OLE DB connection strings, see "Creating the Connection String" in the Platform SDK Documentation located in the MSDN library.

Note   The OleDbConnection object does not support setting or retrieving dynamic properties specific to an OLE DB provider. Only properties that can be passed in the connection string for the OLE DB provider are supported.

The following code example demonstrates how to create and open a connection to an OLE DB data source.

Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _
                                                       "Integrated Security=SSPI;Initial Catalog=northwind")
nwindConn.Open()
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" +
                                                "Integrated Security=SSPI;Initial Catalog=northwind");
nwindConn.Open();

You can supply connection information for an OleDbConnection using a Universal Data Link (UDL) file. Because a UDL file is an external resource to your application, you should protect UDL files using New Technologies File System (NTFS) file permissions to guard against connection information being exposed or modified. Also, be sure to provide a fully qualified path to a UDL file to ensure that the correct UDL file is used for the connection.

Because UDL files can be modified externally to any ADO.NET client program, connection strings that contain references to UDL files will be parsed every time the connection is opened. This can slow performance and it is therefore recommended, for best performance, that you use a static connection string that does not include a UDL file.

Closing the Connection

It is recommended that you always close the Connection when you are finished using it, in order for the connection to be returned to the pool. This can be done using either the Close or Dispose methods of the Connection object. Connections that are not explicitly closed might not be added or returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid.

Note   Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, see Programming for Garbage Collection.

See Also

Using .NET Framework Data Providers to Access Data | OleDbConnection Class | System.Data.OleDb Namespace