Opening and Closing Connections

You’ve now seen all the ingredients you need to create and use a connection. You simply create the Connection object required for your data source, apply the appropriate connection string settings, and open the connection. In Example 3-1, a connection is created to a SQL Server database on the local computer using integrated authentication. The code opens the connection, tests its state, and closes it.

Example 3-1. Opening and testing a connection
// ConnectionTest.cs - Opens and verifies a connection

using System;

using System.Data.SqlClient;

public class ConnectionTest


    public static void Main() 


        SqlConnection con = new SqlConnection("Data Source=localhost;" +

               "Initial Catalog=Northwind;Integrated Security=SSPI");


        Console.WriteLine("Connection is " + con.State.ToString());



        Console.WriteLine("Connection is " + con.State.ToString());



The output clearly indicates whether the connection test is successful:

Connection is Open

Connection is Closed

Connection Events

Connection objects expose only two events. The InfoMessage event can retrieve warnings and other messages from a data source. Generally, you use this event if you wish to receive specific information messages that don’t correspond to errors (in SQL Server, these are messages with a severity of 10 or less). You can’t use this event to be informed about errors, which simply causes the Connection object to throw an exception.

The message information is wrapped into ...

Get ADO.NET in a Nutshell now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.