Lab Practice Opening and Closing Data Connections

In this lab you will practice working with connection objects by opening and closing the connections and displaying connection information back 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 and locate the System.Data.OracleClient component and click OK.

3. Add twelve 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, is 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

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

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.

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

5| Data Connections Sample

Get SQL Info 1

Close SQL

Get OLE DB Info

Close OLE DB

GetODBC Info

Close ODBC

Get Oracle Info |

Close Oracle |

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

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

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.

Imports System.Data.SqlClient

Imports System.Data.OleDb

Imports System.Data.Odbc

Imports System.Data.OracleClient

Public Class Forml

' Declare the connection objects for the four data providers Private WithEvents ConnectionToSql As New Sq1Connection( _

"Data Source=.\sq1express;Initia1 Cata1og=Northwind;Integrated Security=True") Private WithEvents ConnectionToOleDb As New System.Data.O1eDb.O1eDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""C:\DataSources\Nwind.mdb"";" & _ "Persist Security Info=Fa1se") Private WithEvents ConnectionToOdbc As New OdbcConnection( _

"Dsn=MS Access Database;dbq=C:DataSources\Nwind.mdb;defau1tdir=C:\DataSources;"

"driverid=281;fi1=MS Access;maxbuffersize=2048;pagetimeout=5;uid=admin") Private WithEvents ConnectionToOracle As New Orac1eConnection("Data Source=MyOrac1eDB;Integrated Security=yes;") End Class

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 {

Initiali zeComponent() ;

// 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, we 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 will open 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

Connecti onToSql.Open() End Sub

Private Sub OpenOleDbButton_Click(ByVal sender As System ByVal e As System.EventArgs) Handles OpenOleDbButton 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

private void OpenSqlServerButton_Click(object sender, EventArgs e)

ConnectionToSql.Open(); private void OpenOleDbButton_Click(object sender, EventArgs e)

Connecti onToOleDb.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. Worth noting is 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.CloseO End Sub

Private Sub C1oseO1eDbButton_C1ick(ByVa1 ByVal e As System.EventArgs) Handles ConnectionToOleDb.C1ose() End Sub

Private Sub C1oseOdbcButton_C1ick(ByVa1 sender As System ByVal e As System.EventArgs) Handles CloseOdbcButton ConnectionToOdbc. C1ose() End Sub

Private Sub C1oseOrac1eButton_C1ick(ByVa1 sender As System ByVal e As System.EventArgs) Handles CloseOracleButton ConnectionToOracle.Cl ose() End Sub

private void C1oseSq1Button_C1ick(object sender, EventArgs e) {

Connecti onToSql.CloseO;

private void C1oseO1eDbButton_C1ick(object sender, EventArgs e) {

Connecti onToO1eDb.C1ose();

private void C1oseOdbcButton_C1ick(object sender, EventArgs e) {

ConnectionToOdbc.C1ose();

private void C1oseOrac1eButton_C1ick(object sender, EventArgs e) {

ConnectionToOrac1e.C1ose();

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 opening and closing a connection, we 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 can be responded 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 sender As System.Object, _ CloseOleDbButton. Click

.Object, _ .Click 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

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 we 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, we are going to take advantage of a feature built into the SqlConnection object that allows us to capture errors with severities up to severity level 16 by setting the connection object's FireInfoMessageEvent-OnUserErrors property to True before executing a method that will force an error to be thrown.

13. Add the following code, which will handle the GetSqlInfo button's click event and the SqlConnection object's InfoMessage event.

Upon examination of the code in the button-click event, you can see we 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 will open 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

// 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 meta data from the data source you are connected to. In Lesson 1, 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, let's add a few more lines of code to our application and implement the Get Info buttons of the remaining connections to return the server versions of the data sources they are connected to.

Was this article helpful?

0 0

Post a comment