/// <summary>
/// Creates a throwaway database using the connection string provided. No need to set the Initial Catalog as it will get replaced by the name of the database that will be created.
/// </summary>
public static ThrowawayDatabase Create(string connectionString, ThrowawayDatabaseOptions options)
{
if (!TryPingDatabase(connectionString))
{
throw new Exception("Could not connect to the database");
}
var database = new ThrowawayDatabase(connectionString ?? string.Empty, options);
if (!database.CreateDatabaseIfDoesNotExist(options))
{
throw new Exception("Could not create the throwaway database");
}
return(database);
}
private bool CreateDatabaseIfDoesNotExist(ThrowawayDatabaseOptions options)
{
try
{
var builder = new SqlConnectionStringBuilder(ConnectionString);
if (!builder.TryGetValue("Initial Catalog", out var database))
{
return(false);
}
var databaseName = database.ToString();
var connectionStringOfMaster = ConnectionString.Replace(databaseName, "master");
using (var connection = new SqlConnection(connectionStringOfMaster))
{
connection.Open();
var cmdText = "SELECT NAME from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');";
using (var cmd = new SqlCommand(cmdText, connection))
{
using (var reader = cmd.ExecuteReader())
{
var databaseExists = false;
while (reader.Read())
{
if (reader.GetString(0) == databaseName)
{
databaseExists = true;
_databaseCreated = true;
break;
}
}
if (!databaseExists)
{
using (var otherConnection = new SqlConnection(connectionStringOfMaster))
{
otherConnection.Open();
cmdText = $"CREATE DATABASE {databaseName}";
if (!string.IsNullOrWhiteSpace(options.Collation))
{
cmdText += $" COLLATE {options.Collation}";
}
using (var createCmd = new SqlCommand(cmdText, otherConnection))
{
createCmd.ExecuteNonQuery();
Debug.Print($"Successfully created database {databaseName}");
_databaseCreated = true;
}
}
}
else
{
_databaseCreated = true;
}
}
}
}
return(true);
}
catch
{
return(false);
}
}
private ThrowawayDatabase(string originalConnectionString, ThrowawayDatabaseOptions options)
{
// Default constructor is private
_originalConnectionString = originalConnectionString;
(ConnectionString, Name) = DeriveThrowawayConnectionString(originalConnectionString, options);
}
/// <summary>
/// Creates a database through SQL server authentication using the given username, password and the datasource/instance.
/// </summary>
public static ThrowawayDatabase Create(string username, string password, string dataSource, ThrowawayDatabaseOptions options)
{
var connectionString = $"Password={password};Persist Security Info=True;User ID={username};Initial Catalog=master;Data Source={dataSource}";
return(Create(connectionString, options));
}
/// <summary>
/// Uses the given instance as the Data Source of the connection string along with integration security assuming that the current user has direct access to his or her Sql server instance.
/// </summary>
public static ThrowawayDatabase FromLocalInstance(string instance, ThrowawayDatabaseOptions options)
{
var connectionString = $"Data Source={instance};Initial Catalog=master;Integrated Security=True;";
return(Create(connectionString, options));
}
private static (string connectionString, string databaseName) DeriveThrowawayConnectionString(string originalConnectionString, ThrowawayDatabaseOptions options)
{
var builder = new SqlConnectionStringBuilder(originalConnectionString);
var databasePrefix = string.IsNullOrWhiteSpace(options.DatabaseNamePrefix) ? DefaultDatabaseNamePrefix : options.DatabaseNamePrefix;
var databaseName = $"{databasePrefix}{Guid.NewGuid().ToString("n").Substring(0, 10)}";
if (builder.TryGetValue("Initial Catalog", out _))
{
builder.Remove("Initial Catalog");
}
builder.InitialCatalog = databaseName;
return(builder.ConnectionString, databaseName);
}