Home > Sample chapters

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.

    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

    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:

    ' 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.

  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