CODEWITHSUNDEEP
c#
Kashif Hanif
Kashif Hanif

Reputation: 1728

How To Delete SQl Server database while it is "In Use" using C#?

I am trying to delete sql server database. It gets deleted if it is not in use. If database is in use an exception occurred saying that can not delete database while it is in use. We can delete use database from management studio using option Close existing connection. But how can we do this using C# code?

Upvotes: 1

Views: 1869

Answers (2)

user1711092
user1711092

Reputation: 

ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

DROP DATABASE AdventureWorks2012;

Make a script file by writing above query in it.Then execute this script file as below :

string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";
            FileInfo file = new FileInfo("C:\\myscript.sql");
            string script = file.OpenText().ReadToEnd();
            SqlConnection conn = new SqlConnection(sqlConnectionString);
            Server server = new Server(new ServerConnection(conn));
            server.ConnectionContext.ExecuteNonQuery(script);

Upvotes: 3

Pranav
Pranav

Reputation: 8871

The easy way on a MS SQL Server to kick all users off is:

ALTER DATABASE [MyDB] SET Single_User WITH Rollback Immediate
GO

Fire this Query through your code and go ahead:-

SOURCE:-

Upvotes: 2

Related Questions