A Closer Look at ADO.NET: The Connection Object

When developing database applications using .NET, the very first thing that we need is a connection to the database. ADO.NET provides us with connection classes like the SqlConnection class and OleDbConnection class. The SqlConnection class is part of the SQL Server .NET Data Provider. This data provider has been designed for performance optimization with SQL Server 7.0 and later.

The OleDbConnection is part of the OLEDB .NET Data Provider, which is used to access a data source that has an OLEDB Provider. In this article (and actually most of my articles on ADO.NET) I use the SQL Server .NET Data Provider. So let's talk a little about the SqlConnection class before we write code.

The SqlConnection class is part of the namespace System.Data.SqlClient (the namespace that forms the SQL Server .NET Data Provider). The Connection classes (SqlConnection, OleDbConnection and the other available classes like OracleConnection) implement the IDbConnection Interface, which has properties such as ConnectionString, ConnectionTimeout, Database, State. It also features methods such as Open(), Close(), BeginTransaction(), ChangeDatabase() and CreateCommand(). We will discuss all of these properties and methods.

You can be sure that there are common interfaces for all of the connection classes (through the IDbConnection), but of course each connection class can implement other interfaces that are needed for its operations. That means there are more methods than the methods of the IDbConnection Interface. To open a connection with the database we have to instantiate a SqlConnection object and set some properties (such as the ConnectionString), then call the Open() method to establish the connection with the database. The ConnectionString property takes a key/value pair string value that represents information about the server, database, user information and other information that is needed to connect to the specific server. After you set the ConnectionString property, you can open the connection. Let's look at an example.

The Example

Copy the following code and paste it into your VS.NET class file, then run the application.

using System;
// reference to the namespace that contains most
// of the classes that form the ADO.NET Architecture
using System.Data;
// referene to the namespace of the SQL Server .NET Data provider
using System.Data.SqlClient;

namespace AdoApp
{
  class Class1
  {
    static void Main(string[] args)
    {
      SqlConnection SqlConn1 = new SqlConnection();
      SqlConn1.ConnectionString = "Server=MichaelServer;Database=pubs;" +
"Integrated Security=true";
      // We use the Open() method to establish the connection
SqlConn1.Open();
      SqlCommand SqlComm1 = new SqlCommand();
      SqlComm1.Connection = SqlConn1;
      SqlComm1.CommandText = "Select * from titles";

      SqlDataReader SqlReader = SqlComm1.ExecuteReader();
      while(SqlReader.Read())
      {
        Console.Write(SqlReader["title_id"]);
        Console.Write("--");
        Console.Write(SqlReader["title"]);
        Console.WriteLine();
      }

      // here we call the Close() method to close the Connection
      SqlConn1.Close();
      Console.ReadLine(); 
    }
  }
}

You will get the following results to the console when you run this code example. Note that you need to have SQL Server installed in order to run this example.

In the above example, we have connected to the database pubs and issued a SQL Query (Select * from titles); we then printed out the results. For the purpose of our article I have instantiated two more objects in order to get the above results (to illustrate that we have established a connection to the database), but we will not discuss those objects in detail here. We will do that in another article. Let's take a look at the SqlConnection instantiation code:

SqlConnection SqlConn1 = new SqlConnection();
SqlConn1.ConnectionString = "Server=MichaelServer;Database=pubs;" +
"Integrated Security=true";
// We use the Open() method to establish the connection
SqlConn1.Open();

After we instantiated our SqlConn1 object, we set the ConnectionString property (which provides the information needed to connect to the database and begins manipulating its tables). Then we have opened the connection.

SqlCommand SqlComm1 = new SqlCommand();
SqlComm1.Connection = SqlConn1;
SqlComm1.CommandText = "Select * from titles";

SqlDataReader SqlReader = SqlComm1.ExecuteReader();
while(SqlReader.Read())
{
  Console.Write(SqlReader["title_id"]);
  Console.Write("--");
  Console.Write(SqlReader["title"]);
  Console.WriteLine();
}

// here we call the Close() method to close the Connection
SqlConn1.Close();

The above code creates the object SqlComm1 of type SqlCommand. Actually, after we establish the connection we need to issue or execute a query or a stored procedure, so we can think of them as commands to send to the SQL Server to execute (again, we will discuss commands in another article). The next statement that follows sets the connection that the command uses.

Of course we have set the SqlCommand.Connection property to our connection object SqlConn1. Next we need the command that we want to execute, which is a simple Select statement that selects all the rows of the table titles. Then we use an object of type SqlDataReader to read the records, and after that we close the connection. Now we need to discuss the ConnectionString property in detail.

The ConnectionString Property Setting

The Connection String value is used to provide information to connect to a specific SQL server instance. This information includes the server name, database name, the authentication mechanism, and some other important information such as connection timeout and network settings. You specify this type of information as a string value containing pairs of key/value separated by a semicolon (;). The order of listing these pieces of information is not critical; also, you can choose any capitalization you may like. Let's discuss some of the most important and commonly used Connection String Information.

Server: This is the name of the database server instance. Use (local) if the server is on your machine, so the Connection String will look like SqlConn1.ConnectionString = "Server=(local);Database=pubs;Integrated Security=true". I have used the server name MichaelServer because this is the server instance name on my machine, which is also called MichaelServer. To access a SQL server instance on your network, you need to qualify the instance name with the machine name. For example, if the machine is called MickMachine and the server is called MichaelServer, you set MickMachine\MichaelServer to the server. But most of the time you will find that both the machine name and the server name have the same value, and that's all you have to set.

Connection Timeout: The connection timeout is how many seconds the connection waits to establish the connection with the server before generating an error. The default value is 15 seconds. You can read the connection timeout (after you set it here in the connection string) through the ConnectionTimeOut property of a SqlConnection object.

Database: The database that will be used by the connection to issue commands.

Integrated Security: SQL Server uses two different ways to authenticate users. The first way is to use NT authentication in which SQL Server uses your Windows login to access the server (your Windows login must be authorized to access SQL Server in this case). The other way is to use SQL Server authentication, which can be set in the connection string using the next two keys. The integrated security key has the default value of false, which means that it will not be used to connect to the SQL Server, so if you want use it you have to set it to true as we did in our example.

User ID: This is the SQL Server user ID that will be used when you want to access the server using SQL Server authentication.

Password: The password that's associated with the server user ID. If it's an empty password you can use it as in the following Connection String, "Server=(local);Database=pubs;User ID=sa;Password=;"

Common Errors While Connecting

Although the code to establish a connection to the database is simple, many problems can happen. For example, the server doesn't exist or the database doesn't exist, which cause us problems when running the code. For this reason we will use the try/catch/finally statement to catch the exception and print the exception message to the console window. Here is what the code looks like:

using System;
// reference to the namespace that contains most
// of the classes that form the ADO.NET Architecture
using System.Data;
// referene to the namespace of the SQL Server .NET Data provider
using System.Data.SqlClient;

namespace AdoApp
{
  class Class1
  {
    static void Main(string[] args)
    {
      SqlConnection SqlConn1 = new SqlConnection();
      SqlConn1.ConnectionString = "Server=(local);Database=pubs;User ID=sa;Password=;";
      try
      {
        // We use the Open() method to establish the connection
        SqlConn1.Open();
        SqlCommand SqlComm1 = new SqlCommand();
        SqlComm1.Connection = SqlConn1;
        SqlComm1.CommandText = "Select * from titles";

        SqlDataReader SqlReader = SqlComm1.ExecuteReader(); 
        while(SqlReader.Read())
        {
          Console.Write(SqlReader["title_id"]);
          Console.Write("--");
          Console.Write(SqlReader["title"]);
          Console.WriteLine();
        } 

      }
      catch(Exception ex)
      {
        Console.WriteLine(ex.Message); 
      }
      finally
      { 
        // here we call the Close() method to close the Connection
        SqlConn1.Close();
        Console.ReadLine();
      } 
    }
  }
}

Now stop the SQL Server, then run the code, and you will get the next exception message printed to the console window after 15 seconds. 

In the Connection String, modify the database name from pubs to pub and run the application again.

As you can see, the try/catch/finally statement is good to use with the connection object. Note that we close the connection object in the finally block.

Using VS.NET to Create a SqlConnection Object

For the next example I will create a Windows application using VS.NET, then we will create a SqlConnection object without writing even one line of code. Create a Windows application using VS.NET and call it AdoApp2, then from the Toolbox select the Data tab.

As you can see, the Data tab contains many objects to connect and manipulate SQL Server, Oracle, ODBC and OLEDB sources. For our example we need to drag a SqlConnection object onto the form. When you drag that object onto the form, you will find that there's a panel on the bottom of VS.NET that contains the sqlConnection1 object.

We need to set the Connection String to the ConnectionString property of the SqlConnection1 object. We do that through the properties window. Note that the sqlConnection1 object on the panel must be active in order to get to its properties.

Now click the drop down menu to select a connection string or to create a new one. We will create a new one, so click on New Connection.

Now select the server name (which is MichaelServer on my machine), then select the radio button Use Windows NT Integrated Security. Next, select the database pubs. Now you are ready to test the connection through the button Test Connection. Click on it and you will get the following message box:

Click Ok then Ok. Now let's look at the code that has been generated for us behind the scenes.

In the InitializeComponent() method the following code has been generated for the Connection Object:

this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=MICHAELSERVER;packet size=4096;integrated security=SSPI;data sourc" +
"e=MICHAELSERVER;persist security info=False;initial catalog=pubs";

Of course there's a private member called sqlConnection1 in the Form1 class. Note that the generated code has not opened the connection yet. This is enough for now. In the next article we will discuss the Command object and look at more interesting code examples.