How to close all existing connections to a DB programmatically

Question

I want to close the existing connections to an SQL Server so that I can do a restore on that database. I am using the entity framework. I tried executing

alter database YourDb 
set single_user with rollback immediate

but then I get an exception saying that

Connection was not closed

I can not figure out why the connections are not allowed to close?

This image shows the full exception

this is the method,

 public void dbQueueryExctr(string queuery)
        {
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;


            using (SqlConnection connectionx = new SqlConnection(CONNECTIONSTRING))
            {

                connectionx.Open();
                //connectionx.Open(); // Removed
                cmd.CommandText = queuery;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = connectionx;

                reader = cmd.ExecuteReader();
                connectionx.Close();


            }

Edit: I removed the first .Open(). Now I have only Open()

Answer 1

It does seem that Entity Framework keeps a connection to the database. You can see it be executing sp_who2 in SQL Server Management Studio where Entity Framework is listed as EntityFrameworkMUE under ProgramName.

You don't have to use "raw" sql statements to disconnect the active connections though, it can be solved this way as well:

Server server = new Server(".\\SQLEXPRESS");
Database database = new Database(server, dbName);
database.Refresh();
server.KillAllProcesses(dbName);
database.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
database.Alter(TerminationClause.RollbackTransactionsImmediately);

//restore.SqlRestore(server);

Answer 2

You get that error when you are call Open() on a connection twice. You should make all SqlConnection objects you create inside using blocks and only open them once.

If you are reusing connections "to make it faster" .NET already does that by default for you via Connection Pooling but you must dispose of the connection object to make it work.

Answer 3

You need to dispose the reader, the command and the connection. Your reader is not disposed. This code snippet will guarantee that the connection is closed even if there are exceptions thrown during the read process.

using (var conn = new SqlConnection("..."))
{
    conn.Open();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "Command text.....";
        using (var reader = cmd.ExecuteReader())
        {
           ....
        }
    }
}

Answer 4

Your first problem (now that you have posted your code) is you call open twice:

    public void dbQueueryExctr(string queuery)
    {
        SqlCommand cmd = new SqlCommand();
        SqlDataReader reader;


        using (SqlConnection connectionx = new SqlConnection(CONNECTIONSTRING))
        {

            //YOU CALL OPEN HERE
            //DELETE THIS ONE!!!
            connectionx.Open();
            cmd.CommandText = queuery;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = connectionx;

            //AND OPEN HERE
            connectionx.Open();

            reader = cmd.ExecuteReader();
            //You do not need connectionx.Close() here
            //You have it within a using which will dispose the connection
            //upon exiting the using scope.
            connectionx.Close();


        }

Next your problem will require you to reset the database to force close all connections. You will have to use a separate connection string to connect to MASTER not the database you are trying to close all connections to.

    alter database <data base>
           set offline with rollback immediate 

    alter database <data base>
           set online with rollback immediate

Once you have executed the above SQL from MASTER against the database needing reset you should be good to do whatever you need to do. Remember, connect to master!! If you connect to the database you are trying to reset you end up closing all connections, including yourself, which will not work!

Change your Catalog to master.

Example Connection String (from MSDN):

"Persist Security Info=False;Integrated Security=true;Initial Catalog=Master;server=(local)"

Also ensure the SQL User you are using has full permissions to master. You do this by opening management studio and looking at the users collection under master.

Answer 5

The error is pretty clear...using Linq that way, you can't close the connection you are currently on. I haven't tried this but I think the following would work...try creating a stored procedure in your database and run it in your C# code using either a TableAdapter or SqlCommand(you can still use Linq too). Your code won't know you are about to run a stored procedure that is about to kill it's connection so it should work.

CREATE PROCEDURE [dbo].[sp_KillSpidsByDBName] 
@dbname sysname = ''
AS
BEGIN

-- check the input database name
IF DATALENGTH(@dbname) = 0 OR LOWER(@dbname) = 'master' OR LOWER(@dbname) = 'msdb'
RETURN

DECLARE @sql VARCHAR(30) 
DECLARE @rowCtr INT
DECLARE @killStmts TABLE (stmt VARCHAR(30))

-- find all the SPIDs for the requested db, and create KILL statements 
--   for each of them in the @killStmts table variable
INSERT INTO @killStmts SELECT 'KILL ' + CONVERT (VARCHAR(25), spid)
FROM master..sysprocesses pr
INNER JOIN master..sysdatabases db
ON pr.dbid = db.dbid
WHERE db.name = @dbname

-- iterate through all the rows in @killStmts, executing each statement
SELECT @rowCtr = COUNT(1) FROM @killStmts
WHILE (@rowCtr > 0)
    BEGIN
        SELECT TOP(1) @sql = stmt FROM @killStmts
        EXEC (@sql)
        DELETE @killStmts WHERE stmt = @sql
        SELECT @rowCtr = COUNT(1) FROM @killStmts
    END

END

GO

Now you can run this stored procedure from code and it will kill open connections even your own. Enjoy!

Answer 6

It is good practice to check to see if the connection is open before attempting to open it. Try adding a check before trying to open your connection, something like this:

using (SqlConnection connectionx = new SqlConnection(CONNECTIONSTRING))
{

    if(connectionx.State != ConnectionState.Open
        connectionx.Open();
    cmd.CommandText = queuery;
    cmd.CommandType = CommandType.Text;
    cmd.Connection = connectionx;

    reader = cmd.ExecuteReader();

    connectionx.Close();


}

This will help prevent the issue you described.

Answer 7

You can use SqlConnection.ClearAllPools and SqlConnection.ClearPool to close all or one connection in from .NET.

ClearPool clears the connection pool that is associated with the connection. If additional connections associated with connection are in use at the time of the call, they are marked appropriately and are discarded (instead of being returned to the pool) when Close is called on them.

ClearAllPools resets (or empties) the connection pool. If there are connections in use at the time of the call, they are marked appropriately and will be discarded (instead of being returned to the pool) when Close is called on them.

for examples:

using(var comm = new SqlConnection())
  using(var comExecuteInsert = new SqlCommand())
  {
    comExecuteInsert.Connection = comm;
    comExecuteInsert.CommandType = CommandType.StoredProcedure;
    comExecuteInsert.CommandText = strProcedureName;
    comExecuteInsert.ExecuteScalar();
    comExecuteInsert.Parameters.Clear();
    comm.Close();
  }    

SqlConnection.ClearAllPools();

Answer 8

Once exam this way, this is my Data access layer samples:

    public T ExecuteScalar<T>(SqlCommand cmd, params SqlParameter[] Params)
    {
        try
        {
            if (Transaction != null && Transaction != default(SqlTransaction))
                cmd.Transaction = Transaction;
            else
                cmd.Connection = SqlConn;

            if (Params != null && Params.Length > 0)
            {
                foreach (var param in Params)
                    cmd.Parameters.Add(param);
            }

            Open();

            var retVal = cmd.ExecuteScalar();

            if (retVal is T)
                return (T)retVal;
            else if (retVal == DBNull.Value)
                return default(T);
            else
                throw new Exception("Object returned was of the wrong type.");

        }
        finally
        {
            Close();
        }

    }