Is it possible to connect to SQL Server without specifying a database?

Question

I'm trying to write some unit tests for my code that connect to SQL Server for persistence, but I would like to be able to run my unit tests by just pointing it at a SQL Server instance, and let the tests create their own database to run tests in, so after each test it can just drop the database and then on setup before the next test recreate it, so I know there is no legacy data or structures left over from a previous test effecting the next test.

Answer 1

In brief: no, you cannot do that. You might be able to leave out the database from the connection string, but in that case, that connection will be made to the configured default database of the login that's connecting to SQL Server (and that default database must exist at the time the connection is made)

If you want to have this scenario, you need to

1. first connect to your instance and database master and create your new testdb (or whatever it's called)

2. disconnect

3. in your tests, connect to the instance and the testdb database

Better yet: use a mocking framework of some sort so you don't even need an actual database in your testing scenario!

Answer 2

I use the following class to facilitate the OP's scenario:

public class MsSqlDatabaseCreator
{
    public void Create(string connectionstring)
    {
        if (DatabaseExists(connectionstring))
        {
            DropDatabase(connectionstring);
        }
        CreateDatabase(connectionstring);
    }

    private static void CreateDatabase(string connectionString)
    {
        var sqlConnectionStringBuilder = new SqlConnectionStringBuilder(connectionString);

        var databaseName = sqlConnectionStringBuilder.InitialCatalog;

        sqlConnectionStringBuilder.InitialCatalog = "master";

        using (var sqlConnection = new SqlConnection(sqlConnectionStringBuilder.ConnectionString))
        {
            sqlConnection.Open();

            using (var sqlCommand = sqlConnection.CreateCommand())
            {
                sqlCommand.CommandText = $"CREATE DATABASE {databaseName}";
                sqlCommand.ExecuteNonQuery();
            }
        }
    }

    private static bool DatabaseExists(string connectionString)
    {
        var sqlConnectionStringBuilder = new SqlConnectionStringBuilder(connectionString);

        var databaseName = sqlConnectionStringBuilder.InitialCatalog;

        sqlConnectionStringBuilder.InitialCatalog = "master";

        using (var sqlConnection = new SqlConnection(sqlConnectionStringBuilder.ConnectionString))
        {
            sqlConnection.Open();

            using (var command = sqlConnection.CreateCommand())
            {
                command.CommandText = $"SELECT db_id('{databaseName}')";

                return command.ExecuteScalar() != DBNull.Value;
            }
        }
    }

    private static void DropDatabase(string connectionString)
    {
        var sqlConnectionStringBuilder = new SqlConnectionStringBuilder(connectionString);

        var databaseName = sqlConnectionStringBuilder.InitialCatalog;

        sqlConnectionStringBuilder.InitialCatalog = "master";

        using (var sqlConnection = new SqlConnection(sqlConnectionStringBuilder.ConnectionString))
        {
            sqlConnection.Open();

            using (var sqlCommand = sqlConnection.CreateCommand())
            {
                sqlCommand.CommandText = $@"
                    ALTER DATABASE {databaseName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                    DROP DATABASE [{databaseName}]
                ";
                sqlCommand.ExecuteNonQuery();
            }
        }
    }
}

The important part is the switching of the database name (initial catalog) to master. This way you can have just one connectionstring.

Answer 3

What you want to accomplish is possible using a mocking framework, in which case you don't even have to "connect to a database", you simply mock the return values that the database should return in order for you to test your "db handler" implementation.

There are several to choose from when it comes to C#, I can recommend Rhino Mocks and Moq to name two. Here's a question detailing a bit more; https://stackoverflow.com/questions/37359/what-c-sharp-mocking-framework-to-use

Answer 4

Why not have the same named database dedicated for tests? and drop-create it every time. This way you won't need to mess about with connection strings - it is always the same.

And yet, there is a better solution: within all your tests, start transaction, do your test, where your data is messed up. Once you verified (or failed) the test, unroll the transaction. This way you don't need to drop-create your tests for every test, because the data is never changed.

But you'll need to make sure schema in test-database is always up to date. So you'll need to drop-create test database whenever your schema is changed.

I've blogged about database tests and how we deal with Entity Framework migrations. This might not be completely applicable to your situation, but might help with ideas.

Regarding using mocks in your tests - yes this is absolutely valid suggestion and should be followed most of the time. Unless you are trying to test the database layer. In that case no mocks will save you, and you just have to go to DB. Many times over I have tried to mock DbContext in EF, but never managed to simulate realistic DB behavior. So going to DB was easier for me, rather than simulating DB-mock.

Answer 5

I'd use SQL Server Management Objects for the task. It's Server and Database APIs doesn't necessarily need a connection string but I think you might still need to specify a database. You can use master for that. (Check jeroenh's answer about creating object using SMO API as well)

By the way, if you are using .Net 4.0.2 and up you can use LocalDB as well, which is even better.

Edit: Note that actually LocalDB is an SQL Server 2012 feature however you still need .Net Framework > 4.0.2 to be able to use it.