Each data provider in ADO.NET contains a Connection class that inherits from the System.Data.Common.DbConnection  class. The DbConnection serves as the base class for all the Connection classes of different data providers. The following are the connection categories for every data provider.

 Data Provider  Connection Class
 SQL Server  SqlConnection
 OLE DB  OleDbConnection
 ODBC  OdbcConnection

Figure 1 – Connection Classes

The DbConnection class implements the IDbConnection interface which contains methods and properties that are used to define a connection and to open a connection to a  data source. Opening a connection consumes resources of the computer so you need to close it as soon as possible. For you to open a connection, you must specify a Connection String to indicate the location, type and other configurations for connecting to a database source.

The following are the methods and properties of the IDbConnection interface that are common to all Connection classes.

 Property  Description
 ConnectionString  Specifies the connection string to be used by the connection.
 ConnectionTimeout  Specifies the time to wait for the connection to be established before declaring that the connection has timed out.
 Database  Specifies the database that the connection is currently working on.
 State  Specifies the current state of the connection.

Figure 2 – IDbConnection Properties

 Method  Description
 BeginTransaction  Begins a database transaction.
 ChangeDatabase  Changes the database the connection is currently working on.
 Close  Closes the connection.
 CreateCommand create and returns an order protest-related with the connection.
 Open  Opens the connection.

Figure 3 – IDbConnection Methods

Note that each Connection class of every data provider can have more properties and methods listed below which are unique to them. How about we utilize the SQL  Server as the data provider for the accompanying precedents. It implies that we will utilize the SqlConnection class which is the Connection class of SQL Server data provider. To create a Connection object, simply use its parameterless constructor.

SqlConnection connection = new SqlConnection();

We need to specify the proper connection string for the connection. To assign a connection string to the Connection object, you can use the ConnectionString property.

connection.ConnectionString = @"Data Source=.SQLEXPRESS;Initial Catalog=University;" +
 "Integrated Security=SSPI";

Alternatively, you can use the overloaded constructor of the SqlConnection class when creating an instance of  it.

SqlConnection connection = new SqlConnection(@"Data Source=.SQLEXPRESS;" +
 "Initial Catalog=University;Integrated Security=SSPI");

The connection string above indicates a connection with a SQL Server database utilizing the University as the initiating database with Window Authentication. The ConnectionString property is set just when the connection is closed.The Connection class offers various properties that entrance the distinctive segments of the connection string.

The Database property specifies the database to use. The initial database is shown by the Initial Catalog parameter of the ConnectionString. To change the database to use, you can call the ChangeDatabase method as shown in the following code.

connection.ChangeDatabase("AnotherDatabase");

The code changes our database from University(specified in the connection string) to  AnotherDatabase, provided that the  AnotherDatabase database exists.

The ConnectionTimeout property gets the estimation of the Connection Timeout parameter of the connection string. If it is not specified, it has a default value of 15. The Connection Timeout determines to what extent the connection needs to hold up before it tosses an exemption demonstrating that a connection has timed out.

To open a connection, we use the Open method. Make certain you have set the points of interest required in the connection string before calling this technique. After you have used the database, you can use the Close method. Note that you can utilize the using keyword to automatically close the connection. This is shown below.

using (SqlConnection connection = new SqlConnection())
{
   // some code....
   connection.Open();
   // some code....
}

We put the declaration and initialization between the parentheses of the using statement. This means the object created between these parentheses is only usable inside the block of the using statement. Once the closing blocked is reached, the Connection object is disposed of automatically closing the connection. If you are using the Close method, be sure to always put it in the finally block like this.

try
{
   connection.Open();
}
catch(SqlException)
{
}
finally
{
   connection.Close();
}

This is because, if you put it inside the try block, and an exception was thrown, then the statement that calls the Close method probably won’t be achieved leaving the connection open. If it is in the finally block, then the Close method will always be called. You can use the  State property to know the current state of a connection, whether it is close or open.  The State property accepts a value from the System.Data.ConnectionStateEnumeration. The following are the values of the ConnectionState enumeration.

 Value  Description
 Broken  States that the connection is broken.
 Closed  States that the connection is closed.
 Connecting  States that the connection is currently connecting to a data  source.
 Executing  States that the connection is executing a command.
 Fetching  States that the connection is retrieving data.
 Open  States that the connection is open.

Figure 4 – System.Data.ConnectionState Enumeration Values

The Connection class offers two events that you can use. The first is the  InfoMessage event which is utilized to get particular data messages. The StateChange event is activated when the connection state is changed.

static void con_StateChange(object sender, System.Data.StateChangeEventArgs e)
{
    Console.WriteLine("State has been changed from {0} to {1}.",
            e.OriginalState.ToString(), e.CurrentState.ToString());
}

static void Main()
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = @"Data Source=.SQLEXPRESS;Initial Catalog=University;" + 
                            "Integrated Security=SSPI";
    con.StateChange += new System.Data.StateChangeEventHandler(con_StateChange);
    con.Open(); // Opens a connection
    con.Close(); // Closes a connection
}
State has been changed from Closed to Open.
State has been changed from Open to Closed.

When we called the Open() method, the connection’s state was changed from  Close to Open, triggering the event handler that is attached to the  StateChange event. The occasion(event) handler prints a message that shows the OriginalState and CurrentState.. When we called the Close() method, the connection state was changed from  Open to Closed also triggering the  StateChange event.