10.1 Connect to a Database

Problem

You need to open a connection to a database.

Solution

Create a connection object appropriate to the type of database to which you need to connect; all connection objects implement the System.Data.IDbConnection interface. Configure the connection object by setting its ConnectionString property. Open the connection by calling the connection object's Open method.

Discussion

The first step in database access is to open a connection to the database. The IDbConnection interface represents a database connection, and each data provider includes a unique implementation. Here is the list of IDbConnection implementations for the five standard data providers.

• System.Data.Odbc.OdbcConnection

• System.Data.OleDb.OleDbConnection

• System.Data.OracleClient.OracleConnection

• System.Data.SqlServerCe.SqlCeConnection

• System.Data.SqlClient.SqlConnection

You configure a connection object using a connection string. A connection string is a set of semicolon-separated name value pairs. You can supply a connection string either as a constructor argument or by setting a connection object's ConnectionString property before opening the connection. Each connection class implementation requires that you provide different information in the connection string. Refer to the ConnectionString property documentation for each implementation to see the values you can specify. Possible settings include the following:

• The name of the target database server

• The name of the database to open initially

• Connection timeout values

• Connection-pooling behavior (see recipe 10.2.)

• Authentication mechanisms to use when connecting to secured databases, including provision of user names and passwords

Once configured, call the connection object's Open method to open the connection to the database. You can then use the connection object to execute commands against the data source (discussed in recipe 10.3). The properties of a connection object also allow you to retrieve information about the state of a connection and the settings used to open the connection. When you're finished with a connection, you should always call its Close method to free up the underlying database connection and system resources. IDbConnection extends System.IDisposable , meaning that each connection class implements the Dispose method. Dispose automatically calls Close , making the using statement a very clean and efficient way of using connection objects in your code.

You achieve optimum scalability by opening your database connection as late as possible and closing it again as soon as you have finished. This ensures that you don't tie up database connections for long periods and give all code the maximum opportunity to obtain a connection. This is especially important if you are using connection pooling.

The code shown here demonstrates how to use the SqlConnection class to open a connection to a SQL Server database running on the local machine that uses integrated Windows security. To access a remote machine, simply change the data source name from localhost to the name of your database instance.

 // Create an empty SqlConnection object. using (SqlConnection con = new SqlConnection()) {     // Configure the SqlConnection object's connection string.     con.ConnectionString =          "Data Source = localhost;"+ // local SQL Server instance         "Database = Northwind;" +   // the sample Northwind DB         "Integrated Security=SSPI"; // integrated Windows security     // Open the Database connection.     con.Open();     // Display information about the connection.     if (con.State == ConnectionState.Open) {         Console.WriteLine("SqlConnection Information:");         Console.WriteLine("  Connection State = " + con.State);         Console.WriteLine("  Connection String = " +              con.ConnectionString);         Console.WriteLine("  Database Source = " + con.DataSource);         Console.WriteLine("  Database = " + con.Database);         Console.WriteLine("  Server Version = " + con.ServerVersion);         Console.WriteLine("  Workstation Id = " + con.WorkstationId);         Console.WriteLine("  Timeout = " + con.ConnectionTimeout);         Console.WriteLine("  Packet Size = " + con.PacketSize);     } else {         Console.WriteLine("SqlConnection failed to open.");         Console.WriteLine("  Connection State = " + con.State);     }     // At the end of the using block Dispose() calls Close(). } 

As another example, the following excerpt from the sample code for this recipe shows the connection string used to open a connection to the same database if you were using the OLE DB data provider to provide connectivity.

 // Create an empty OleDbConnection object. using (OleDbConnection con = new OleDbConnection()) {     // Configure the OleDbConnection object's connection string.     con.ConnectionString =          "Provider = SQLOLEDB;" +         // OLE DB Provider for SQL Server         "Data Source = localhost;" +     // local SQL Server instance         "Initial Catalog = Northwind;" + // the sample Northwind DB         "Integrated Security=SSPI";      // integrated Windows security     // Open the Database connection.     con.Open();      }