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

Lesson 1: Creating and Configuring Connection Objects

This lesson describes the two ways to create and configure connection objects:

• Through a user interface (UI), using the Add Connection dialog box

• Programmatically, by handcrafting the objects in code

Whether you choose to create connections through the UI or programmatically, the result is the same—a configured connection object ready to open a connection and communicate with your data source. For this lesson, we will focus only on creating connection objects as opposed to actually connecting and communicating with a data source. In Lesson 2: Connecting to Data Using Connection Objects, we will move on to the next level to open the connection and retrieve information from the data source.

What Is a Connection Object?

A connection object is simply a representation of an open connection to a data source. The easiest way to describe a connection object is, first, to explain what a connection object is not! A connection object does not fetch or update data, it does not execute queries, and it does not contain the results of queries. It is merely the pipeline through which commands and queries send their SQL statements and receive results. Although connection objects typically can be thought of as the place where you set your connection string, they have additional methods for working with the connection, such as methods that open and close connections, as well as methods for working with connection pools and transactions. Essentially, connection objects provide a conduit for sending commands to a database and retrieving data and information into your application, as shown in Figure 5-1.

Creating Connections in Server Explorer

To simplify the process of creating applications that access data, Visual Studio provides the Server Explorer window as a central location to manage data connections independent of any actual projects. In other words, you can create data connections in Server Explorer and access them in any project. Data connections created in Server Explorer are user-specific settings in Visual Studio that display the connections each time you open Visual Studio (instead of creating connections as part of developing a specific application that stores them in that application). Of course, you can create data connections as part of the development process from within an open project, but that is covered in the next section.

Creating Connections Using Data Wizards

Visual Studio provides a few wizards that simplify the process of creating applications that access data and that create data connections as a result of completing the wizards. The main wizard for bringing data into an application is the Data Source Configuration Wizard. When you run the Data Source Configuration Wizard and select the database path, you end up with a configured connection object ready to use in your application. In addition to creating a configured connection object, the Data Source Configuration Wizard allows you to select the database objects you want to use in your application.

Creating Connection Objects Programmatically

When you do not want to use the visual tools previously described and need to create your connections manually, it is easy to create connection objects in code programmatically. The first step is to decide which type of connection object to create. The choice is fairly simple because it depends on the back-end data source your application needs to communicate with.

Table 5-1 lists the primary connection objects available in ADO.NET and the data sources they are designed to access.

Table 5-1 Connection Objects

Name	Target Data Source
SqlConnection	SQL Server 7.0 and later databases
OleDbConnection	OLE DB data sources (such as Office Access databases through Jet 4.0)
OdbcConnection	Open database connectivity (ODBC) data sources such as a Data Source Name (DSN) as defined in the ODBC Data Source Administrator dialog box
OracleConnection	Oracle 7.3, 8i, or 9i databases

The properties, methods, and events associated with the connection objects in this table vary because each connection object is designed to efficiently connect and interact with its respective data sources. However, each connection object contains the same base properties, methods, and events that are inherited from the System.Data.Common.DbConnection class.

Table 5-2 lists the properties common to all connection objects.

Table 5-2 Connection Properties

Name	Description
ConnectionString	Gets or sets the string used to open the connection.
ConnectionTimeout	Read only. Gets the time to wait while establishing a connection before terminating the attempt and generating an error.
Database	Read only. Gets the name of the current database after a connection is opened or the database name specified in the connection string before the connection is opened.
DataSource	Read only. Gets the name of the database server to which it is connected.
ServerVersion	Read only. Gets a string that represents the version of the server to which the object is connected.
State	Read only. Gets a combination of System.Data.ConnectionState values that describes the state of the connection.

Table 5-3 lists the methods common to all connection objects.

Table 5-3 Connection Methods

Name	Description
BeginDbTransaction	Starts a database transaction.
BeginTransaction	Starts a database transaction.
ChangeDatabase	Changes the current database for an open connection.
Close	Closes the connection to the database. This is the preferred method of closing any open connection.
CreateCommand	Creates and returns a System.Data.Common.DbCommand object associated with the current connection.
CreateDbCommand	Creates and returns a System.Data.Common.DbCommand object associated with the current connection.
EnlistTransaction	Enlists in the specified transaction as a distributed transaction.
GetSchema	Returns schema information for the data source of this System.Data.Common.DbConnection class.
New	Initializes a new instance of the System.Data.Common.DbConnection class.
OnStateChange	Raises the System.Data.Common.DbConnection.StateChange event.
Open	Opens a database connection with the settings specified by the System.Data.Common.DbConnection.ConnectionString.

Table 5-4 lists the events common to all connection objects.

Table 5-4. Connection Events

Name	Description
StateChange	Occurs when the state of the connection changes
InfoMessage	Occurs when the server returns a warning or informational message

To create connections programmatically using the four primary data providers, you start by instantiating a new connection object and setting its ConnectionString property that you will use to open the connection.

Creating SQL Server Connection Objects in Code

You create SqlConnection objects with the New keyword. You can instantiate the connection and set the connection string in the same call, or you can assign the connection string to the SqlConnection.ConnectionString property after instantiating the connection. Be sure to replace ServerName and DatabaseName with valid values for your environment. To eliminate the need to qualify the objects fully in code, add an Imports System.Data.SqlClient statement (Visual Basic) or using System.Data.SqlClient; statement (C#) to the top of your code file. Use the WithEvents keyword (in Visual Basic) or create event handlers in C# if your application needs to respond to the connection objects events.

' VB
Private WithEvents ConnectionToSql As New SqlConnection _
   ("Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True")

// C#
SqlConnection ConnectionToSql = new SqlConnection
    ("Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True");

Creating OLE DB Connection Objects in Code

You create OleDbConnection objects with the New keyword. You can instantiate the connection and set the connection string in the same call, or you can assign the connection string to the OleDbConnection.ConnectionString property after instantiating the connection. Be sure to replace the data source with a valid path if you are connecting to an Office Access database, or replace the connection string with a valid connection string for the OLE DB data source you want to connect to. To eliminate the need to fully qualify the objects in code, add an Imports System.Data.OleDb statement (Visual Basic) or using System.Data.OleDb; statement (C#) to the top of your code file.

' VB
Private WithEvents ConnectionToOleDb As New System.Data.OleDb.OleDbConnection _
   ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""Nwind.mdb"";Persist Security
Info=False")

// C#
System.Data.OleDb.OleDbConnection ConnectionToOleDb = new System.Data.OleDb.
OleDbConnection
   ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"Nwind.mdb";Persist Security
Info=False");

Creating ODBC Connection Objects in Code

You create OdbcConnection objects with the New keyword. You can instantiate the connection and set the connection string in the same call, or you can assign the connection string to the OdbcConnection.ConnectionString property after instantiating the connection. Be sure to replace the connection string with a valid connection string for the ODBC data source you want to connect to. To eliminate the need to qualify the objects fully in code, add an Imports System.Data.Odbc statement (Visual Basic) or using System.Data.Odbc; statement (C#) to the top of your code file.

' VB
 Private WithEvents ConnectionToOdbc As New OdbcConnection _
    ("Dsn=MS Access Database;dbq=C:\Nwind.mdb;defaultdir=C:\DataSources;" & _
    "driverid=281;fil=MS Access;maxbuffersize=2048;pagetimeout=5;uid=admin")

// C#
OdbcConnection ConnectionToOdbc = new OdbcConnection
    ("Dsn=MS Access Database;dbq=C:\\DataSources;" +
    "driverid=281;fil=MS Access;maxbuffersize=2048;pagetimeout=5;uid=admin");

Creating Oracle Connection Objects in Code

You create OracleConnection objects with the New keyword. You can instantiate the connection and set the connection string in the same call, or you can assign the connection string to the OracleConnection.ConnectionString property after instantiating the connection. Be sure to replace the connection string with a valid one for the Oracle database you want to connect to. To eliminate the need to qualify the objects fully in code, add an Imports System.Data.OracleClient statement (Visual Basic) or using System.Data.OracleClient; statement (C#) to the top of your code file.

' VB
Private WithEvents ConnectionToOracle As New OracleConnection _
   ("Data Source=Oracle8i;Integrated Security=yes")

// C#
private OracleConnection ConnectionToOracle = new OracleConnection
    ("Data Source=Oracle8i;Integrated Security=yes");

Lab: Creating New Data Connections

In this lab you will practice creating new Data Connections in Server Explorer and using the Data Source Configuration Wizard.

EXERCISE 1: Creating Connections in Server Explorer

The following steps describe how to create a Data Connection (a connection to a database) in Server Explorer:

1. If the Server Explorer window is not visible, select Server Explorer from the View menu.

2. Right-click the Data Connections node and select Add Connection.

   The first time you add a connection in Visual Studio, the Choose Data Source dialog box opens.

   NOTE: ADD CONNECTION DIALOG BOX

   If the Add Connection dialog box opens instead of the Choose Data Source dialog box, click the Change button located at the top of the Add Connection dialog box.

   The Choose Data Source dialog box (or the similar Change Data Source dialog box) is where you select the data source you want to connect to, as well as the data provider to use for the connection. Notice how the proper data provider is automatically populated when you select different data sources. You can choose any valid provider you want for any selected data source, but Visual Studio automatically selects the most appropriate data provider based on the selected data source.

   For the first connection, we’ll create a connection to the Northwind Traders sample database in SQL Server.

3. Select Microsoft SQL Server for the data source and click OK.

   The Add Connection dialog box now appears with Microsoft SQL Server as the selected data source.

   NOTE: .NET FRAMEWORK DATA PROVIDERS

   The .NET Framework Data Provider for SQL Server is designed to connect to SQL Server 7 and later versions. When connecting to SQL Server 6 or earlier, select the <other> data source and select the .NET Framework Data Provider for OLE DB. Then, in the Add Connection dialog box, select the Microsoft OLE DB Provider for SQL Server.

4. Type the name of your SQL Server in the server name area.

5. Select the appropriate method of authentication to access your SQL Server.

6. Choose the Select Or Enter A Database Name option and select the Northwind database from the drop-down list.

7. You can verify the connection is valid by clicking Test Connection and then clicking OK to close the dialog box and create the connection in Server Explorer.

   After creating the connection, the Properties window provides information related to the connection, as well as information related to the actual database you are connected to.

8. Select the connection you just created in the Server Explorer window to view the available information in the properties window.

EXERCISE 2: Creating Connections Using the Data Source Configuration Wizard

To create data connections using the Data Source Configuration Wizard, perform the following steps:

1. Create a Windows Forms application.

2. Select Add New Data Source from the Data menu.

3. The default data source type is Database, so just click Next.

4. The Choose Your Data Connection page of the wizard is where you create your connection object.

   NOTE: AVAILABLE CONNECTIONS

   The drop-down list is populated with the connections already available in Server Explorer. If you completed the previous section and created a data connection to the Northwind database, it will be available in this drop-down list.

5. For this exercise we will create a new connection, so click the New Connection button to open the Add Connection dialog box.

   NOTE: ADD CONNECTION DIALOG BOX

   Just like adding a new connection to Server Explorer, you use the Add Connection dialog box to create the connection. Basically, when creating new connections through the UI, whether using one of the data wizards or Server Explorer, the Add Connection dialog box is always used.

6. Type the name of your SQL server in the server name area.

7. Select the appropriate method of authentication to access your SQL server.

8. Choose the Select Or Enter A Database Name option and select the Northwind database from the drop-down list.

9. You can verify that the connection is valid by clicking Test Connection and then clicking OK to close the dialog box.

   NOTE: INCLUDING SENSITIVE DATA

   If your connection uses SQL authentication and requires a user name and password to connect to your database, the option to include or exclude sensitive data in the connection string is enabled. By default, the connection string does not include sensitive data, and you need to provide this information in your application when you attempt to open the connection and connect to the database. You can select the option to include sensitive data in the connection string, but this is not considered best practice because users who have access to the connection string might be able to view the password. Using Integrated Security is the recommended option.

   At this point in the wizard, you have successfully created your data connection and can view the connection string by expanding the Connection string node. To add the connection to your project, finish the wizard by completing the following steps.

10. Click Next. You are presented with the option of saving the Connection string in the application configuration file as well as providing a name for the connection. By default, the selection is set to save the connection; this is probably a good idea for most applications. Saving your connection in the application configuration file would be advantageous if, after deployment, you wanted to point to a different data source. Then you (or a systems administrator) could easily modify the configuration setting rather than having to change the connection string in code and recompile and redeploy the application. Once a connection string is saved to the application configuration file, you can access and modify it using the Project Designer. Open the Project Designer by clicking the My Project toolbar button (VB) or the Properties toolbar button (C#) in Solution Explorer. After the Project Designer opens, click the Settings tab to access the connection strings stored in your application.

11. The Choose Your Database Objects page of the wizard allows you to select the Tables, Views, Stored Procedures, and so on to be used in your application. For this lesson, expand the Tables node and select the Customers and Orders tables.

12. Click Finish. A typed dataset with the connection object defined in the wizard is added to your project.

Now that you’ve completed the wizard, let’s take a look at where the connection is and what it contains. The connection created as a result of running the wizard is located within the designer-generated dataset code file. To view the actual connection object, open the dataset in the Dataset Designer by double-clicking the dataset object in Solution Explorer. (The Dataset object is the NorthwindDataSet.xsd node.) Select the title bar of a TableAdapter on the design surface (for example, select CustomersTableAdapter). The connection information is available in the Properties window, where you can expand the node and see the name, modifier, and connection string.

Lesson Summary

• Connection objects provide two-way communication between your application and a data source.

• Connection objects can be added to Server Explorer, where they can then be easily incorporated into future projects.

• To create connection objects, you must have a valid connection string and the proper credentials to access the data source.

• Connection objects can be created either through the UI or programmatically, depending on user preference and development style.

• There are four primary connection objects, one for each of the .NET Framework Data Providers.

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. Where is the connection object located that was created as a result of running the Data Source Configuration Wizard?

   1. In the application configuration file

   2. In the Data Sources window

   3. In the designer-generated dataset code file

   4. In the generated form code

2. When should you use the OleDbConnection object? (Choose all that apply.)

   1. When connecting to an Oracle database

   2. When connecting to an Office Access database

   3. When connecting to SQL Server 6.x or later

   4. When connecting to SQL Server 2000

   5. When connecting to SQL Server 2000

3. What user interface component is used to create connections?

   1. The Data Source Configuration Wizard

   2. The Server Explorer window

   3. The Add Connection dialog box

   4. The Properties window