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);

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
{

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