/// <summary>
/// Ensures that the database specified in the connection string exists.
/// </summary>
/// <param name="supported">Fluent helper type.</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="commandTimeout">Use this to set the command time out for creating a database in case you're encountering a time out in this operation.</param>
/// <param name="azureDatabaseEdition">Azure edition to Create</param>
/// <param name="collation">The collation name to set during database creation</param>
/// <returns></returns>
public static void SqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, int commandTimeout, AzureDatabaseEdition azureDatabaseEdition, string collation)
{
SqlDatabase(supported, connectionString, new ConsoleUpgradeLog(), commandTimeout, azureDatabaseEdition, collation);
}
/// <summary>
/// Ensures that the database specified in the connection string exists.
/// </summary>
/// <param name="supported">Fluent helper type.</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="logger">The <see cref="IUpgradeLog"/> used to record actions.</param>
/// <param name="timeout">Use this to set the command time out for creating a database in case you're encountering a time out in this operation.</param>
/// <param name="azureDatabaseEdition">Use to indicate that the SQL server database is in Azure</param>
/// <param name="collation">The collation name to set during database creation</param>
/// <returns></returns>
public static void SqlDatabase(
this SupportedDatabasesForEnsureDatabase supported,
string connectionString,
IUpgradeLog logger,
int timeout = -1,
AzureDatabaseEdition azureDatabaseEdition = AzureDatabaseEdition.None,
string collation = null)
{
GetMasterConnectionStringBuilder(connectionString, logger, out var masterConnectionString, out var databaseName);
using (var connection = new SqlConnection(masterConnectionString))
{
try
{
connection.Open();
}
catch (SqlException)
{
// Failed to connect to master, lets try direct
if (DatabaseExistsIfConnectedToDirectly(logger, connectionString, databaseName))
{
return;
}
throw;
}
if (DatabaseExists(connection, databaseName))
{
return;
}
var collationString = string.IsNullOrEmpty(collation) ? "" : $@" COLLATE {collation}";
var sqlCommandText = $@"create database [{databaseName}]{collationString}";
switch (azureDatabaseEdition)
{
case AzureDatabaseEdition.None:
sqlCommandText += ";";
break;
case AzureDatabaseEdition.Basic:
sqlCommandText += " ( EDITION = ''basic'' );";
break;
case AzureDatabaseEdition.Standard:
sqlCommandText += " ( EDITION = ''standard'' );";
break;
case AzureDatabaseEdition.Premium:
sqlCommandText += " ( EDITION = ''premium'' );";
break;
}
// Create the database...
using (var command = new SqlCommand(sqlCommandText, connection)
{
CommandType = CommandType.Text
})
{
if (timeout >= 0)
{
command.CommandTimeout = timeout;
}
command.ExecuteNonQuery();
}
logger.WriteInformation(@"Created database {0}", databaseName);
}
}
/// <summary>
/// Ensures that the database specified in the connection string exists.
/// </summary>
/// <param name="supported">Fluent helper type.</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="azureDatabaseEdition">Azure edition to Create</param>
/// <returns></returns>
public static void SqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, AzureDatabaseEdition azureDatabaseEdition)
{
SqlDatabase(supported, connectionString, new ConsoleUpgradeLog(), -1, azureDatabaseEdition);
}
/// <summary>
/// Ensures that the database specified in the connection string exists.
/// </summary>
/// <param name="supported">Fluent helper type.</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="logger">The <see cref="DbUp.Engine.Output.IUpgradeLog"/> used to record actions.</param>
/// <param name="timeout">Use this to set the command time out for creating a database in case you're encountering a time out in this operation.</param>
/// <param name="azureDatabaseEdition">Use to indicate that the SQL server database is in Azure</param>
/// <param name="collation">The collation name to set during database creation</param>
/// <returns></returns>
public static void SqlDatabase(
this SupportedDatabasesForEnsureDatabase supported,
string connectionString,
IUpgradeLog logger,
int timeout = -1,
AzureDatabaseEdition azureDatabaseEdition = AzureDatabaseEdition.None,
string collation = null)
{
GetMasterConnectionStringBuilder(connectionString, logger, out var masterConnectionString, out var databaseName);
try
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
if (DatabaseExists(connection, databaseName))
{
return;
}
}
}
catch (Exception e)
{
logger.WriteInformation(@"Database not found on server with connection string in settings: {0}", e.Message);
}
using (var connection = new SqlConnection(masterConnectionString))
{
connection.Open();
if (DatabaseExists(connection, databaseName))
{
return;
}
var collationString = string.IsNullOrEmpty(collation) ? "" : string.Format(@" COLLATE {0}", collation);
var sqlCommandText = string.Format
(
@"create database [{0}]{1}",
databaseName,
collationString
);
switch (azureDatabaseEdition)
{
case AzureDatabaseEdition.None:
sqlCommandText += ";";
break;
case AzureDatabaseEdition.Basic:
sqlCommandText += " ( EDITION = ''basic'' );";
break;
case AzureDatabaseEdition.Standard:
sqlCommandText += " ( EDITION = ''standard'' );";
break;
case AzureDatabaseEdition.Premium:
sqlCommandText += " ( EDITION = ''premium'' );";
break;
}
// Create the database...
using (var command = new SqlCommand(sqlCommandText, connection)
{
CommandType = CommandType.Text
})
{
if (timeout >= 0)
{
command.CommandTimeout = timeout;
}
command.ExecuteNonQuery();
}
logger.WriteInformation(@"Created database {0}", databaseName);
}
}
/// <summary>
/// Ensures that the database specified in the connection string exists.
/// </summary>
/// <param name="supported">Fluent helper type.</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="logger">The <see cref="DbUp.Engine.Output.IUpgradeLog"/> used to record actions.</param>
/// <param name="timeout">Use this to set the command time out for creating a database in case you're encountering a time out in this operation.</param>
/// <param name="azureDatabaseEdition">Use to indicate that the SQL server database is in Azure</param>
/// <returns></returns>
public static void SqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, IUpgradeLog logger, int timeout = -1, AzureDatabaseEdition azureDatabaseEdition = AzureDatabaseEdition.None)
{
string databaseName;
string masterConnectionString;
GetMasterConnectionStringBuilder(connectionString, logger, out masterConnectionString, out databaseName);
using (var connection = new SqlConnection(masterConnectionString))
{
connection.Open();
var sqlCommandText = string.Format
(
@"SELECT TOP 1 case WHEN dbid IS NOT NULL THEN 1 ELSE 0 end FROM sys.sysdatabases WHERE name = '{0}';",
databaseName
);
// check to see if the database already exists..
using (var command = new SqlCommand(sqlCommandText, connection)
{
CommandType = CommandType.Text
})
{
var results = (int?)command.ExecuteScalar();
// if the database exists, we're done here...
if (results.HasValue && results.Value == 1)
{
return;
}
}
sqlCommandText = string.Format
(
@"create database [{0}];",
databaseName
);
switch (azureDatabaseEdition)
{
case AzureDatabaseEdition.Basic:
sqlCommandText += " ( EDITION = ''basic'' );";
break;
case AzureDatabaseEdition.Standard:
sqlCommandText += " ( EDITION = ''standard'' );";
break;
case AzureDatabaseEdition.Premium:
sqlCommandText += " ( EDITION = ''premium'' );";
break;
}
// Create the database...
using (var command = new SqlCommand(sqlCommandText, connection)
{
CommandType = CommandType.Text
})
{
if (timeout >= 0)
{
command.CommandTimeout = timeout;
}
command.ExecuteNonQuery();
}
logger.WriteInformation(@"Created database {0}", databaseName);
}
}
/// <summary>
/// Ensures that the database specified in the connection string exists.
/// </summary>
/// <param name="supported">Fluent helper type.</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="logger">The <see cref="DbUp.Engine.Output.IUpgradeLog"/> used to record actions.</param>
/// <param name="timeout">Use this to set the command time out for creating a database in case you're encountering a time out in this operation.</param>
/// <param name="azureDatabaseEdition">Use to indicate that the SQL server database is in Azure</param>
/// <param name="collation">The collation name to set during database creation</param>
/// <returns></returns>
public static void FlywaySqlDatabase(
this SupportedDatabasesForEnsureDatabase supported,
string connectionString,
IUpgradeLog logger,
int timeout = -1,
AzureDatabaseEdition azureDatabaseEdition = AzureDatabaseEdition.None,
string collation = null)
{
string databaseName;
string masterConnectionString;
GetMasterConnectionStringBuilder(connectionString, logger, out masterConnectionString, out databaseName);
using (var connection = new SqlConnection(masterConnectionString))
{
/*
* if (!string.IsNullOrWhiteSpace(_provider.Conf.Url))
* {
* var connstring = _provider.Conf.GetConnectionString();
* var sqlbuilder = new SqlConnectionStringBuilder(connstring);
* sqlbuilder.InitialCatalog = "master";
*
* var cm = new global::DbUp.SqlServer.SqlConnectionManager(sqlbuilder.ConnectionString);
* var exec = new DbUpSqlScriptExecution(cm, _provider.Conf);
* exec.Run(_callbacks.createDatabase, journal:NullJournal);
* }
*/
connection.Open();
var sqlCommandText = string.Format
(
@"SELECT TOP 1 case WHEN dbid IS NOT NULL THEN 1 ELSE 0 end FROM sys.sysdatabases WHERE name = '{0}';",
databaseName
);
// check to see if the database already exists..
using (var command = new SqlCommand(sqlCommandText, connection)
{
CommandType = CommandType.Text
})
{
var results = (int?)command.ExecuteScalar();
// if the database exists, we're done here...
if (results.HasValue && results.Value == 1)
{
return;
}
}
string collationString = string.IsNullOrEmpty(collation) ? "" : string.Format(@" COLLATE {0}", collation);
sqlCommandText = string.Format
(
@"create database [{0}]{1};",
databaseName,
collationString
);
switch (azureDatabaseEdition)
{
case AzureDatabaseEdition.Basic:
sqlCommandText += " ( EDITION = ''basic'' );";
break;
case AzureDatabaseEdition.Standard:
sqlCommandText += " ( EDITION = ''standard'' );";
break;
case AzureDatabaseEdition.Premium:
sqlCommandText += " ( EDITION = ''premium'' );";
break;
}
// Create the database...
using (var command = new SqlCommand(sqlCommandText, connection)
{
CommandType = CommandType.Text
})
{
if (timeout >= 0)
{
command.CommandTimeout = timeout;
}
command.ExecuteNonQuery();
}
logger.WriteInformation(@"Created database {0}", databaseName);
}
}
/// <summary>
/// Ensures that the database specified in the connection string exists.
/// </summary>
/// <param name="supported">Fluent helper type.</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="azureDatabaseEdition">Azure edition to Create</param>
/// <param name="collation">The collation name to set during database creation</param>
/// <returns></returns>
public static void FlywaySqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, AzureDatabaseEdition azureDatabaseEdition, string collation)
{
FlywaySqlDatabase(supported, connectionString, new ConsoleUpgradeLog(), azureDatabaseEdition: azureDatabaseEdition, collation: collation);
}
