How to close all existing connections to a DB programmatically

c# database entity-framework restore sql-server

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

enter image description here

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()

1
10
10/20/2017 8:39:58 AM

Accepted Answer

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.

8
5/7/2015 5:27:20 AM

Popular Answer

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);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow