Spring.net Drop all adotemplate connections?

Question

I have an application which is connected to a database through a spring.net AdoTemplate. I am charged with creating a restore database method which keeps the app running but drops the network connections so as to drop the old database and bring up the new one. My question is how do I drop all the current connections that this application has to this AdoTemplate? I do not see any public method in spring 1.1 to drop the network connections.

Answer 1

Hi,

there is no physical "connection" between AdoTemplate and the SQL database. Leaving transactions aside, AdoTemplate creates a new SqlConnection object for each method that is executed from ADO.NET, executes a command and disposes the SqlConnection object after that. Under the hoods, ADO.NET caches physical connections to the database in a pool. When you create a new SqlConnection object, 1 of those cached physical connections is obtained from the pool to serve that SqlConnection. This means, that you will need a different strategy for solving your problem. One strategy coming to my mind is to obtain the list of active connections from the sysprocesses database and execute the KILL statement on them. Short googling brought up this article. Note, that this article refers to mssql 2000. I'm pretty sure, that you need to google a bit more to find a solution for 2005. Since 2005 it isn't allowed to access system tables anymore asfair.

hth, Erich

Answer 2

What ended up working great was:

   SqlConnection.ClearAllPools();
   // if any connections were being used at the time of the clear, hopefully waiting
   // 3 seconds will give them time to be released and we can now close them as well
   Thread.Sleep(3000);
   //clear again
   SqlConnection.ClearAllPools();