CODEWITHSUNDEEP
c#sql-servermstest
khr055
khr055

Reputation: 29032

Deleting database from C#

I have an 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.

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

Upvotes: 15

Views: 35762

Answers (7)

Digital_Jo4
Digital_Jo4

Reputation: 54

or try this 

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

Upvotes: 0

Georgy Batalov
Georgy Batalov

Reputation: 499

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()
    {
    }
}

Upvotes: 3

Bassem
Bassem

Reputation: 3006

Here is how you do it using Entity Framework version 6

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

Upvotes: 6

mheyman
mheyman

Reputation: 4325

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.

Upvotes: 6

feO2x
feO2x

Reputation: 5728

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

Upvotes: 8

Oded
Oded

Reputation: 498904

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

Upvotes: 17

buda
buda

Reputation: 2372

Try with this:

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

I think this would help.

Upvotes: 10

Related Questions