Using Connection Objects

OleDbConnection Object Reference

While writing this book I've wrestled with the structure of the reference-based Chapters, 3 through 8. I wanted to provide developers with a complete guide to ADO.NET but also wanted the text to flow smoothly. At this point I expect that you understand the role of the Connection object in database applications and are comfortable using its major features. The following section is intended to fill in some of the blanks and cover the less-frequently-used but still important features of the object.

Read it now or save it for later. This reference material does not introduce new characters, nor does it contain any unexpected plot twists that will affect your ability to understand subsequent chapters.

Without further ado, let's take a closer look at the properties and methods of the OleDbConnection object.

Properties of the OleDbConnection Object

The only property on the OleDbConnection class that's not read-only is the ConnectionString property. This property accepts a connection string that the OleDbConnection will use to try to connect to your data source when you call the Open method. Table 3-1 contains the properties you'll use most often when working with an OleDbConnection object.

Table 3-1 Commonly Used Properties of the OleDbConnection Object

Property	Data Type	Description
ConnectionString	String	Controls how the OleDbConnection object will connect to your data source
ConnectionTimeout	Int32	Specifies how long, in seconds, the OleDbConnection will try to connect to your data source (read-only)
Database	String	Returns the name of the database you are, or will be, connected to (read-only)
DataSource	String	Returns the location of the database you are, or will be, connected to (read-only)
Provider	String	Returns the name of the OLE DB provider the OleDbConnection will use to connect to your data source (read-only)
ServerVersion	String	Returns the version of your data source (read-only)
State	ConnectionState	Indicates the current state of the OleDbConnection (open or closed) (read-only)

ConnectionString Property

The ConnectionString property controls how the Connection object will attempt to connect to your data source. You can set this property only when your Connection is not connected to your data source. When it's connected to your data source, the property is read-only.

ConnectionTimeout Property

The ConnectionTimeout property indicates the amount of time, in seconds, that the OLE DB provider will wait on an attempt to connect to your data source before timing out.

This property is read-only because not all OLE DB providers support this feature. For example, the Microsoft OLE DB provider for SQL Server supports this feature, while the Microsoft OLE DB providers for Jet and Oracle databases do not.

So how do you tell the OLE DB provider how you long you want to wait before timing out? By using the connection string attribute Connect Timeout. Here's an example of a connection string that uses the Microsoft OLE DB Provider for SQL Server and the Connect Timeout attribute:

"Provider=SQLOLEDB;Data Source=(local)\NetSDK;Initial Catalog=Northwind;     Trusted_Connection=Yes;Connect Timeout=11;"

If you set a value for the Connect Timeout attribute in your connection string and the OLE DB provider you're using doesn't support this feature, you'll throw an exception when you call the Open method on the OleDbConnection.

Database and DataSource Properties

The terms database and data source are often used interchangeably, as they are in this book. But the Connection object exposes each as a separate property. So how do they differ?

In our discussion of connection strings, you might have noticed that each connection string used the Data Source attribute followed by the location of the data source we wanted to connect to. Similarly, the Connection object's DataSource property contains the location of the data source referenced in our connection string. If you're working with a server-based data store, such as SQL Server or Oracle, the DataSource property will return the name of the machine acting as the server. For file-based databases, such as Access, the DataSource property will return the location of the data file.

When we discussed connection strings that use the SQL Server OLE DB provider, you learned how to specify an instance of SQL Server in a connection string as part of the Data Source attribute. If you use a connection string that includes information specifying the desired instance of SQL Server, that information will be returned in the DataSource property, just as it appears in the connection string.

So, what information does the Database property return? This property is designed for data sources that support multiple databases, such as SQL Server. When we examined SQL Server connection strings, we specified the database on the server we wanted to connect to in the Initial Catalog attribute of the connection string.

	The SQL Server ODBC driver supports the same functionality in the Database attribute. For the SQL Server OLE DB provider, the two attributes are interchangeable.

Provider Property

The OleDbConnection class exposes the Provider property to let you determine the OLE DB provider specified in the connection string. The OdbcConnection property exposes a similar property, Driver, which returns the name of the ODBC driver specified in the connection string. Because the SqlConnection class supports connecting only to SQL Server databases, there is no need for such a property.

ServerVersion Property

Most database systems introduce new features with each successive version. SQL Server 2000, for example, supports returning the results of a query as XML. For this reason, you can check the ServerVersion property to ensure you don't make unsupported calls to a server. The SourceVersion property returns a string containing the version of the database to which you're connected. Developers with a SQL Server background might be familiar with the SELECT @@Version query. The SourceVersion property returns a subset of the information returned by SELECT @@Version—the database's version number.

Let's say your application communicates with different SQL Server databases, using the OLE DB, ODBC or SQL .NET data provider, and there are certain queries that you can run only against servers running SQL Server 2000 or later. In such a situation, you can use the following code to determine whether to run your query:

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() If cn.ServerVersion >= "08" Then     'Run your query here. End If

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); if (cn.ServerVersion >= "08") {     //Run your query here. }

State Property

The Connection object's State property returns the current state of the connection as a member of the ConnectionState enumeration in the System.Data namespace. Table 3-2 contains the constants, values, and descriptions of possible connection states.

Table 3-2 Connection State Constants

Constant	Value	Description
Broken	16	The connection is broken. A connection is considered broken if it has been opened but is then unable to communicate with the data store for whatever reason (such as network problems or the server being reset). Not used in initial release of ADO.NET.
Closed	0	The connection is closed.
Connecting	2	The connection is in the process of connecting. Not used in initial release of ADO.NET.
Executing	4	The connection is executing a query. Not used in initial release of ADO.NET.
Fetching	8	The connection is busy fetching data. Not used in initial release of ADO.NET.
Open	1	The connection is open.

The ConnectionState enumeration contains a number of values that aren't used in the initial release of ADO.NET. Currently, the Connection object's State property will return either Open or Closed. Future releases might support combinations of these values to indicate that a connection is open but is currently executing a query.

You can use the Connection object's StateChange event to determine when the value of the State property changes.

Methods of the OleDbConnection Object

Table 3-3 lists the OleDbConnection object's methods. Methods such as GetType and ToString that are common to most objects in the .NET Framework are omitted. Take a look at the table to familiarize yourself with these methods. I discuss each one following the table.

Table 3-3 Commonly Used Methods of the OleDbConnection Object

Method	Description
BeginTransaction	Begins a transaction on the connection
ChangeDatabase	Changes the current database on an open connection
Close	Closes the connection
CreateCommand	Creates an OleDbCommand for the current connection
GetOleDbSchemaTable	Retrieves schema information from the data source
Open	Opens the connection
ReleaseObjectPool	Releases the connection from the OLE DB connection pool

BeginTransaction Method

If you want to start a transaction on your connection—to lock data or to ensure that you can commit or roll back a series of changes to your data store—call the BeginTransaction method on the Connection object. This method returns a new Transaction object, a class we'll discuss in depth in Chapter 10 when we discuss updating your database.

	Developers who've used the connection objects in ADO, RDO, or DAO might expect methods of the Connection object to commit or roll back a transaction. In the ADO.NET object model, the BeginTransaction method generates a new Transaction object. When you want to commit or roll back a transaction, call Commit or Rollback on the Transaction object.

Because BeginTransaction creates a new transaction, associates it with the connection that created it, and initializes the transaction, using this method of the Connection object can simplify your code slightly. The following code snippets are functionally equivalent:

Visual Basic .NET

Dim txn As OleDb.OleDbTransaction = cn.BeginTransaction()

is equivalent to

Dim txn As New OleDb.OleDbTransaction() txn.Connection = cn txn.Begin()

Visual C# .NET

OleDbTransaction txn = cn.BeginTransaction();

is equivalent to

OleDbTransaction txn = new OleDbTransaction(); txn.Connection = cn; txn.Begin();

ChangeDatabase Method

Earlier in the chapter, we talked about SQL Server's ability to support multiple databases on a single server. If you're working with SQL Server, you can change the database you're communicating with by executing a query such as this one:

USE Northwind

ADO.NET also offers a simpler method for changing the database. The Connection object has a ChangeDatabase method that simplifies the process. The following code snippets are equivalent:

Visual Basic .NET

Dim cn As New OleDbConnection(strConn) cn.Open() ... cn.ChangeDatabase("Northwind")

is equivalent to

Dim cn As New OleDbConnection(strConn) cn.Open() ... Dim cmd As OleDbCommand = cn.CreateCommand() cmd.CommandText = "USE Northwind" cmd.ExecuteNonQuery()

Visual C# .NET

OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); ... cn.ChangeDatabase("Northwind");

is equivalent to

OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); ... OleDbCommand cmd = cn.CreateCommand(); cmd.CommandText = "USE Northwind"; cmd.ExecuteNonQuery();

Close Method

To close a Connection, you call the object's Close method. Remember that if you're using connection pooling, you're simply sending the physical connection to your data source to the pool.

Calling the Close method on a Connection object that's already marked as closed will not generate an exception.

CreateCommand Method

You can also create new Command objects by using the Connection class's CreateCommand method. This method accepts no arguments and returns a new Command object whose Connection property is set to the Connection object that created it.

The following code snippets are functionally equivalent:

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) Dim cmd As OleDb.OleDbCommand = cn.CreateCommand()

is equivalent to

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) Dim cmd As New OleDb.OleDbCommand() cmd.Connection = cn

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); OleDbCommand cmd = cn.CreateCommand();

is equivalent to

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = cn;

GetOleDbSchemaTable Method

The OleDbConnection lets you retrieve schema information about your database through the GetOleDbSchemaTable method. You supply a value from the OleDbSchemaGuid enumeration to specify the type of schema information you want, such as tables, columns, and procedures.

The GetOleDbSchemaTable method also requires a parameter called Restrictions, which acts as a filter on the schema information returned by the method. For example, rather than retrieving information for all columns in your database, you can retrieve information for just the columns in a particular table. The Restrictions parameter contains an array of values. Each schema type allows a different set of restrictions.

If you want to retrieve information about all of the columns in all of the tables in your database, you should omit the Restrictions parameter, as shown in the following code:

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() Dim tbl As DataTable tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); DataTable tbl; tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

However, if you want to retrieve the columns from just a specific table, use the Restrictions parameter and supply the name of the table whose columns you want to examine. The MSDN documentation for the Tables member of the OleDbSchemaGuid enumeration states that the Restrictions array for the member should have the following structure:

{"TABLE_CATALOG", "TABLE_SCHEMA", "TABLE_NAME", "COLUMN_NAME"}

You can use the following code to retrieve just the columns from the Customers table:

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() Dim objRestrictions As Object() objRestrictions = New Object() {Nothing, Nothing, "Customers", Nothing} Dim tbl As DataTable tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objRestrictions)

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); object[] objRestrictions; objRestrictions = new object[] {null, null, "Customers", null}; DataTable tbl; tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objRestrictions);

For information on the Restrictions parameter for a particular OleDbSchemaGuid value, see the MSDN documentation for that value.

The GetOleDbSchemaTable method returns a DataTable (a structure we'll examine in detail in Chapter 6), which contains the schema information you requested. The structure of the DataTable that the method returns will depend on the type of schema you requested. You can use the following code to loop through the rows in the schema table of column information that we just retrieved:

Visual Basic .NET

... Dim row as DataRow tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objRestrictions) Console.WriteLine("Columns in Customers table:") For Each row In tbl.Rows     Console.WriteLine(vbTab & row("COLUMN_NAME").ToString()) Next row

Visual C# .NET

... tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objRestrictions); Console.WriteLine("Columns in Customers table:"); foreach(DataRow row in tbl.Rows)     Console.WriteLine("\t" + row["COLUMN_NAME"].ToString());

You can build a fairly simple application that uses the GetOleDbSchemaTable method to display schema information about your database (such as tables, views, and stored procedures) similar to how Server Explorer displays that information.

The GetOleDbSchemaTable method relies on functionality in the OLE DB provider that your OleDbConnection is using. Not all OLE DB providers support all schema methods. If you request a schema that your OLE DB provider doesn't support, you'll throw a trappable exception.

Open Method

To open a connection to your data source, call the Connection object's Open method. The Connection object will attempt to connect to your data source based on the information provided in the object's ConnectionString property. If the attempt to connect fails, the Connection object will throw an exception.

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) Try     cn.Open() Catch ex As Exception     Console.WriteLine("Attempt to connect failed!" & vbCrLf & ex.Message) End Try

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); try  {     cn.Open(); } catch (Exception ex)  {     Console.WriteLine("Attempt to connect failed!\n" + ex.Message); }

Calling the Open method on a Connection object that's already opened will close and then reopen the connection. If connection pooling is enabled, this scenario might generate an additional connection to your data source. When the initial connection is closed, it's sent to the pool. However, because pooling is handled on another thread, the initial connection might not be available when the Connection object requests a connection to the data source based on the connection string.

ReleaseObjectPool Method

The ReleaseObjectPool method can help you manage OLE DB connection pooling within your components. Calling ReleaseObjectPool on your connection, or on the OleDbConnection class itself, releases your reference to the pool.

In all honesty, you'll rarely need to use this method. With the Visual Studio .NET beta, most developers wanted to use this method to truly close a physical connection to the data store rather than to simply send the physical connection to the pool. In such cases, you're better off creating your connection so it won't be pooled in the first place. To do that, include the following snippet in the connection string:

OLE DB Services=-4;

If you use this attribute and value in your connection string, your connection to the data source will be closed rather than pooled when you call the Close method on the OleDbConnection class.

Events of the OleDbConnection Object

The OleDbConnection object exposes two events, InfoMessage and StateChange, as described in Table 3-4.

Table 3-4 Events of the OleDbConnection Object

Event	Description
InfoMessage	Fires when the connection receives an informational message from the data source
StateChange	Fires when the State property of the connection changes

InfoMessage Event

Some database systems, such as SQL Server, support informational messages. SQL Server lets you send messages to the client via the PRINT command. These messages are not returned as errors, nor are they included with the results of a query.

You can use the Connection object's InfoMessage event to trap for such messages. The following code snippet shows how you can log informational messages.

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) AddHandler cn.InfoMessage, AddressOf cn_InfoMessage cn.Open() With cn.CreateCommand()     .CommandText = "PRINT 'Hello ADO.NET!'"     .ExecuteNonQuery() End With Public Sub cn_InfoMessage(ByVal sender As Object, _            ByVal e As System.Data.OleDb.OleDbInfoMessageEventArgs)     Console.WriteLine("InfoMessage event occurred")     Console.WriteLine(vbTab & "Message received: " & e.Message) End Sub

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.InfoMessage += new OleDbInfoMessageEventHandler(cn_InfoMessage); cn.Open(); OleDbCommand cmd = cn.CreateCommand(); cmd.CommandText = "PRINT 'Hello ADO.NET'"; cmd.ExecuteNonQuery(); static void cn_InfoMessage(object sender, OleDbInfoMessageEventArgs e) {     Console.WriteLine("InfoMessage event occurred");     Console.WriteLine("\tMessage received: " + e.Message); }

	SQL Server also supports generating informational messages using the RAISERROR command. Errors created with this command are treated as informational messages if the error's severity level is 10 or below. For more information, see SQL Server Books Online.

Visual Basic .NET code snippets involving events

Visual Basic .NET provides two ways for you to add code to handle the events that an object exposes. The first way, which we'll use throughout this book, is by using the AddHandler statement. There's also a way to add code to handle events that involves less typing.

In Figure 3-13 you see the Visual Basic .NET code editor. There's a variable named cn with module-level scope. The Dim statement includes the WithEvents keyword. When you declare a variable using this keyword, you can use Visual Basic .NET to easily create the procedures to handle the object's events.

Just above the code are two drop-down list boxes. The list box on the left lists the code module and any object variables with module scope that expose events. You'll see that the OleDbConnection object has been selected in this list box. When you select an object variable that exposes events in the list box on the left, the list box on the right lists the events that the object exposes.

Once you select one of the available events, Visual Basic .NET creates a procedure with the appropriate signature that you can use to handle the event of that object.

While this is an extremely handy feature for developers, it poses a challenge to authors. Object variables declared with the WithEvents keyword must be at module-level scope, which means I need to denote that anytime I declare variables in code. The code snippets would need to look like this:

'At module level Dim WithEvents cn As OleDbConnection Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" cn = New OleDbConnection(strConn) AddHandler cn.StateChange, AddressOf cn_StateChange cn.Open() cn.Close() Public Sub cn_StateChange(ByVal sender As Object, _                           ByVal e As System.Data.StateChangeEventArgs)     Console.WriteLine("StateChange event occurred")     Console.WriteLine(vbTab & "From " & e.OriginalState.ToString)     Console.WriteLine(vbTab & "To " & e.CurrentState.ToString) End Sub

Using AddHandler simplifies the code snippets slightly. More important, if you're working with the electronic version of the book, code snippets that use AddHandler are a little easier to cut and paste.

Figure 3-13

Adding code to handle events in Visual Basic .NET

StateChange Event

The Connection object's StateChange event fires whenever the value of its State property changes. This event can prove handy if you display the current state of your connection in, say, a status bar at the bottom your application's main form.

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) AddHandler cn.StateChange, AddressOf cn_StateChange cn.Open() cn.Close() Public Sub cn_StateChange(ByVal sender As Object, _                           ByVal e As System.Data.StateChangeEventArgs)     Console.WriteLine("StateChange event occurred")     Console.WriteLine(vbTab & "From " & e.OriginalState.ToString)     Console.WriteLine(vbTab & "To " & e.CurrentState.ToString) End Sub

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.StateChange += new StateChangeEventHandler(cn_StateChange); cn.Open(); cn.Close(); static void cn_StateChange(object sender, StateChangeEventArgs e) {     Console.WriteLine("StateChange event occurred");     Console.WriteLine("\tFrom " & e.OriginalState.ToString());     Console.WriteLine("\tTo " & e.CurrentState.ToString()); }