As the information about how to write software continues to grow on the internet a trend is emerging where software developers are able to write software without really understanding how what they have written works. They are able to copy code snippets from an example, modify it slightly to meet their needs, and plug it into their software without knowing the overall impact.
I was talking with a colleague last week about this very phenomenon. He had a team of developers working on an application using ADO.Net. They really didn’t understand how ADO connections to the database worked, and went under the assumption that Connection Pooling in Dot Net would solve all the pitfalls associated with using an SQL connection.
As we talked the pitfalls began to emerge. The application they were working on was a thick client app. They wanted to reduce the overhead of making a database connection by using Connection Pooling.
One individual felt it would be more efficient to create a database connection when starting the application and close the connection when the application finished. The thought was that there was too much time lost managing a connection pool if that single connection could be opened and closed each time. The application was not multi-threaded, at least for database activity; so the overhead of maintaining a connection pool was overkill.
While it is true that you could get a minor performance gain by only using and opening only one connection throughout the application lifetime, the gain is insignificant, and would be near impossible to measure. Moreover, by using that single connection, the connection utilization would have to be modified the first time they found a need to open a database connection on more than one thread.
Another member of the team considered ADO Connection Pooling to work like some sort of magic. It is turned on in a connection string by default; you have to specify that connection pooling is not to be used in the connection string in order to turn it off. So, since it is already turned on, I don’t have to worry about how it works.
The problem is that if you don’t close a connection when using connection pooling, then the connection does not go back to the pool for re-use, and continues to take database connection resources until the application ends. Since a connection pool has a default of 100 connections, again set in the connection string if you want something different than the default, you can easily have a number of abandoned connections in the connection pool.
When you close a connection using connection pooling the database connection is not actually closed. Instead the connection is placed back in the pool for re-use by the next client with the same connection string.
One way to be sure your connection is always closed is to enclose it in a “using” clause in either C# or VB.Net. When the using clause is completed the connection is automatically closed and returned to the connection pool.
Using (var con as new SqlConnection(constring)
{
…
some code using the connection
…
}
There are some scenarios when you don’t want to use connection pooling. You really just need to open and close a connection as needed. This may slow your application down a little bit, but once again, you are going to be hard pressed to measure the difference, especially on a fast network. Why not do a search on times when you may choose to not use connection pooling?
In short, simply because you found code on the internet that can show you how to do things like ADO.Net, they don’t necessarily show you how to use it efficiently. Simply finding syntax is not enough. As a professional you really need to understand how the tools you are using function, and use them according to their design for your problem.
Cheers,
Ben