Deleting database from C#

问题:

I have a MDF file that I'm attaching to my local SQL server during testing with MSTEST and I don't want to have to go delete those temporary databases by hand after I've run the test set 50 times. (I've already done that and I don't like it >.<) I'm look for a way to delete the database from the server after I'm done with the tests, during my TestCleanup method. I just need a little guidance on what SQL statements I would use to do this.

Thoughts?

Thx in advance! :D

EDIT (By Software Monkey, from OP's rejected edit to ODED's answer)

Here is the code which worked for me:

var server = new Server(serverName); // Can use overload that specifies 

foreach (Database db in server.Databases)
{
     if (db.Name.ToLower().Contains(testDatabaseIdentifier))
     {
          databasesToDelete.Add(db.Name);
     }
}
databasesToDelete.ForEach(x =>
{
     Database db = new Database(server, x);
     db.Refresh();
     db.Drop();
});

回答1:

Take a look at the SMO (SQL Server Management Objects) .NET wrappers.

These allow you to manage all aspects of SQL Server from code, including deleting of databases.

The database object has a Drop method.

The code below is to illustrate how you could use the object model, though I have not tested it:

var server = new Server(serverName); // Can use overload that specifies 

foreach (Database db in server.Databases)
{
     if (db.Name.ToLower().Contains(testDatabaseIdentifier))
     {
          databasesToDelete.Add(db.Name);
     }
}
databasesToDelete.ForEach(x =>
{
     Database db = new Database(server, x);
     db.Refresh();
     db.Drop();
});

回答2:

Try with this:

sqlCommandText = "DROP DATABASE [NAME]";
sqlCommand = new SqlCommand(sqlCommandText , connection);
sqlCommand.ExecuteNonQuery();

I think this would help.

回答3:

Instead of using the Database type to delete a database in TestCleanup, I would recommend to use the Microsoft.SqlServer.Management.Smo.Server.KillDatabase method. In addition, this will close all existing SQL connections before the database is deleted. Thus your unit tests (or rather integration tests) might leave connections open and this will have no effect on the cleanup method.

var server = new Server(SqlServerName);
server.KillDatabase(DatabaseName);

回答4:

feO2x's answer works great but he didn't give code. The following works if you have your database connection string in your app.config.

using System.Configuration;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
public class Foo
{
    public static void DropDatabase(string connectionName)
    {
        using (
            var sqlConnection =
                new SqlConnection(
                    ConfigurationManager.ConnectionStrings[connectionName]
                    .ConnectionString))
        {
            var serverConnection = new ServerConnection(sqlConnection);
            var server = new Microsoft.SqlServer.Management.Smo.Server(
                             serverConnection);
            server.KillDatabase(sqlConnection.Database);
        }
    }
}

You must reference System.Data, *System.Configuratio*n, Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk.Sfc, and Microsoft.SqlServer.Management.Smo.

回答5:

Here is how you do it using Entity Framework version 6

System.Data.Entity.Database.Delete(connectionString);

回答6:

I. Create Resources file (Resources.rsx) and write sql queries:

• SQL_KillConnections

---

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'{0}'
DECLARE @SQL varchar(max)
SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
EXEC(@SQL)

• SQL_DropDatabaseIfExists

---

IF EXISTS(select * from sys.databases where name='{0}')
DROP DATABASE [{0}]

II. Add MsSqlDatabaseTestsHelper class into the test project

public class MsSqlDatabaseTestsHelper
{
    private readonly string _connectionString;

    public MsSqlDatabaseTestsHelper(string connectionString)
    {
        _connectionString = connectionString;
    }

    private void ExecuteNonQuery(string sql)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = sql;
            command.ExecuteNonQuery();
        }
    }

    public void CreateDatabase(string databaseName)
    {
        ExecuteNonQuery("CREATE DATABASE {0}".Set(databaseName));
    }

    public void DropDatabase(string databaseName)
    {
        try
        {
            ExecuteNonQuery(Resources.SQL_KillConnections
                .Set(databaseName));
        }
        catch (Exception)
        {
            throw new Exception("Can't kill database '{0}' connections"
                .Set(databaseName));
        }

        try
        {
            ExecuteNonQuery(Resources.SQL_DropDatabaseIfExists
                .Set(databaseName));
        }
        catch (Exception)
        {
            throw new Exception("Can't drop database '{0}'"
                .Set(databaseName));
        }
    }
}

III. Use database helper in your unit tests

[TestFixture]
public class CmsPageRepositoryTests
{
    private readonly MsSqlDatabaseTestsHelper _msSqlDatabaseTestsHelper = 
        new MsSqlDatabaseTestsHelper(ConnectionStringWithoutDatabase);

    private const string ConnectionStringWithoutDatabase = 
        @"server=.\SqlExpress;uid=sa;pwd=1;";

    private const string DatabaseName = "TestPersistence";

    [SetUp]
    public void SetUp()
    {
        _msSqlDatabaseTestsHelper.DropDatabase(DatabaseName);
        _msSqlDatabaseTestsHelper.CreateDatabase(DatabaseName);
    }

    [TearDown]
    public void TearDown()
    {
        _msSqlDatabaseTestsHelper.DropDatabase(DatabaseName);
    }

    [Test]
    public void TestSomethingWithDatabaseUsing()
    {
    }
}

回答7:

or try this

       var  sqlCommandText = "DROP DATABASE [dbName]";
       var sqlConnection = new SqlConnection(connectionstring);
       var sqlCommand = new SqlCommand(sqlCommandText, sqlConnection);
       sqlConnection.OpenAsync();
       sqlCommand.ExecuteNonQuery();