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

Question

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?

Could this be useful to you : stackoverflow.com/questions/1145892/… ? — Andy M
– Andy M, Commented Nov 5, 2012 at 7:16

user1711092 · Accepted Answer · 2012-11-05 07:37:36Z

3

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

answered Nov 5, 2012 at 7:37

user1711092

Sign up to request clarification or add additional context in comments.

2 Comments

Pranav Over a year ago

why you need to create seperate .SQL for this,you can just Execute it through writing query in string itself..:):P

Kashif Hanif Over a year ago

Mr Wajid Ali thanks for Ur help.it has solved my 2 days problem.it work fine for me.thanks again.......i was only trying to set db offline but it was not working......"With role back immediate" it has worked for me as suggested by you.thanks again.

Community · Accepted Answer · 2017-05-23 10:25:11Z

2

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:-

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Nov 5, 2012 at 7:20

Pranav

8,9214 gold badges28 silver badges42 bronze badges

1 Comment

Kashif Hanif Over a year ago

"Fire this Query through your code and go ahead:" yes Pranav it work for me......i am too happy.thanks for help

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related