How to check if database creation in LocalDB is finished

Question

I work on a project with uses SQL Server as database together with Entity Framework Core 3.1. For the unit tests / integration tests we use LocalDB.

To ensure the LocalDB database is in the correct migration state, we drop and recreate the test database.

This looks something like this:

using (var sqlConnection = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True"))
{
    sqlConnection.Open();

    new SqlCommand("USE MASTER", sqlConnection).ExecuteNonQuery();

    try
    {
        new SqlCommand($"ALTER DATABASE [MyTestDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", sqlConnection).ExecuteNonQuery();
        new SqlCommand($"DROP DATABASE [MyTestDb]", sqlConnection).ExecuteNonQuery();
    }
    catch (SqlException e)
    {
        // We get an error if there is no database. This happens the very first time a test gets executed on a machine
    }

    new SqlCommand($"CREATE DATABASE [MyTestDb];", sqlConnection).ExecuteNonQuery();
    new SqlCommand($"ALTER DATABASE [MyTestDb] SET ALLOW_SNAPSHOT_ISOLATION ON;", sqlConnection).ExecuteNonQuery();
    new SqlCommand($"ALTER DATABASE [MyTestDb] SET READ_COMMITTED_SNAPSHOT  ON;", sqlConnection).ExecuteNonQuery();
}

using (var creationConnection = new SqlConnection(@$"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True;Initial Catalog=MyTestDb"))
{
    creationConnection.Open(); // <-- Problematic exception here

    using (var context = new MyDbContext(/*some DbContextOptions here... */))
    {
        context.Database.Migrate();
    }
}

This will throw an exception on creationConnection.Open(); with following message:

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot open database "MyTestDb" requested by the login. The login failed.
Login failed for user ''.

If I add a Thread.Sleep(10_000); between the two using blocks, no exception will be thrown.

using (var sqlConnection = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True"))
{
    sqlConnection.Open();

    new SqlCommand("USE MASTER", sqlConnection).ExecuteNonQuery();

    try
    {
        new SqlCommand($"ALTER DATABASE [MyTestDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", sqlConnection).ExecuteNonQuery();
        new SqlCommand($"DROP DATABASE [MyTestDb]", sqlConnection).ExecuteNonQuery();
    }
    catch (SqlException e)
    {
        // We get an error if there is no Database. This happens the very first time a test gets executed on a machine
    }

    new SqlCommand($"CREATE DATABASE [MyTestDb];", sqlConnection).ExecuteNonQuery();
    new SqlCommand($"ALTER DATABASE [MyTestDb] SET ALLOW_SNAPSHOT_ISOLATION ON;", sqlConnection).ExecuteNonQuery();
    new SqlCommand($"ALTER DATABASE [MyTestDb] SET READ_COMMITTED_SNAPSHOT  ON;", sqlConnection).ExecuteNonQuery();
}

Thread.Sleep(10_000); // <-- exception will NOT be thrown 

using (var creationConnection = new SqlConnection(@$"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True;Initial Catalog=MyTestDb"))
{
    creationConnection.Open(); 

    using (var context = new MyDbContext(/*some DbContextOptions here... */))
    {
        context.Database.Migrate();
    }
}

I assume the database MyTestDb is not ready on creationConnection.Open(); and throws an error.

Is there a way to check if the database creation is finished?

Answer 1

You could do something like this:

SELECT [collation_name] FROM sys.databases WHERE name = 'MyTestDb';

If collation_name has a value (is not null), the database is online.