How to detach database successfully in any case using code?

Question

5.00/5 (1 vote)

See more:

I know the following code can be used to detach a Sql server database:

C#

SqlCommand com = new SqlCommand("USE master; exec sp_detach_db mydatabase", con);
com.ExecuteNonQuery();

But it doesn't always work, just for some cases the database is currently in use without knowing why and what process is using it. If using Sql Server manager, I can refresh all the databases and can execute the detach stored proc successfully. But I want to detach the database (whenever I want) using code, could you please give me any solution?

I discovered that my database was really in use, because I had used sql connection to read data from it, however I tried diposing all sql stuffs (sql connection, sql dataadapter...) before detaching but it still throws the exception. Could you have any ideas on how to make the database not currently be in use any more?
Your helps would be highly appreciated!
Thank you very much!

Posted 4-Nov-11 13:23pm

King Boy

Updated 4-Nov-11 13:40pm

Wonde Tadesse

v2

Add a Solution

2 solutions

Solution 1

Hello,

Follow this link "Programmatically Enumerating, Attaching, and Detaching SQL Server Databases[^]

Posted 4-Nov-11 18:55pm

deepakdynamite

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Boy · Accepted Answer · 2011-11-05T03:15:00

Thank you for the good link.
I have read it before posting my question here. Moreover, that's a guide through how to detach, attach database using code when the database is not currently in use. My situation is another problem, We have to make the database not be in use first.

I found some solutions here, however sometimes they works very slowly:

SQL

ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
or
ALTER DATABASE mydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
or
Declare @DBname sysname
Set @DBname = 'mydatabase'
Declare @spid int
SELECT @spid = MIN(spid) FROM master.dbo.sysprocesses WHERE dbid = db_id(@DBname)
WHILE @spid is not null
BEGIN
EXECUTE('Kill ' + @spid)
SELECT @spid = MIN(spid) FROM master.dbo.sysprocesses WHERE dbid = db_id(@DBname) AND spid > @spid
END

USE MASTER before one of snippets above and then use the detach command.
If there are some connections to the database, they work really slow!
Thank you!