Establishing a Connection

In most cases when working with data and ADO.NET, you will need to establish a connection to the data source. This data source can be SQL Server 2000, SQL Server 2005, Oracle, Microsoft Access, or any number of other types of data including file-type sources such as Paradox files or even Microsoft Excel documents.

This section provides you with a basic overview of what ADO.NET is and how it works. After that, the discussion will move on to connection strings, what they are, and how you can construct them programmatically. Finally, you will see how to connect to a database and use that connection to query schema and metadata information.

Building a Connection String

Connection strings are often semicolon-delimited strings and contain the information that tells an ADO.NET data provider how to establish a link to the database. Take a look at the following connection string:

Initial Catalog=Northwind; Server=localhost; User Id=sa; Password=password; 

That connection string will open the Northwind sample database on the default SQL Server instance running on the local computer, and will authenticate as the system administrator (sa).

If you have been programming with the same database server for a long time, you can probably construct valid connection strings in your sleep. However, if you're working with a new data provider that has a different connection string format, or you want an easy way to programmatically construct connection strings, there is a new tool in ADO.NET 2.0 to make that task easy.

That tool is the System.Data.Common.DbConnectionStringBuilder class and the classes that descend from it. At its core, it is a dictionary that stores the list of name/value pairs that make up a connection string. Individual providers can then create classes that inherit from DbConnectionStringBuilder to expose strongly typed properties for connection string elements that are specific to that particular provider.

You can either use the base class as a general tool for building connection strings, or you can use the derivative classes for creating connection strings specific to a particular data provider.

For example, if you want to use the basic DbConnectionStringBuilder class, you can simply use the Add method to build the dictionary of connection settings, as shown in the following example:

DbConnectionStringBuilder connBuilder = new DbConnectionStringBuilder(); connBuilder.Add("server", @".\SQLExpress"); connBuilder.Add("Initial Catalog", "TestDB"); connBuilder.Add("Integrated Security", "SSPI"); 

The preceding lines of code set up the name/value pairs necessary to connect to a SQL Express database named TestDB on the local server using integrated Windows authentication. To access the formatted connection string, you can use the ConnectionString property of the DbConnectionStringBuilder class.

To make use of a connection string builder class that is specific to a provider, you can use the derivative class, as in this example:

SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder(); sqlBuilder.DataSource = @".\SQLExpress"; sqlBuilder.InitialCatalog = "TestDB"; sqlBuilder.IntegratedSecurity = true; 

As you can see, the manually added name/value pairs from the basic DbConnectionStringBuilder class have been replaced by strongly typed properties. This is a huge benefit because the developer no longer has to remember exactly how to format all the different properties of the connection string. In addition, IntelliSense will automatically give the developer a list of all the connection string options available. If you are using a full SQL Server instance instead of just SQL Express, you would use the server name (or its IP address) for the DataSource property.

Using the DbConnection Classes

When you have a connection string, you're ready to connect to your data source. A connection represents a live connection to the data source. In most cases that connection is a network connection between the ADO.NET data provider and the RDBMS server such as SQL Server or Oracle, but it could just as well be a connection to a file on disk, such as an Excel document.

Before seeing the code to establish a database connection, you should be aware of the cost of connections. As a general rule of thumb, database connections are expensive, and acquiring new connections can be costly both in terms of time and resources.

To help alleviate this, many data providers support the notion of connection pooling.When pooling is used, connections are placed in a pool when they are first created. When a pooled connection is closed, it is returned to the pool instead of being completely destroyed. The next time your application requests a database connection with the same connection string, the data provider can then retrieve the previously created connection from the pool at a much lower cost than creating one from scratch. Connection pooling is typically enabled in the connection string itself, so if you aren't sure if your provider supports pooling, you can take a look at the provider-specific DbConnectionStringBuilder to see if there are any pooling-related options.

Before wading into the code for creating and using a connection, take a look at Tables 17.1 and 17.2, which detail the methods and properties exposed by classes that derive from DbConnection.

Table 17.1. DbConnection Methods

Method	Description
BeginTransaction	Establishes and starts a new transaction.
ChangeDatabase	If the connection supports it, this will change databases within the same server using the existing credentials.
Close	Closes the connection to the database.
CreateCommand	Creates an instance of DbCommand specific to the current provider.
EnlistTransaction	Enlists the connection in a previously existing transaction.
GetSchema	Obtains schema information from various schema collections exposed by the provider.
Open	Opens a connection to the database.

Table 17.2. DbConnection Properties

Property	Description
ConnectionString	String containing the name-value pairs that describe the connection properties.
ConnectionTimeout	The maximum amount of time that can elapse before a live connection is established.
Database	The name of the database to which the connection is attached.
DataSource	The network identifier for the source of data; can be a network name or IP address.
ServerVersion	The version of the database server software of the server to which the connection is attached.
State	The state of the connection. It can be any of: Broken, Closed, Connecting, Executing, Fetching, or Open.

To experiment with creating a connection, opening that connection, and then obtaining information that can only be obtained when the connection is open, create a new Console application. The code in Program.cs should be as shown in Listing 17.1. If you installed VS.NET 2005 with SQL Express, you should be able to run this sample on your machine. If not, you can modify the connection string to point to the location of a SQL Server 2000 or 2005 instance elsewhere.

Listing 17.1. Program.cs for a Console Application Verifying a SQL Connection

using System; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; namespace ConnectionTest {   class Program   {   static void Main(string[] args)   {     SqlConnectionStringBuilder connBuilder =       new SqlConnectionStringBuilder();     connBuilder.InitialCatalog = "TestDB";     connBuilder.DataSource = @".\SQLExpress";     connBuilder.IntegratedSecurity = true;     SqlConnection conn = new SqlConnection(connBuilder.ConnectionString);     conn.Open();     Console.WriteLine("Connected to SQL Server v" + conn.ServerVersion);     conn.Close();     Console.ReadLine();   } } } 

The ServerVersion property of the SqlConnection object is a property that cannot be accessed unless a valid connection has been established. When you run this application you should see a valid version number for SQL Server (version 9 and higher for SQL Server 2005). Remember that Console.ReadLine() waits for the user to press Enter before continuing processing. A lot of commercial applications will attempt to make a "ping"-type connection to a database before allowing the user to enter any data to verify that there is a valid, running data source. If no database connection can be established, many applications will display an error message and halt processing.