September 17, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Using Connection Pooling in ADO.NET

  • February 25, 2008
  • By Bipin Joshi
  • Send Email »
  • More Articles »

The GetConnectionString() method returns a string representing the database connection string. Inside, it uses the SqlConnectionStringBuilder built-in class to build the connection string. It is assumed that you have Northwind database installed on your SQL server. The code simply sets properties of SqlConnectionStringBuilder class. Notice the Pooling, MinPoolSize, and MaxPoolSize properties. These properties will add the appropriate connection string attributes.

Now, add the following code in the Click event handler of "Open a connection" button:

private void button3_Click(object sender, EventArgs e)
{
   string connstr = GetConnectionString();
   SqlConnection cnn = null;
   try
   {
      cnn = new SqlConnection(connstr);
      cnn.Open();
      MessageBox.Show("Connection Opened!\r\
                       nConnection String:\r\n" + connstr);
   }
   catch (Exception ex)
   {
      MessageBox.Show(ex.Message);
   }
   finally
   {
      cnn.Close();
   }
}

Here, you retrieve the database connection string by using your helper method GetConnectionString(). Then, you open a connection with the Northwind database. Just to see how the connection string has been built, you display it in a message box. Note the use of try-catch-finally block. This guarantees that the connection is always closed (even if there is any exception) and returned to the pool.

Then, handle the Click event of the "Open" button as shown below:

private void button1_Click(object sender, EventArgs e)
{
   SqlConnection[] conn = null;
   try
   {
      int count = int.Parse(textBox1.Text);
      conn = new SqlConnection[count];
      string connstr = GetConnectionString();
      for (int i = 0; i < count; i++)
      {
         conn[i] = new SqlConnection(connstr);
         conn[i].Open();
      }
      MessageBox.Show("Connection Opened!\r\
                       nConnection String:\r\n" + connstr);
   }
   catch (Exception ex)
   {
      MessageBox.Show(ex.Message);
   }
   finally
   {
      for (int i = 0; i < count; i++)
      {
         if (conn[i] != null)
         {
            conn[i].Close();
         }
      }
   }
}

Here, you declare an array of SqlConnection objects. Depending on the number of connections to open (as specified in the textbox), a for loop instantiates and opens the connections. The finally block simply closes them again.

Now, it's time to run the application and observe the connection pooling behavior. Run the application and uncheck the "Enable connection pooling" checkbox to disable the connection pooling. Then, click the "Open a connection" button. If the connection is opened successfully, you should see a message box as shown below:

Figure 2: The connection has opened

Notice how the Pooling attribute of the connection string is set to False. Keeping the message box open, run SQL Server Management Studio and execute the SP_WHO stored procedure.

EXEC SP_WHO

The results of SP_WHO stored procedure are shown below:



Click here for a larger image.

Figure 3: The results of the SP_WHO stored procedure

Notice the highlighted row. This indicates your open database connection. Because the connection pooling is disabled, only one database connection got created. Now, click the OK button of the message box so that the connection is closed. Run the SP_WHO stored procedure again. This time, you will find that the connection has vanished; this indicates that closing the connection also closes the database connection.





Page 2 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel