Configuring Connections and Connecting to Data in Microsoft .NET Framework 3.5

Lesson 2: Connecting to Data Using Connection Objects

Now that you have learned how to create connection objects using the primary .NET data providers, let’s start using them and actually connect to some data sources. This lesson will explain how to use a connection object and open a connection to a data source. After opening the connection, you will verify that the connection is opened by examining the ConnectionState property. Once you verify that the connection state is opened, you will also cause the InfoMessage event to fire and display the message returned by the data source.

Opening and Closing Data Connections

Open and close connections using the appropriately named Open and Close methods. To open a connection to a database, the connection object must contain a connection string that points to a valid data source, as well as enough information to pass the appropriate credentials to the data source. When connections are opened and closed, you can keep an eye on the state of the connection by responding to the StateChange event. The following example shows how to open and close connections and how to update the text in a label in reaction to the StateChange event. We will also demonstrate how you can use the InfoMessage event to provide informational messages from a data source to the application. And, finally, we will demonstrate how the connection object can provide information about the data source by retrieving metadata (for example, the server version number) from an open connection.

Connection Events

Connection objects provide the StateChanged and InfoMessage events to provide information to your application regarding the status of the database and information pertaining to commands executed using a specific connection object.

• StateChanged event. This event is raised when the current state of the database changes from Open to Closed.

• InfoMessage event. In addition to monitoring the state of a connection, each connection object provides an InfoMessage event that is raised when warnings or messages are returned from the server. Informational messages are typically provided when low-severity errors are returned by the data source that the connection object is connected to. For example, SQL Server errors with a severity of 10 or less are provided to the InfoMessage event.

Lab: Practice Opening and Closing Data Connections

In this lab you will practice working with connection objects by opening and closing connections and displaying connection information to the user.

EXERCISE 1: Opening and Closing Data Connections

To demonstrate working with connection objects, perform the following steps:

1. Create a new Windows application and name it DataConnections.

2. Because Windows applications are not created with a reference to the System.Data.OracleClient namespace, from the Project menu, select the Add Reference command, locate the System.Data.OracleClient component, and click OK.

3. Add 12 buttons to the form, setting the Name and Text properties as shown in Table 5-5.

   NOTE: SIMILAR CONNECTIONS

   No matter which connection objects you use, the methods for opening and closing, handling events, and so on, are the same. Feel free to only set up the example using the connection object for the provider you are interested in working with.

   Table 5-5 Button Settings for Data Connections Form

   Name Property	Text Property
   OpenSqlButton	Open SQL
   OpenOleDbButton	Open OLE DB
   OpenOdbcButton	Open ODBC
   OpenOracleButton	Open Oracle
   CloseSqlButton	Close SQL
   CloseOleDbButton	Close OLE DB
   CloseOdbcButton	Close ODBC
   CloseOracleButton	Close Oracle
   GetSqlInfoButton	Get SQL Info
   GetOleDbInfoButton	Get OLE DB Info
   GetOdbcInfoButton	Get ODBC Info
   GetOracleInfoButton	Get Oracle Info

4. Add four labels to the form, setting the Name and Text properties as shown in Table 5-6.

   Table 5-6 Label Settings for Data Connections Form

   Name Property	Text Property
   SqlConnectionStateLabel	Closed
   OleDbConnectionStateLabel	Closed
   OdbcConnectionStateLabel	Closed
   OracleConnectionStateLabel	Closed

   Arrange the controls so the form layout looks similar to Figure 5-2.

   To create the connection objects for this lesson, you will take the code examples from Lesson 1: Creating and Configuring Connection Objects, and add them to your form as follows.

   

   Figure 5-2 Form with controls arranged in preparation for creating connection objects

5. Open the form you just created in code view.

6. Add the code to create all four connection objects so that you end up with code that looks like the following:

   IMPORTANT: CONNECTION STRINGS

   Be sure to modify the connection strings to point to your specific server and database for each provider.

   ' VB
   Imports System.Data.SqlClient
   Imports System.Data.OleDb
   Imports System.Data.Odbc
   Imports System.Data.OracleClient
   
   Public Class Form1
       ' Declare the connection objects for the four data providers
       Private WithEvents ConnectionToSql As New SqlConnection( _
           "Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated
   Security=True")
       Private WithEvents ConnectionToOleDb As New _
           System.Data.OleDb.OleDbConnection( _
               "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\DataSources\Nwind.
   mdb"";" & _
           "Persist Security Info=False")
       Private WithEvents ConnectionToOdbc As New OdbcConnection( _
           "Dsn=MS Access Database;dbq=C:DataSources\Nwind.mdb;defaultdir=C:DataSources;" & _
           "driverid=281;fil=MS Access;maxbuffersize=2048;pagetimeout=5;uid=admin")
       Private WithEvents ConnectionToOracle As New OracleConnection("Data
   Source=MyOracleDB;Integrated Security=yes;")
   End Class
   
   // C#
   using System;
   using System.Collections.Generic;
   using System.ComponentModel;
   using System.Data;
   using System.Drawing;
   using System.Text;
   using System.Windows.Forms;
   using System.Data.SqlClient;
   using System.Data.OleDb;
   using System.Data.Odbc;
   using System.Data.OracleClient;
   
   namespace DataConnections
   {
       public partial class Form1 : Form
       {
           public Form1()
           {
               InitializeComponent();
           }
   
           // Declare the connection objects for the four data providers
           private SqlConnection ConnectionToSql = new SqlConnection(
               "Data Source=.\\sqlexpress;Initial Catalog=Northwind;Integrated
   Security=True");
           private OleDbConnection ConnectionToOleDb = new
               System.Data.OleDb.OleDbConnection(
                   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DataSources\Nwind.mdb");
           private OdbcConnection ConnectionToOdbc = new OdbcConnection(
               "Dsn=MS Access Database;dbq=C:\\DataSources\\Nwind.mdb;" +
               "defaultdir=C:\\DataSources;driverid=281;fil=MS
   Access;maxbuffersize=2048;" +
               "pagetimeout=5;uid=admin");
           private OracleConnection ConnectionToOracle = new OracleConnection(
   
               "Data Source=MyOracleDB;Integrated Security=yes;");
   
       }
   }

   To open connections to a database, use the connection object’s Open method. To demonstrate this, you will call the Open method for each connection when the open buttons are clicked.

7. Create event handlers for the open buttons for each provider and add the following code, which opens the connection to the database when the open buttons are clicked:

   ' VB
   Private Sub OpenSqlServerButton_Click(ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles OpenSqlServerButton.Click
       ConnectionToSql.Open()
   End Sub
   
   Private Sub OpenOleDbButton_Click(ByVal sender As System.Object, _
       ByVal e As System.EventArgs) Handles OpenOleDbButton.Click
      ConnectionToOleDb.Open()
   End Sub
   
   Private Sub OpenOdbcButton_Click(ByVal sender As System.Object, _
       ByVal e As System.EventArgs) Handles OpenOdbcButton.Click
      ConnectionToOdbc.Open()
   End Sub
   
   Private Sub OpenOracleButton_Click(ByVal sender As System.Object, _
       ByVal e As System.EventArgs) Handles OpenOracleButton.Click
      ConnectionToOracle.Open()
   End Sub
   
   // C#
   private void OpenSqlServerButton_Click(object sender, EventArgs e)
   {
       ConnectionToSql.Open();
   }
   private void OpenOleDbButton_Click(object sender, EventArgs e)
   {
       ConnectionToOleDb.Open();
   }
   private void OpenOdbcButton_Click(object sender, EventArgs e)
   {
       ConnectionToOdbc.Open();
   }
   private void OpenOracleButton_Click(object sender, EventArgs e)
   {
       ConnectionToOracle.Open();
   }

   To close database connections, use the connection object’s Close method. Technically, you can also call the Dispose method of the connection object to close the connection, but the preferred technique is to call the Close method. It is worth noting that calling the Close method also rolls back all pending transactions and releases the connection back to the connection pool. To implement this, create event handlers for the close buttons for each provider and add code to call the Close method to the body of the handler.

8. Add the Close methods into the event handlers to close the connection to the database when the close buttons are clicked.

   ' VB
   Private Sub CloseSqlButton_Click(ByVal sender As System.Object, _
       ByVal e As System.EventArgs) Handles CloseSqlButton.Click
      ConnectionToSql.Close()
   End Sub
   
   Private Sub CloseOleDbButton_Click(ByVal sender As System.Object, _
       ByVal e As System.EventArgs) Handles CloseOleDbButton.Click
      ConnectionToOleDb.Close()
   End Sub
   
   Private Sub CloseOdbcButton_Click(ByVal sender As System.Object, _
       ByVal e As System.EventArgs) Handles CloseOdbcButton.Click
      ConnectionToOdbc.Close()
   End Sub
   
   Private Sub CloseOracleButton_Click(ByVal sender As System.Object, _
       ByVal e As System.EventArgs) Handles CloseOracleButton.Click
      ConnectionToOracle.Close()
   End Sub
   
   // C#
   private void CloseSqlButton_Click(object sender, EventArgs e)
   {
       ConnectionToSql.Close();
   }
   
   private void CloseOleDbButton_Click(object sender, EventArgs e)
   {
       ConnectionToOleDb.Close();
   }
   
   private void CloseOdbcButton_Click(object sender, EventArgs e)
   {
       ConnectionToOdbc.Close();
   }
   
   private void CloseOracleButton_Click(object sender, EventArgs e)
   {
       ConnectionToOracle.Close();
   }

   When the state of a connection changes, the value in the CurrentState property of the connection object is updated to reflect the connection’s current state. When you are opening and closing a connection, you can inspect the value in this property to verify that the connection is actually opening and closing. Each connection object raises a StateChange event that you can respond to in order to monitor the state of the connection. To populate the connection-state labels, we need to create event handlers for the StateChange events for each provider. Inside the StateChange event handlers, add code that updates the connection-state labels with the value of the connection’s CurrentState property, which is provided as an event argument.

9. Add the following code to the form, which updates the connection-state label values whenever the current state of a connection changes. Create the form load handler for C# so you can add the StateChange event handlers.

   ' VB
   Private Sub ConnectionToSql_StateChange(ByVal sender As Object, _
       ByVal e As System.Data.StateChangeEventArgs) _
       Handles ConnectionToSql.StateChange
      SqlConnectionStateLabel.Text = e.CurrentState.ToString
   End Sub
   
   Private Sub ConnectionToOleDb_StateChange(ByVal sender As Object, _
       ByVal e As System.Data.StateChangeEventArgs) _
       Handles ConnectionToOleDb.StateChange
      OleDbConnectionStateLabel.Text = e.CurrentState.ToString
   End Sub
   
   Private Sub ConnectionToOdbc_StateChange(ByVal sender As Object, _
       ByVal e As System.Data.StateChangeEventArgs) _
       Handles ConnectionToOdbc.StateChange
      OdbcConnectionStateLabel.Text = e.CurrentState.ToString
   End Sub
   
   Private Sub ConnectionToOracle_StateChange(ByVal sender As Object, _
       ByVal e As System.Data.StateChangeEventArgs) _
       Handles ConnectionToOracle.StateChange
      OracleConnectionStateLabel.Text = e.CurrentState.ToString
   End Sub
   
   // C#
   private void Form1_Load(object sender, EventArgs e)
   {
       ConnectionToSql.StateChange += new
           System.Data.StateChangeEventHandler(this.ConnectionToSql_StateChange);
       ConnectionToOleDb.StateChange += new
           System.Data.StateChangeEventHandler(this.ConnectionToOleDb_StateChange);
       ConnectionToOdbc.StateChange += new
           System.Data.StateChangeEventHandler(this.ConnectionToOdbc_StateChange);
       ConnectionToOracle.StateChange += new
           System.Data.StateChangeEventHandler(this.ConnectionToOracle_StateChange);
   }
   
   private void ConnectionToSql_StateChange(object sender,
       StateChangeEventArgs e)
   {
       SqlConnectionStateLabel.Text = e.CurrentState.ToString();
   }
   
   private void ConnectionToOleDb_StateChange(object sender,
       StateChangeEventArgs e)
   {
       OleDbConnectionStateLabel.Text = e.CurrentState.ToString();
   }
   
   private void ConnectionToOdbc_StateChange(object sender,
       StateChangeEventArgs e)
   {
       OdbcConnectionStateLabel.Text = e.CurrentState.ToString();
   }
   
   private void ConnectionToOracle_StateChange(object sender,
       StateChangeEventArgs e)
   {
       OracleConnectionStateLabel.Text = e.CurrentState.ToString();
   }

10. Press F5 to run the application and test the form to see the functionality you have so far.

11. When the form opens, click the Open SQL button and verify that the connection-state label changes to show that the connection is now open.

12. Click the Close SQL button and verify that the connection-state label changes to reflect the current state of the connection, which is now closed.

    To demonstrate use of the InfoMessage event, you need to create an event handler to process the message. To eliminate the need to create a database object that throws an error with a low severity, you can take advantage of a feature built into the SqlConnection object that allows you to capture errors with severities up to severity level 16 by setting the connection object’s FireInfoMessageEventOnUserErrors property to True before executing a method that will force an error to be thrown.

13. Add the following code, which will handle the click event for GetSqlInfoButton and the SqlConnection object’s InfoMessage event.

    Upon examination of the code in the button-click event, you can see that you are going to change the database on the connection to an invalid name, which will raise an error with severity level 11 and cause the InfoMessage event to fire. When the event fires, the code in the InfoMessage event handler opens a message box displaying the error.

    ' VB
    Private Sub GetSqlInfoButton_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles GetSqlInfoButton.Click
       ConnectionToSql.FireInfoMessageEventOnUserErrors = True
       ConnectionToSql.ChangeDatabase("Northwind1")
    End Sub
    
    Private Sub ConnectionToSql_InfoMessage(ByVal sender As Object, _
        ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs) _
        Handles ConnectionToSql.InfoMessage
       MsgBox(e.Message)
    End Sub
    
    
    // C#
    // Add this line of code into the form load handler to hook up the InfoMessage
    handler.
    ConnectionToSql.InfoMessage += new
        System.Data.SqlClient.SqlInfoMessageEventHandler(this.ConnectionToSql_
    InfoMessage);
    
    private void GetSqlInfoButton_Click(object sender, EventArgs e)
    {
        ConnectionToSql.FireInfoMessageEventOnUserErrors = true;
        ConnectionToSql.ChangeDatabase("Northwind1");
    }
    
    private void ConnectionToSql_InfoMessage(object sender,
        SqlInfoMessageEventArgs e)
    {
        MessageBox.Show(e.Message);
    }

    In addition to the previous types of information available from connection objects, you can also return some metadata from the data source you are connected to. In Lesson 1: Creating and Configuring Connection Objects, we examined the connection properties in the Properties window for the connections available in Server Explorer. This information is available at run time from the connection object as well. As an example, add a few more lines of code to your application and implement the Get Info buttons of the remaining connections to return the server versions of the data sources they are connected to.

14. Add the following code to the bottom of the form:

    ' VB
    Private Sub GetOleDbInfoButton_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles GetOleDbInfoButton.Click
       MsgBox(ConnectionToOleDb.ServerVersion.ToString, "Server Version")
    End Sub
    
    Private Sub GetOdbcInfoButton_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles GetOdbcInfoButton.Click
       MsgBox(ConnectionToOdbc.ServerVersion.ToString, "Server Version")
    End Sub
    
    Private Sub GetOracleInfoButton_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles GetOracleInfoButton.Click
       MsgBox(ConnectionToOracle.ServerVersion.ToString, "Server Version")
    End Sub
    
    // C#
    private void GetOleDbInfoButton_Click(object sender, EventArgs e)
    {
        MessageBox.Show(ConnectionToOleDb.ServerVersion.ToString(), "Server Version");
    }
    
    private void GetOdbcInfoButton_Click(object sender, EventArgs e)
    {
        MessageBox.Show(ConnectionToOdbc.ServerVersion.ToString(), "Server Version");
    }
    
    private void GetOracleInfoButton_Click(object sender, EventArgs e)
    {
        MessageBox.Show(ConnectionToOracle.ServerVersion.ToString(), "Server
    Version");
    }

    Now let’s run the application one more time to check out the additional functionality and verify that the info message and metadata is available from the connection objects.

15. Press F5 to run the application.

16. Click the Open SQL button to open the connection to the SQL server and update the connection-state label.

17. Click the Get SQL Info button to change the database to the invalid Northwind1 database and raise the InfoMessage event that will display in the message box.

    IMPORTANT: POSSIBLE INVALID OPERATION EXCEPTION

    The connection must be open or an Invalid Operation exception is thrown and the InfoMessage event does not fire.

18. Click the Close SQL button to close the connection to SQL Server and update the connection-state label.

19. Click the Open OLE DB button to open the connection to the OLE DB data source and update the connection-state label.

20. Click the Get OLE DB Info button to retrieve the server version of the OLE DB data source.

21. Click the Close OLE DB button to close the connection and update the connection-state label.

22. Save the application.

Lesson Summary

• Open connections by calling the Open method of a connection object.

• Close connections by calling the Close method of a connection object.

• Determine whether a connection is opened or closed by monitoring the StateChanged event.

• Use the InfoMessage event to process any warnings or informational messages that are returned from the server.

Lesson Review

The following questions are intended to reinforce key information presented in this lesson. The questions are also available on the companion CD if you prefer to review them in electronic form.

1. What is the minimal information needed by a connection string to open a connection to a SQL Server 2000 or SQL Server 2005 database? (Choose all that apply.)

   1. A valid data source

   2. A valid provider name

   3. A valid filepath

   4. Appropriate credentials or Integrated Security settings

2. What happens when you call the Close method of a connection object? (Choose all that apply.)

   1. The connection is destroyed.

   2. The connection is returned to the connection pool.

   3. The StateChange event is fired.

   4. All noncommitted pending transactions are rolled back.

3. What types of information does the InfoMessage event typically expose?

   1. Information regarding the current state of a connection

   2. High-severity SQL Server errors (severity 17 and above)

   3. Low-severity SQL Server errors (severity 10 and below)

   4. Network errors that are encountered when attempting to open a connection