Using a SqlConnection Object to Connect to a SQL Server Database

You create a SqlConnection object using the SqlConnection() constructor. This constructor is overloaded, meaning that there are multiple versions of the constructor that you can call. The SqlConnection() constructors are as follows:

 SqlConnection() SqlConnection(string connectionString)

where connectionString contains the details for the database connection. You'll learn the details of the connectionString in this section.

Assuming you've imported the System.Data.SqlClient namespace, you can create a new SqlConnection object using the following statement:

 SqlConnection mySqlConnection = new SqlConnection();

You can then set the details for the database connection using the ConnectionString property of mySqlConnection. For example:

 mySqlConnection.ConnectionString =   "server=localhost;database=Northwind;uid=sa;pwd=sa";

where

server specifies the name of the computer on which SQL Server is running.

database specifies the name of the database.

uid specifies the name of the database user.

pwd specifies the password for the user.

Warning

For security reasons, do not include the username password in your production code. Instead ask the user to enter their name and password-or use integrated security, which you'll learn about shortly.

One thing you need to bear in mind is that you can set the ConnectionString property only when your Connection object is closed.

You can also pass a connection string directly to the SqlConnection() constructor. For example:

 string connectionString =   "server=localhost;database=Northwind;uid=sa;pwd=sa";   SqlConnection mySqlConnection =     new SqlConnection(connectionString);

Or more simply:

 SqlConnection mySqlConnection =   new SqlConnection(     "server=localhost;database=Northwind;uid=sa;pwd=sa"   );

You can set an optional connection timeout, which specifies the number of seconds that the Open() method will wait for a connection to the database. You do this by specifying a connection timeout in your connection string. For example, the following string specifies a connection timeout of 10 seconds:

 string connectionString =   "server=localhost;database=Northwind;uid=sa;pwd=sa;" +   "connection timeout=10";

Note

The default connection timeout is 15 seconds. A connection timeout of 0 seconds means the connection attempt will wait indefinitely. Avoid setting your connection timeout to 0.

Before starting a Windows session, you typically log in to Windows with a username and password. If you're using Windows integrated security, you can pass your username and password to SQL Server and use those credentials to connect to the database. This saves you from providing a separate username and password to SQL Server. You can use integrated security in your program by specifying integrated security=SSPI in your connection string. For example:

 string connectionString =   "server=localhost;database=Northwind;integrated security=SSPI";

Notice that you don't provide the username and password. Instead, the username and password you used when logging into Windows is passed to SQL Server. SQL Server will then check its list of users to see if you have permission to access the database. (For further details on integrated security, consult the SQL Server Books Online documentation.)

You've now seen how to create a Connection object using program statements. You'll see how to create a Connection object visually using Visual Studio .NET later in the "Creating a Connection Object using Visual Studio .NET" section. Next, you'll see how to open and close a connection.

Opening and Closing a Database Connection

Once you've created your Connection object and set its ConnectionString property to the appropriate details for your database connection, you can open the connection to the database. You do this by calling the Open() method of your Connection object. The following example calls the Open() method of mySqlConnection:

 mySqlConnection.Open();

Once you've finished with your database connection, you call the Close() method of your Connection object. For example:

 mySqlConnection.Close();

Listing 7.1 illustrates how to connect to the SQL Server Northwind database using a SqlConnection object and display some of the properties of that object.

Listing 7.1: MYSQLCONNECTION.CS

 /*   MySqlConnection.cs illustrates how to use a   SqlConnection object to connect to a SQL Server database */ using System; using System.Data; using System.Data.SqlClient; class MySqlConnection {   public static void Main()   {     // formulate a string containing the details of the     // database connection     string connectionString =       "server=localhost;database=Northwind;uid=sa;pwd=sa";     // create a SqlConnection object to connect to the     // database, passing the connection string to the constructor     SqlConnection mySqlConnection =       new SqlConnection(connectionString);     // open the database connection using the     // Open() method of the SqlConnection object     mySqlConnection.Open();     // display the properties of the SqlConnection object     Console.WriteLine("mySqlConnection.ConnectionString = "+       mySqlConnection.ConnectionString);     Console.WriteLine("mySqlConnection.ConnectionTimeout = "+       mySqlConnection.ConnectionTimeout);     Console.WriteLine("mySqlConnection.Database = "+       mySqlConnection.Database);     Console.WriteLine("mySqlConnection.DataSource = "+       mySqlConnection.DataSource);     Console.WriteLine("mySqlConnection.PacketSize = "+       mySqlConnection.PacketSize);     Console.WriteLine("mySqlConnection.ServerVersion = "+       mySqlConnection.ServerVersion);     Console.WriteLine("mySqlConnection.State = "+       mySqlConnection.State);     Console.WriteLine("mySqlConnection.WorkstationId = "+       mySqlConnection.WorkstationId);    // close the database connection using the Close() method    // of the SqlConnection object    mySqlConnection.Close();   } }

The output from this program is as follows:

 mySqlConnection.ConnectionString = server=localhost;database=Northwind;uid=sa; mySqlConnection.ConnectionTimeout = 15 mySqlConnection.Database = Northwind mySqlConnection.DataSource = localhost mySqlConnection.PacketSize = 8192 mySqlConnection.ServerVersion = 08.00.0194 mySqlConnection.State = Open mySqlConnection.WorkstationId = JMPRICE-DT1

Note

Your results will differ from those here. For example, your connection string and workstation ID will be different.

Connection Pooling

Opening and closing a database connection is a relatively time-consuming process. For this reason, ADO.NET automatically stores database connections in a pool. Connection pooling offers a great performance improvement because you don't have to wait for a brand new connection to the database to be established when there's a suitable connection already available. When you close a connection, that connection isn't actually closed; instead, your connection is marked as unused and stored in the pool, ready to be used again.

If you then supply the same details in the connection string (same database, username, password, and so on), then the connection from the pool is retrieved and returned to you. You then use that same connection to access the database.

When using a SqlConnection object, you can indicate the maximum number of connections allowed in the pool by specifying max pool size in your connection string (the default is 100). You can also indicate the minimum number of connections in the pool by specifying min pool size (the default is 0). For example, the following SqlConnection specifies a max pool size of 10 and a min pool size of 5:

 SqlConnection mySqlConnection =   new SqlConnection(     "server=localhost;database=Northwind;uid=sa;pwd=sa;" +     "max pool size=10;min pool size=5"   );

In this example, a pool with five initial SqlConnection objects is created. A maximum of 10 SqlConnection objects can be stored in the pool. If you attempt to open a new SqlConnection object and the pool is already full with currently used objects, your request waits until a SqlConnection object is closed, at which point that object is returned for you to use. If your request waits longer than the number of seconds in the ConnectionTimout property, then an exception is thrown.

Listing 7.2 illustrates the time-saving when opening a previously pooled connection.

Listing 7.2: CONNECTIONPOOLING.CS

 /*   ConnectionPooling.cs illustrates connection pooling */ using System; using System.Data; using System.Data.SqlClient; class ConnectionPooling {   public static void Main()   {     // create a SqlConnection object to connect to the database,     // setting max pool size to 10 and min pool size to 5     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa;" +         "max pool size=10;min pool size=5"       );     // open the SqlConnection object 10 times     for (int count = 1; count <= 10; count++)     {       Console.WriteLine("count = "+ count);       // create a DateTime object and set it to the       // current date and time       DateTime start = DateTime.Now;       // open the database connection using the       // Open() method of the SqlConnection object       mySqlConnection.Open();       // subtract the current date and time from the start,       // storing the difference in a TimeSpan       TimeSpan timeTaken = DateTime.Now - start;       // display the number of milliseconds taken to open       // the connection       Console.WriteLine("Milliseconds = "+ timeTaken.Milliseconds);       // display the connection state       Console.WriteLine("mySqlConnection.State = "+        mySqlConnection.State);       // close the database connection using the Close() method       // of the SqlConnection object       mySqlConnection.Close();     }   } }

The output from this program is as follows:

 count = 1 Milliseconds = 101 mySqlConnection.State = Open count = 2 Milliseconds = 0 mySqlConnection.State = Open count = 3 Milliseconds = 0 mySqlConnection.State = Open count = 4 Milliseconds = 0 mySqlConnection.State = Open count = 5 Milliseconds = 0 mySqlConnection.State = Open count = 6 Milliseconds = 0 mySqlConnection.State = Open count = 7 Milliseconds = 0 mySqlConnection.State = Open count = 8 Milliseconds = 0 mySqlConnection.State = Open count = 9 Milliseconds = 0 mySqlConnection.State = Open count = 10 Milliseconds = 0 mySqlConnection.State = Open

Note

Your results might differ from those here.

As you can see, the time to open the first connection is relatively long compared with the subsequent ones. This is because the first connection makes the actual connection to the database. When it is closed, it's stored in the connection pool. When the connection is then opened again, it's retrieved from the pool, and this retrieval is very fast.

Getting the State of a Connection Object

The state of a connection enables you to know the progress of your connection request to the database; two examples of states are open and closed. You use the Connection object's State property to get the current state of the connection to the database. The State property returns a constant from the ConnectionState enumeration.

Note

An enumeration is a list of numeric constants, each of which has a name.

Table 7.4 lists the constants defined in the ConnectionState enumeration.

Table 7.4: ConnectionState CONSTANTS
CONSTANT NAME	DESCRIPTION
Broken	The Connection is broken. This can happen after you've opened the Connection object. You can close the Connection and reopen it.
Closed	The Connection is closed.
Connecting	The Connection is establishing access to the database.
Executing	The Connection is running a command.
Fetching	The Connection is retrieving information from the database.
Open	The Connection is open.

Note

In version 1 of ADO.NET, only the Open and Closed states are used. The other states will be used in later versions.

An example of using the State property would be to check if your Connection object is currently open before calling its Open() method. You might need to do that if you have a complex application and you're using a Connection object created somewhere else in the application: you might not know the current state of that Connection object and you don't want to call the Open() method on an already open Connection because that will raise an exception.

The following example uses the State property to check if mySqlConnection is closed before opening it:

 if (mySqlConnection.State == ConnectionState.Closed) {   mySqlConnection.Open(); }

As you'll learn in the next section, you can use the StateChange event to monitor changes in a Connection object's state.

Using Connection Events

The Connection classes have two useful events: StateChange and InfoMessage. You'll see how to use these events next.

The StateChange Event

The StateChange event fires when the state of your connection is changed, and you can use this event to monitor changes in the state of your Connection object.

The method that handles an event is known as an event handler. You call this method when a particular event is fired. All event handler methods must return void and accept two parameters. The first parameter is an object (of the class System.Object), and it represents the object that raises the event.

Note

The System.Object class acts as the base class for all classes. In other words, all classes are ultimately derived from the System.Object class.

The second parameter is an object of a class that is derived from the System.EventArgs class. The EventArgs class is the base class for event data and represents the details of the event. In the case of the StateChange event, this second object is of the StateChangeEventArgs class.

The following example defines a method named StateChangeHandler to handle the StateChange event. You'll notice that the second parameter to this method is a StateChangeEventArgs object. You get the original state of the connection using this object's OriginalState property, and you get the current state using the CurrentState property.

 public static void StateChangeHandler(   object mySender, StateChangeEventArgs myEvent ) {   Console.WriteLine(     "mySqlConnection State has changed from "+     myEvent.OriginalState + "to "+     myEvent.CurrentState   ); }

To monitor an event, you must register your event handler method with that event. For example, the following statement registers the StateChangeHandler() method with the StateChange event of the mySqlConnection object:

 mySqlConnection.StateChange +=   new StateChangeEventHandler(StateChangeHandler);

Whenever the StateChange event fires, the StateChangeHandler() method will be called, which displays the original and current state of mySqlConnection.

Listing 7.3 illustrates the use of the StateChange event.

Listing 7.3: STATECHANGE.CS

 /*   StateChange.cs illustrates how to use the StateChange event */ using System; using System.Data; using System.Data.SqlClient; class StateChange {   // define the StateChangeHandler() method to handle the   // StateChange event   public static void StateChangeHandler(     object mySender, StateChangeEventArgs myEvent   )   {     Console.WriteLine(       "mySqlConnection State has changed from "+       myEvent.OriginalState + "to "+       myEvent.CurrentState     );   }   public static void Main()   {     // create a SqlConnection object     SqlConnection mySqlConnection =       new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");     // monitor the StateChange event using the StateChangeHandler() method     mySqlConnection.StateChange +=       new StateChangeEventHandler(StateChangeHandler);     // open mySqlConnection, causing the State to change from Closed     // to Open     Console.WriteLine("Calling mySqlConnection.Open()");     mySqlConnection.Open();     // close mySqlConnection, causing the State to change from Open     // to Closed     Console.WriteLine("Calling mySqlConnection.Close()");     mySqlConnection.Close();   } }

The output from this program is as follows:

 Calling mySqlConnection.Open() mySqlConnection State has changed from Closed to Open Calling mySqlConnection.Close() mySqlConnection State has changed from Open to Closed

The InfoMessage Event

The InfoMessage event fires when the database returns a warning or information message produced by the database. You use the InfoMessage event to monitor these messages. To get the message, you read the contents of the Errors collection from the SqlInfoMessageEventArgs object.

You can produce information and error messages using the SQL Server PRINT or RAISERROR statements, which are described in Chapter 4, "Introduction to Transact-SQL Programming."

The following InfoMessageHandler() method is used to handle the InfoMessage event. Notice the use of the Errors collection to display the message:

 public static void InfoMessageHandler(   object mySender, SqlInfoMessageEventArgs myEvent ) {   Console.WriteLine(     "The following message was produced:\n" +     myEvent.Errors[0]   ); }

Note

If you're using the OLE DB managed providers, you replace SqlInfoMessageEventArgs with OleDbInfoMessageEventArgs. If you're using the ODBC managed providers, you replace SqlInfoMessageEventArgs with OdbcInfoMessageEventArgs.

Listing 7.4 illustrates the use of the InfoMessage event. You'll notice this program uses the ExecuteNonQuery() method of the SqlCommand object to send PRINT and RAISERROR statements to the database for execution. You'll learn the details of the SqlCommand object and the ExecuteNonQuery() method in Chapter 8, "Executing Database Commands."

Listing 7.4: INFOMESSAGE.CS

 /*   InfoMessage.cs illustrates how to use the InfoMessage event */ using System; using System.Data; using System.Data.SqlClient; class InfoMessage {   // define the InfoMessageHandler() method to handle the   // InfoMessage event   public static void InfoMessageHandler(     object mySender, SqlInfoMessageEventArgs myEvent   )   {     Console.WriteLine(       "The following message was produced:\n" +       myEvent.Errors[0]     );   } public static void Main() {   // create a SqlConnection object   SqlConnection mySqlConnection =     new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");   // monitor the InfoMessage event using the InfoMessageHandler() method   mySqlConnection.InfoMessage +=     new SqlInfoMessageEventHandler(InfoMessageHandler);   // open mySqlConnection   mySqlConnection.Open();   // create a SqlCommand object   SqlCommand mySqlCommand = mySqlConnection.CreateCommand();   // run a PRINT statement   mySqlCommand.CommandText =     "PRINT 'This is the message from the PRINT statement'";   mySqlCommand.ExecuteNonQuery();   // run a RAISERROR statement   mySqlCommand.CommandText =     "RAISERROR('This is the message from the RAISERROR statement', 10, 1)";   mySqlCommand.ExecuteNonQuery();   // close mySqlConnection   mySqlConnection.Close();   } }

The output from this program is as follows:

 The following message was produced: System.Data.SqlClient.SqlError: This is the message from the PRINT statement The following message was produced: System.Data.SqlClient.SqlError: This is the message from the RAISERROR statement