14.1 Creating a Database Connection

You want to open a connection to a database.

Technique

You make a database connection within .NET by first creating a database connection string. This semicolon-delimited list of values contains various connection parameters to control how a connection is made. ADO.NET contains a couple of connection classes based on the type of database you are utilizing . For instance, a connection to an SQL Server uses the SqlConnection class, whereas Object Linking and Embedding (OLEDB)-supported databases use the OleDBConnection class. Most of the connection objects support a subset of connection string parameters but are not required to support all. You must investigate the possible connection-string parameters for your type of connection.

This chapter utilizes SQL Server for its discussions. However, you don't need the full version of SQL Server to use it. A copy of the Microsoft SQL Server 2000 Desktop Engine is included with Visual Studio .NET 2003 and the .NET Framework software development kit (SDK) but is not installed by default. Once installed, it allows you to use SQL databases within your application.

To create a connection to an SQL Server database, pass a connection string to the SqlConnection constructor when instantiating an SqlConection object. To open the connection, call the Open method, and likewise, to close the connection, call the Close method defined within the SqlConnection class. The application in this chapter uses a connection string containing three parameters. The first is Integrated Security , which is set to SSPI and tells SQL Server that you want to enable Windows-based authentication. The Data Source parameter is set to your machine name, which is obviously different from the machine name we are using. Finally, the Initial Catalog parameter is set to Northwind because the examples in this chapter use the Northwind database.

 [STAThread] static void Main(string[] args) {     SqlConnection connection;     string connectionString = "Integrated Security=SSPI;" +         "Data Source=VCSMARKHSCH6;Initial Catalog=Northwind;";     // create new SqlConnection specifying the connection string     connection = new SqlConnection( connectionString );     // open the connection     connection.Open();     // get the data     // close the connection     connection.Close(); } 

Comments

The major hurdle to overcome when creating a database connection is figuring out the correct connection string to use. Using an Access database with the OleDBConnection class uses the same Open and Close methods ; the only difference between it and SqlConnection lies within the connection string.

As mentioned in the introduction, you can use ADO.NET in a disconnected state. In other words, you can open an initial database connection, transfer data to a cache object, and then close the connection. However, you are still free to manipulate the data, only reconnecting when you want to apply those changes. The objects involved in this scenario include a data adapter such as SqlDataAdapter , which you use to manage the transfer of data between a database and the in-memory data-representation object called the DataSet . Each of these objects is explained in the following sections.