c# application connected to the database - cannot detach

Question

When I create new database with C# and create a table, I am able to close and open connection to it. If I try to detach the database in the same session of my application, it tells me I can't. If I restart the application, then open the database, then close and detach - It works. Why is this happening and how can I solve it?

P.S. I have tried with SET OFFLINE WITH ROLLBACK IMMEDIATE, which works, but changes my database permissions, and I really don't want this.

My connection string:

myConn.ConnectionString = String.Format("Server={0};Integrated security=SSPI;", serverName);

My detach string:

str = string.Format("EXEC sp_detach_db '{0}'", databaseName);

Answer 1

You cannot detach a database that is in use.
So if you are connected to the same database that you want to detach, then switch to database master first.

str = string.Format("use master GO EXEC sp_detach_db '{0}'", databaseName);

Answer 2

Hello friend are you using : "Microsoft.SqlServer.Management.Smo;" ?
if NO then use it to detach your database

Server MyServer = new Server("your server name"); MyServer .DetachDatabase("name of database",true);

if yes please specify your question