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

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.