0

I have created the following method that drops a database which is in use.
I came up with the following string query after looking this SO question. Note that the query runs successfully in the SQL Server Management Studio.

public static bool DeleteProject(string connectionstring, string dbname)
{
     string connString = connectionstring;
     string query = String.Format(
                "USE master;\n" +
                "GO\n" +
                "IF EXISTS(select* from sys.databases where name = '{0}') DROP DATABASE {0}; \n" +
                "GO", dbname);
     Debug.WriteLine(query);

     using SqlConnection conn = new SqlConnection(connString);

     try
     {
         if (conn.State == ConnectionState.Closed)
         {
             conn.Open();
         }

         SqlCommand cmd = new SqlCommand(query, conn);
         //conn.Open();

         return (cmd.ExecuteNonQuery() == -1);
     }
     catch (SqlException ex)
     {
          MessageBox.Show(ex.ToString(), "Test Connection", MessageBoxButton.OK, MessageBoxImage.Error);
          return false;
     }
}

This is how I call my method on the button.Click():

private void DeleteProjectButton_Click(object sender, RoutedEventArgs e)
{
    MessageBoxResult deletebutton = MessageBox.Show(String.Format("Would you like to delete the project database '{0}'? \n\n Click Yes to trigger the delete \n Click No to cancel the delete", HomePageTab.Header.ToString().Split(" - ")[1]), "Warning", MessageBoxButton.YesNo, MessageBoxImage.Warning);

    if (deletebutton == MessageBoxResult.Yes)
    {
        bool commandExecuted=DeleteProject(SQLServerConnectionDetails(), HomePageTab.Header.ToString().Split(" - ")[1]);

        if (commandExecuted)
        {
            MessageBox.Show(String.Format("Project database '{0}' successfully deleted!", HomePageTab.Header.ToString().Split(" - ")[1]), "Confirmation", MessageBoxButton.OK, MessageBoxImage.Information);
        }

        LoginScreen win_loginscreen = new LoginScreen();
        win_loginscreen.Show();
        this.Close();
     }
     else
     {
         return;
     }
}

Briefly when the user clicks the button a message box is presented.

  • If the user clicks Yes the database is dropped and the user is re-directed to the login screen.
  • But if the user clicks No then it returns in the same screen.

However, when I execute the whole thing I get the following error:

enter image description here

How can I solve this issue?

The issue persists despite the fact that the query is successful in the SQL Server Management Studio.

UPDATE

I made connection to "master" database instead of USE the master DB

string connString = "Server=Name;Database=master;Integrated Security=SSPI"

string query = String.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE; \n" + 
              "DROP DATABASE [{0}]", dbname);
LopDev
  • 823
  • 10
  • 26
NikSp
  • 1,262
  • 2
  • 19
  • 42
  • Does this answer your question? [GO statements blowing up sql execution in .NET](https://stackoverflow.com/questions/18596876/go-statements-blowing-up-sql-execution-in-net) – Sinatr Sep 30 '20 at 10:26
  • 1
    `GO` is not a T-SQL keyword. It is used by IDEs and CLIs (such as SSMS and `sqlcmd`) as a batch separator. It does *not* belong in application code. – Thom A Sep 30 '20 at 10:27
  • 1
    Side note, you check for the existence of the database *after* putting it into `SINGLE USER` mode. If the statement to put it into `SINGLE USER` mode succeeded, then you already know the database exists; it would have failed if it didn't. Checking the existence of the database *after* you have altered said database is pointless. – Thom A Sep 30 '20 at 10:29
  • You realise allowing the user to drop a database is rather unusual? Kind of dangerous even. Rather than a database per project, have you considered one database and project as a table in that? With ProjectId identifying data associated with that project. – Andy Sep 30 '20 at 10:38
  • Also. Connection string can dictate the database open. Why open the database you want to drop if you then need to switch to master? – Andy Sep 30 '20 at 10:39
  • @Andy The database to the application is like a project tab. So if the user wants to delete the project, they should also drop the database because they will fill Sql server with pointless databases. So I want a mechanism to permanently delete the database from the user's connected sql server. The server is not used by many but only by the user currently in the application (the user is connected to the local machine), – NikSp Sep 30 '20 at 10:45
  • @Andy and Larnu I edited my SQL query according to your comments. – NikSp Sep 30 '20 at 10:54
  • @Sinatr I have checked the question you posted. Indeed they imply the correct use of GO statement. Although they don't explain how to use this on SQL command. For example the first answer uses ```IEnumerable``` but inside an SQLCommand only strings are available. Any thoughts on this? :) – NikSp Sep 30 '20 at 10:56
  • @Andy I posted an update including solution – NikSp Sep 30 '20 at 12:53

0 Answers0