>>The fact that you have not yet experienced the problem with other connection types is no proof that you won't
>>But, more importantly, where will you close those connections ?
>
>That is a very important question and I have been wondering when someone would ask it.
>
>The framework exposes the dataset after a SQLExec() command or execute a SQLUpdate(), SQLDelete() or SQLInsert(). So, at least, for the first one, I cannot close the connection at the framework level. Because, correct me if I am wrong, but, if I close the connection, the DataSet will become out of scope. So, for now, there is no close. The next step, before seeing if I have to handle that, was to check the memory and see if this is really necessary or not.
>
>Based on your experience, what will happen on 12 new connections created during a hit for example. As they belong to the hit, they should go out of scope.
If the connection goes out of scope without being explicitly closed it will not be returned to the pool of available connections until the Dispose() is called - which, in turn, will not happen until the GC gets around to it - potentially quite a long time. (Actually that is a bit of an over-simplification - there are circumstances where it might be reclaimed but if those circumstances occur there's something wrong anyway)
If you do open a connection explicitly (rather than, as Bonnie rightly suggests, relying on higher level objects to handle it implicitly) the safest option is:
Using sc As New SqlConnection(connectionString)
sc.Open()
End Using
which will guarantee that the connection is closed (and thus returned to the pool) when the block is exited. This will be true even if an exception is thrown within the block.