9-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.
How It Works
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:
CHAPTER 9 ■ DATABASE ACCESS
426
• The name of the target database server
• The name of the database to open initially
• Connection timeout values
• Connection-pooling behavior (see recipe 9-2)
• Authentication mechanisms to use when connecting to secured databases,
including provision of a username and password if needed
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 9-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 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.
The Code
The following example demonstrates how to use both the SqlConnection and OleDbConnection classes to
open a connection to a Microsoft SQL Server Express database running on the local machine that uses
integrated Windows security:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace Apress.VisualCSharpRecipes.Chapter09
{
class Recipe09_01
{
public static void SqlConnectionExample()
{
// Create an empty SqlConnection object.
using (SqlConnection con = new SqlConnection())
{
// Configure the SqlConnection object's connection string.
con.ConnectionString =
@"Data Source=.\sqlexpress;" + // local SQL Server instance
"Database=Northwind;" + // the sample Northwind DB
"Integrated Security=SSPI"; // integrated Windows security
// Open the database connection.
con.Open();
CHAPTER 9 ■ DATABASE ACCESS
427
// 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().
}
}
public static void OleDbConnectionExample()
{
// 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=.\sqlexpress;" + // local SQL Server instance
"Initial Catalog=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("OleDbConnection 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(" Timeout = " + con.ConnectionTimeout);
}
CHAPTER 9 ■ DATABASE ACCESS
428
else
{
Console.WriteLine("OleDbConnection failed to open.");
Console.WriteLine(" Connection State = " + con.State);
}
// At the end of the using block Dispose() calls Close().
}
}
public static void Main()
{
// Open connection using SqlConnection.
SqlConnectionExample();
Console.WriteLine(Environment.NewLine);
// Open connection using OleDbConnection.
OleDbConnectionExample();
// Wait to continue.
Console.WriteLine(Environment.NewLine);
Console.WriteLine("Main method complete. Press Enter.");
Console.ReadLine();
}
}
}
