Stefano Demiliani WeBlog

ADO.NET and Connection Closing

I've received a question by Learner (this is the nick) on my blog regarding ADO.NET and Connection Closing. He asks: "is there a way to CLOSE or DISPOSE any and all open connections - globally? OR to see whats open?"

I want to try to give an answer this question on a new post because it's a bit curious and I'm not sure on how to answer directly to it... why having many connections opened at the same time to a database? Are you sure you want it?

Remember that if you are using Datasets, the connection is closed when you fill it, but if you are using a DataReader you must close the connection explicitly (if you don't do this, the Garbage Collector takes control of this...).

Usually, I use the guide line I've read on a book (I don't remember the title now, maybe "ADO.NET Core Reference"??) that says something like this: "Think disconnected, open it, use it, and close it as fast as you can".

However, in ADO.NET 1 if you have 2 connections (sqlCon1 and sqlCon2) with 2 different databases, I don't know a direct way to close simultaneously the connections... I think you have to do sqlCon1.Close() and sqlCon2.Close(), no way to clear the connection pooling. Someone can say something about?

In the future ADO.NET 2, new connection pooling enhancements will be available... They will allow you to programmatically close all the connections currently kept alive by the pooler. You can clear a specific connection pool by using the static (shared in Visual Basic .NET) method SqlConnection.ClearPool or clear all of the connection pools in an appdomain by using the SqlConnection.ClearPools method. I think this is what you want...

I hope to have feedbacks and ideas about this from the community...