Menu
I have an application that is causing a lot of headaches. It's a .NET app connecting to SQL Server 2005 via a web service. The program has grid that is filled by a long running stored procedure that is prone to timing out. In the case when it does time out and a SqlException is thrown, there is no execption handling to close the connection.
What are the actual consequences of this condition? I think that the framework or SQL Server probably takes care of it one way or another but am not sure.
Addition The program always works well in the morning, but after an hour or so of use it basically stops working. The issue isn't that I don't know how to code the connection properly. I need to know if these symptoms could be casued by the unclosed connections. It is kind of a big deal to change the production code and I would like to know that it is at least possible for this to be the issue.
Conclusion I engineered this failure to occur on hundreds of simultaneous connections. Never was I able reproduce the failure condition in the application environment. Marked best practices answer as correct. Thanks everyone.
941
If we don't close the connection, it will lead to connection memory leakage. Until application server/web server is shut down, connection will remain active, even if the user logs out.
This is a basic step that you should consider to be part of mandatory code. If you do not close your database connections, many problems can occur like web pages hanging, slow page loads, and more. Think of it as going through a door to your house. Maybe the door will shut by itself, but maybe it won't.
It will automatically close when the PHP script is done running during destruct phase. Performance will negatively be affected. Opening a new socket (especially to an external database server) is more expensive and time consuming than just keeping a pointer to the current connection in memory.
At the end of your JDBC program, it is required explicitly to close all the connections to the database to end each database session. However, if you forget, Java's garbage collector will close the connection when it cleans up stale objects.
Since a SqlConnection closes while disposing i usually use this syntax
using (SqlConnection conn = new SqlConnection())
{
// SqlCode here
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
