DatabaseMasterHelper.cs

using Coscine.Configuration;
using System.Data.SqlClient;

namespace Coscine.Database.Helpers
{
    public class DatabaseMasterHelper
    {
        private readonly IConfiguration _configuration;

        public ConnectionSettings ConnectionSettings { get; set; }

        public DatabaseMasterHelper()
        {
            _configuration = new ConsulConfiguration();
        }

        public DatabaseMasterHelper(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public void KillConnectionsToDatabase(string dbDatabase)
        {
            var sqlCommandBuilder = new SqlCommandBuilder();
            using (var connection = new SqlConnection(ConnectionSettings.GetConnectionString(false)))
            {
                connection.Open();
                var command = connection.CreateCommand();

                command.CommandText = $"alter database {sqlCommandBuilder.QuoteIdentifier(dbDatabase)} set single_user with rollback immediate; alter database {sqlCommandBuilder.QuoteIdentifier(dbDatabase)} set MULTI_USER; ";
                command.ExecuteNonQuery();
            }
        }

        public void CreateDatabase(string dbDatabase)
        {
            var sqlCommandBuilder = new SqlCommandBuilder();
            using (var connection = new SqlConnection(ConnectionSettings.GetConnectionString(false)))
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = $"CREATE DATABASE {sqlCommandBuilder.QuoteIdentifier(dbDatabase)};";
                command.ExecuteNonQuery();
            }
        }

        public void DropDatabase(string dbDatabase)
        {
            var sqlCommandBuilder = new SqlCommandBuilder();
            using (var connection = new SqlConnection(ConnectionSettings.GetConnectionString(false)))
            {
                connection.Open();
                var command = connection.CreateCommand();
                command.CommandText = $"DROP DATABASE {sqlCommandBuilder.QuoteIdentifier(dbDatabase)};";
                command.ExecuteNonQuery();
            }
        }

        public bool DatabaseExists(string dbDatabase)
        {
            var sqlCommandBuilder = new SqlCommandBuilder();
            using (var connection = new SqlConnection(ConnectionSettings.GetConnectionString(false)))
            {
                connection.Open();
                var command = connection.CreateCommand();
                var dbName = sqlCommandBuilder.QuoteIdentifier(dbDatabase);
                dbName = dbName.StartsWith("[") ? dbName.Substring(1) : dbName;
                dbName = dbName.EndsWith("]") ? dbName.Remove(dbName.Length - 1) : dbName;
                command.CommandText = $"SELECT DB_ID('{dbName}') AS RESULT;"; ;
                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        var result = reader["RESULT"].ToString();
                        return !string.IsNullOrWhiteSpace(result);
                    }
                }
                return false;
            }
        }

        public void EnsureDatabase(string dbDatabase)
        {
            if (!DatabaseExists(dbDatabase))
            {
                CreateDatabase(dbDatabase);
            }
        }

        public string GetConsulDatabaseName()
        {
            return _configuration.GetString(ConfigurationConnectionSettings.DatabaseKey);
        }
    }
}