Programming Tutorials

OleDbConnection class in VB.net

By: Steven Holzner in VB.net Tutorials on 2009-02-24  

An OleDbConnection object supports a connection to an OLE DB data provider. In practice, you usually use OLE DB connections with all data providers except Microsoft's SQL Server. Note that, depending on the OLE DB data provider, not all properties of an OleDbConnection object may be supported.

A central property of connection objects is the ConnectionString property, which holds a string full of attribute/value pairs that contain data needed to log on to a data provider and choose a specific database. These attribute/value pairs are specific to the data provider you're using, and make up a list of items separated by semicolons. You can either assign a connection string to the connection's ConnectionString property, or you can pass the connection string to the connection object's constructor, like this:

Dim ConnectionString As String = "Provider=SQLOLEDB.1;Integrated " & _
Security=SSPI;Persist Security Info=False;Initial " & _
"Catalog=pubs;Packet Size=4096;Workstation ID=STEVE;" & _
"Use Encryption for Data=False"

Dim Connection1 As OleDbConnection = New OleDbConnection(ConnectionString)

If you have no idea what a connection string should look like for a specific data provider and database, use the visual tools built into Visual Basic to construct a few sample strings to that data provider, which you can either use directly in code or modify as you need. To do that, create a connection to the source you want to use, then drag a data adapter to a project's main form, which creates both data connection and data adapter objects. Then take a look at the connection object's ConnectionString property in the Properties window.

Tip The most common attribute/value pairs used in OLE DB connection strings are also supported with properties of connection objects, such as DataSource, Database, UserId, and Password, which means that when you work with a connection object, you can either set the ConnectionString property as a string, or you can set various connection properties one-by-one and let Visual Basic create the connection string for you (unless your OLE DB provider requires data not supported by the connection object's properties).

After you've created a connection object, you can open it with the Open method, and assign it to the Connection property of a command object. (To specify the SQL you want to use, you can pass that SQL to the command object's constructor.) Then you can use the command object with a data adapter. For example, you might assign the command object to the SelectCommand property of a data adapter, and you can use the data adapter's Fill method to execute that command and fill a dataset. When done with the connection, use its Close method to close it. (The connection won't be closed otherwise, even if the connection object goes out of scope.)

Tip If your application uses a number of connections, you should use connection pooling to improve performance. (Connection pooling lets you keep a cache of connections without having to create new ones all the time.) When you use the OLE DB .NET data provider, connection pooling is enabled automatically.