How can I programmatically drop a SQL Server database from .NET code

Question

I'm trying to drop a SQL Server database from .NET code. I've tried using the SMO classes but get an exception saying the database is in use.

Then I tried executing a query (opening a SqlConnection, executing a SqlCommand), along the lines of:

ALTER DATABASE foo SET SINGLE_USER WITH ROLLBACK IMMEDIATE

(pause)

DROP DATABASE foo

But still I get an exception saying the database is in use.

How do I do this? (Or, how does SQL Server Management Studio implement the Drop database and close existing connections?)

Answer 1

This may be a stupid question, but in your connection string, are you connecting to the database you're trying to drop, or to the Master database on the same server? If you're trying to drop the database you're actively connected to, it would certainly explain the error.

Also, keep in mind that ADO.NET keeps connections open using connection pooling. So even though you aren't actively running queries using a given connection string, connections may still be open.

Answer 2

You need to make sure you're doing it from the master database:

USE master
GO

ALTER DATABASE foo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE foo
GO

Should work. By the way, SQL Server Management Studio allows you to view the script it generates for all command in the GUI. For example, in the "Delete" window, there's a "script" menu item at the top that'll show you the script you can use.