The C# Station ADO.NET Tutorial

by Joe Mayo, 8/9/04

Lesson 02: The SqlConnection Object

This lesson describes the SqlConnection object and how to connect to a data base.  Here are the objectives of this lesson:

• Know what connection objects are used for.
• Learn how to instantiate a SqlConnection object.
• Understand how the SqlConnection object is used in applications.
• Comprehend the importance of effective connection lifetime management.

Introduction

The first thing you will need to do when interacting with a data base is to create a connection.  The connection tells the rest of the ADO.NET code which data base it is talking to.  It manages all of the low level logic associated with the specific data base protocols.  This makes it easy for you because the most work you will have to do in code is instantiate the connection object, open the connection, and then close the connection when you are done.  Because of the way that other classes in ADO.NET are built, sometimes you don't even have to do that much work.

Although working with connections is very easy in ADO.NET, you need to understand connections in order to make the right decisions when coding your data access routines.  Understand that a connection is a valuable resource.  Sure, if you have a stand-alone client application that works on a single data base one one machine, you probably don't care about this.  However, think about an enterprise application where hundreds of users throughout a company are accessing the same data base.  Each connection represents overhead and there can only be a finite amount of them.  To look at a more extreme case, consider a Web site that is being hit with hundreds of thousands of hits a day.  Applications that grab connections and don't let them go can have seriously negative impacts on performance and scalability.

Creating a SqlConnection Object

A SqlConnection is an object, just like any other C# object.  Most of the time, you just declare and instantiate the SqlConnection all at the same time, as shown below:

SqlConnection conn = new SqlConnection(
    "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

The SqlConnection object instantiated above uses a constructor with a single argument of type string.  This argument is called a connection string.  table 1 describes common parts of a connection string.

table 1.  ADO.NET Connection Strings contain certain key/value pairs for specifying how to make a data base connection.  They include the location, name of the database, and security credentials.

Using a SqlConnection

Listing 1.  Using a SqlConnection

using System;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Demonstrates how to work with SqlConnection objects
/// </summary>
class SqlConnectionDemo
{
    static void Main()
    {
        // 1. Instantiate the connection
        SqlConnection conn = new SqlConnection(
            "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

        SqlDataReader rdr = null;

        try
        {
            // 2. Open the connection
            conn.Open();

            // 3. Pass the connection to a command object
            SqlCommand cmd = new SqlCommand("select * from Customers", conn);

            //
            // 4. Use the connection
            //

            // get query results
            rdr = cmd.ExecuteReader();

            // print the CustomerID of each record
            while (rdr.Read())
            {
                Console.WriteLine(rdr[0]);
            }
        }
        finally
        {
            // close the reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // 5. Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}