Recipe 13.1. Connecting to a Data Provider

Problem

You are writing an application that interacts with a database, and you need to connect to it to run some queries.

Solution

Use a Connection object and a "connection string" to establish the connection you will use for queries and updates.

Discussion

The following set of statements establishes a connection to a SQL Server Express database named MyDatabase running on the system named MySystem, using the active Microsoft Windows login account for its security access:

 Dim theDatabase As System.Data.SqlClient.SqlConnection Dim connectionString As String = _    "Data Source=MySystem\SQLEXPRESS;" & _    "Initial Catalog=MyDatabase;Integrated Security=true" theDatabase = New SqlClient.SqlConnection(connectionString) theDatabase.Open( ) ' ---- Perform database processing here, then… theDatabase.Close( ) theDatabase.Dispose( ) 

ADO.NET includes several different database libraries. The most generic library, found in the System.Data namespace, defines the core classes used to manage database sets in memory. There are distinct classes for tables, columns, and rows of data; classes that let you establish relationships between the tables; and classes that let you bundle tables and relationships in one large "data set." You will probably use these classes quite a bit in your code, but they know nothing of database connections or how to communicate with any external data source (other than specially formatted XML files).

To connect to a database, you must use one of the providers included in ADO.NET. Each provider connects to a specific database or data-communication standard. Four providers ship with .NET, each appearing in a specific namespace, as shown in Table 13-1.

Table 13-1. Providers included with .NET

Provider	Namespace	Comments
SQL Server	System.Data.SqlClient	Visual Studio 2005 includes various editions of SQL Server 2005, which you can access through ADO.NET. This provider also communicates with older versions of SQL Server, back through Version 7.0.
Oracle	System.Data.OracleClient	This is the Microsoft-supplied Oracle provider, and it requires at least Oracle 8.1.7. You must license and install the Oracle Client tools, available directly from Oracle. Oracle also supplies its own ADO.NET provider, which appears through the Oracle.DataAccess namespace. You must contact Oracle directly to acquire that provider.
OLE DB	System.Data.OleDb	This OLE DB provider connects to OLE DB data sources, but it is guaranteed to work only with SQL Server, Oracle, and Jet 4.0 data sources. You can try it with other sources, but you may receive incomplete or inadequate results.
ODBC	System.Data.Odbc	This provider is used with ODBC data sources. As with OLE DB, this provider will work with many ODBC data sources, but it may not work with all known sources. If an OLE DB or native provider is available, you should use that instead of the ODBC alternative.

To connect a provider to a data source, you create a connection object using a valid connection string and then use the Open() method to establish the connection. ADO.NET connection strings are similar to those used in OLE DB and ADO, and building them can be tricky. Connection strings are semicolon-delimited sets of connection parameters, with each entry taking the form parameter=value. The choice of parameters and values varies by connection type and desired features. The connection string used here includes three parameters (DataSource, InitialCatalog, and Integrated Security):

 Data Source=MySystem\SQLEXPRESS;Initial Catalog=MyDatabase; Integrated Security=true 

Setting Integrated Security to true tells SQL Server to use the current Windows user's authentication information to connect to the database. If your database uses SQL Server's built-in authentication system, you can use the following connection string (for user "sa" and password "abc"):

 Data Source=MySystem\SQLEXPRESS;Initial Catalog=MyDatabase; User ID=sa;Password=abc 

Each provider includes a " connection string builder class" (it's found at System.Data.SqlClient. SqlConnectionStringBuilder for the SQL Server provider), and although you can use it, it is simply a string-concatenation tool that attaches the semicolon-delimited parts you provide. You still need to know what each of the parameters and values should be.

The documentation installed with Visual Studio includes an article named "Working with Connection Strings" that includes common parameter names and values. If you look in the online help index for "connection strings [ADO.NET]," the "Working with Connection Strings" article is one of the results. For Oracle connection strings using Oracle's own provider, consult your Oracle documentation or their web site.

Once you have a valid connection string, use it as an argument to the connection object's constructor:

 Dim theDatabase As System.Data.SqlClient.SqlConnection Dim connectionString As String = _    "Data Source=MySystem\SQLEXPRESS;" & _    "Initial Catalog=MyDatabase;Integrated Security=true" theDatabase = New SqlClient.SqlConnection(connectionString) 

Establish the connection by using the Open() method:

 theDatabase.Open() 

You don't need to close the connection until you are truly finished interacting with the database. When you use the Open() method, ADO.NET opens the connection only long enough to verify the connection. It then closes the connection, waiting for you to issue a SQL statement before it opens the connection again.

When you are really ready to close the connection, use the Close() method:

 theDatabase.Close() 

See Also

Although it's not an official Microsoft resource, the http://www.connectionstrings.com web site provides many useful examples of ADO.NET connection strings. The site is a little out of date, but it's still the best place we've found so far to locate details on all the various connection-string parameters.