CODEWITHSUNDEEP
.netsql-serverlocaldb
Ben Collins
Ben Collins

Reputation: 20686

Using auto-configured localdb for unit testing; how do I clean up?

I've configured an .mdf file along with a localdb connection string for use in unit tests, like this:

<connectionStrings>
        <add name="TestData" providerName="System.Data.SqlClient" connectionString="Data Source=(localdb)\v11.0; AttachDBFilename='|DataDirectory|\TestData.mdf'; Integrated Security=True"/>
</connectionStrings>

Once I've configured deployment files for my test correctly, this works beautifully: a copy of the .mdf is attached to the default instance of LocalDB, and the SqlClient connects to it without a shred of configuration. It just works.

But how do I clean up afterwards? On my local box, I can periodically use SSMS to manually detach old testing databases, but on a CI server it would obviously be preferable to have the unit test clean itself up.

Is there a similarly automagic way to cause a localdb database to detach itself from the instance?

Upvotes: 15

Views: 5064

Answers (5)

Ben Gribaudo
Ben Gribaudo

Reputation: 5147

Will the unit test framework you're using allow you to attach a clean up method (i.e. AssemblyAttribute in MS Test, OneTimeTearDown in NUnit 3, shared context in XUnit)? If so, you could wire in something like the below to automatically detach the database at the conclusion of testing.

public static void CleanUp()
{
    using (var connection = new SqlConnection(ConnectionString))
    {
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            var sql = "DECLARE @dbName NVARCHAR(260) = QUOTENAME(DB_NAME());\n" +
                "EXEC('exec sp_detach_db ' + @dbName + ';');";
            command.CommandText = sql;
            command.ExecuteNonQuery();
         }
    }
}

Upvotes: 1

MytyMyky
MytyMyky

Reputation: 608

Yes, but you might not need it.

I'm currently using a process where I attach the attach the db the way wbx911/ Tim Post have it in their answer. But you don't need to dettach them if you are attaching a copy of the original db (for instance a copy created by the project/solution build). If no connection is attached to the db, it will get overriden on the next test run.

If you have a lot of copies of the database(s) attached or just wan't to clean up everything that is attached, than you can use a stored procedure like the following, attached to the localDb instance's master database (an example of what I personally use):

CREATE PROCEDURE [dbo].[DettachTestDatabases]
AS

BEGIN
DECLARE  @imax INT, 
         @i    INT 
DECLARE  @Name VARCHAR(255)

DECLARE  @DbsToDrop TABLE(RowID INT IDENTITY ( 1 , 1 ), 
                          name VARCHAR(255)
                         ) 

INSERT @DbsToDrop
SELECT [name]
  FROM [master].[sys].[databases]
  WHERE 
    --Remove all local dbs with *SomeText*.mdf or SomeOtherTextin name
    (name like '%SomeText%.mdf' or name like '%SomeOtherText%')
    -- Exclude VS test dbs (add more as required...)
    and (name not like '%TESTS.%');

SET @imax = @@ROWCOUNT 
SET @i = 1 

WHILE (@i <= @imax) 
  BEGIN 
        SELECT @Name = name 
        FROM   @DbsToDrop 
        WHERE  RowID = @i

        EXEC master.dbo.sp_detach_db @dbname = @Name;

        PRINT 'Dettatched ' + @Name;

        SET @i = @i + 1;
  END   --while

END --sp
GO

I'm Using a naming convention to determine which dbs to drop (therefore the like statements). Visual studio attaches a couple of db (or at least I think they belong to VS) to manage test results, that I don't want to detach, (therefore the "not like" condition). ther is a master.dbo.sp_detach_db that detatches by name so you really only need to refere the name, but since localdb names can be kinda wild, a lookup will be useful.

I haven't automated this yet at the end of my tests because I haven't felt the need to (since overwrites are working ok), but I can execute it in SSMS anytime i want or need to. But if you feel the need to , You would just need to manage a call to the SP from your clean up code.

Attaching the dbs are somewhat an expensive process. I've opted for a cleanup method / SP that removes any variable data and reseeds tables to put the db in a consistent state, which is pretty fast, instead of reattaching the database.

If you find any points of improvement I would realy like to know about them!

Upvotes: 1

phil
phil

Reputation: 638

This is how I am deleting the localDB database. The thing I don't like is that the .mdf is also removed. I overcome that by copying it to a tem directory first and using the copy to create the db.

var sc = new Microsoft.SqlServer.Management.Common.ServerConnection(your localDB SqlConnection here);
var server = new Microsoft.SqlServer.Management.Smo.Server(sc);
server.KillDatabase(dbName here);

Hope this helps Phil

Upvotes: 5

dagilleland
dagilleland

Reputation: 9

If you're talking unit tests, like with xUnit, there are attributes you can add to the tests themselves to do a rollback on any that touch the database.

Effectively, it runs the test in an Enterprise Level Transaction. That leaves the data stable for each test, and you won't need to mock the db; it can become effectively an acceptance test.

For example (in xUnit):

[Fact]
[AutoRollback]
public void Should_Insert_Valid_Data()
{
    // Arrange: presume there's an object, sut, that supports insert,
    // and you have some data

    // Act:
    int key = sut.Insert(data);

    // Assert:
    Assert.True(key > 0, "Expected a primary key to be returned");
    var inserted = sut.Get(key);
    Assert.Equal<DataClassName>(data, inserted, CompareAllButKey);
}

(fyi: "sut" is "situation under test" or "subject under test")

It might be helpful to see this article on "Strategies for Isolating the Database in Tests". There are some other nice links at the end of that article.

BTW, I used xUnit as a sample, but other testing frameworks have similar attributes for controlling rollbacks.

Upvotes: 0

zbw911
zbw911

Reputation: 343

Like this : 

 // ProjectPath/bin/Debug/dbfile.mdf

 [TestInitialize]
 public void SetUp()
 {           
    AppDomain.CurrentDomain.SetData(
      "DataDirectory", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, ""));
  }

Upvotes: -3

Related Questions