October 25, 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 »

Now, repeat the same process by keeping the "Enable connection pooling" checkbox checked. Here are the results of SP_WHO:



Click here for a larger image.

Figure 4: The results of SP_WHO

This time, although you open just one connection, five connections are opened with the database! This is because you have set the MinPoolSize attribute to 5. Moreover, these connections are held open even if you click the OK button of the message box. That means even if you close the connections from your code, they are returned to the pool without actually closing the database connections.

Now, run a second instance of your application and repeat the above process. The SP_WHO now reports 10 open connections. This is because a connection pool is maintained for a process.

At last, the final test. Enter 20 in the "No. of connections" textbox (recollect that you have set MaxPoolSize to 10) and click the Open button. After 15 seconds, you get an exception as shown below:



Click here for a larger image.

Figure 5: The connection was not established

This indicates that, because your number of connections exceed the maximum pool size, there was no connection available to serve the request. The data provider waited for the default timeout period of 15 seconds and then threw an exception.

The Using Block

In the preceding example, you used the finally block to close the database connection. A more elegant way, however, is to use a using block. The following code shows how:

using(SqlConnection cnn=new SqlConnection(connstr))
{
   cnn.Open();
   //some other work
}

Here, you need not call the Close() method at all because the using block automatically calls the Dispose() method on the connection object once the block is complete. The Dispose() method, in turn, does the appropriate cleanup job for you.

Summary

Database connection pooling can significantly improve performance. In ADO.NET, it is enabled by default. You can fine tune it using various connection string parameters. The Pooling, MinPoolSize, and MaxPoolSize parameters allow you to configure connection pooling behavior. It is recommend tthat you play with these attributes to arrive at an exact optimized value for your specific application.

Download the Code

You can download the code that accompanies the article here.

About the Author

Bipin Joshi is the proprietor of BinaryIntellect Consulting, where he conducts premier training programs on .NET technologies. He wears many hats, including software consultant, mentor, prolific author, webmaster, Microsoft MVP, and member of ASPInsiders. Having adopted the Yoga way of life, Bipin also teaches Kriya Yoga to interested individuals. His detailed profile can be read on his blog. He also can be reached there.





Page 3 of 3



Comment and Contribute

 


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

 

 


Sitemap | Contact Us

Rocket Fuel