/// <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="collation">The collation name to set during database creation</param>
/// <returns></returns>
public static void MySqlDatabase(
this SupportedDatabasesForEnsureDatabase supported,
string connectionString,
IUpgradeLog logger,
int timeout = -1,
string collation = null)
{
GetMysqlConnectionStringBuilder(connectionString, logger, out var masterConnectionString, out var databaseName);
try
{
using (var connection = new MySqlConnection(masterConnectionString))
{
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 MySqlConnection(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
);
// Create the database...
using (var command = new MySqlCommand(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="T: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>
/// <returns />
public static void MySqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, IUpgradeLog logger, int timeout = -1)
{
if (supported == null)
{
throw new ArgumentNullException(nameof(supported));
}
if (string.IsNullOrEmpty(connectionString) || connectionString.Trim() == string.Empty)
{
throw new ArgumentNullException(nameof(connectionString));
}
if (logger == null)
{
throw new ArgumentNullException(nameof(logger));
}
var connectionStringBuilder = new MySqlConnectionStringBuilder(connectionString);
var initialCatalog = connectionStringBuilder.Database;
if (string.IsNullOrEmpty(initialCatalog) || initialCatalog.Trim() == string.Empty)
{
throw new InvalidOperationException("The connection string does not specify a database name.");
}
connectionStringBuilder.Database = "sys";
var maskedConnectionStringBuilder = new MySqlConnectionStringBuilder(connectionStringBuilder.ConnectionString)
{
Password = string.Empty.PadRight(connectionStringBuilder.Password.Length, '*')
};
logger.WriteInformation("Using connection string {0}", maskedConnectionStringBuilder.ConnectionString);
using (var connection = new MySqlConnection(connectionStringBuilder.ConnectionString))
{
try
{
connection.Open();
}
catch (Exception ex)
{
logger.WriteError("Unable to open database connection to {0}: {1}",
connection.ConnectionString, connection.Database, ex);
throw;
}
using (var mySqlCommand = new MySqlCommand($"CREATE DATABASE IF NOT EXISTS {initialCatalog}", connection)
{
CommandType = CommandType.Text
})
{
if (timeout >= 0)
{
mySqlCommand.CommandTimeout = timeout;
}
mySqlCommand.ExecuteNonQuery();
}
logger.WriteInformation("Ensured database {0} exists", initialCatalog);
}
}
/// <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="collation">The collation name to set during database creation</param>
/// <returns></returns>
public static void SnowflakeDatabase(
this SupportedDatabasesForEnsureDatabase supported,
string connectionString,
IUpgradeLog logger,
int timeout = -1,
string collation = null)
{
using (var connection = new SnowflakeDbConnection())
{
connection.ConnectionString = connectionString;
try
{
connection.Open();
}
catch (SnowflakeDbException)
{
//// Failed to connect to master, lets try direct
//if (DatabaseExistsIfConnectedToDirectly(logger, connectionString, connection.Database))
// return;
throw;
}
var sqlCommandText = $@"create database IF NOT EXISTS ""{connection.Database}"";";
// Create the database...
using (var command = new SnowflakeDbCommand()
{
CommandText = sqlCommandText,
Connection = connection,
CommandType = CommandType.Text
})
{
if (timeout >= 0)
{
command.CommandTimeout = timeout;
}
command.ExecuteNonQuery();
}
logger.WriteInformation(@"Created database {0}", connection.Database);
}
}
/// <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>
/// <returns></returns>
public static void RedshiftDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString)
{
RedshiftDatabase(supported, connectionString, new ConsoleUpgradeLog());
}
/// <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>
/// <returns></returns>
public static void PostgresqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, IUpgradeLog logger)
{
PostgresqlDatabase(supported, connectionString, logger, null);
}
/// <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>
/// <returns></returns>
public static void CockroachDbDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString)
{
CockroachDbDatabase(supported, connectionString, new ConsoleUpgradeLog());
}
/// <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>
/// <returns></returns>
public static void PostgresqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString)
{
PostgresqlDatabase(supported, connectionString, new ConsoleUpgradeLog());
}
/// <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>
/// <returns></returns>
public static void SqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, int commandTimeout)
{
SqlDatabase(supported, connectionString, new ConsoleUpgradeLog(), commandTimeout);
}
/// <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 SqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, AzureDatabaseEdition azureDatabaseEdition, string collation)
{
SqlDatabase(supported, connectionString, new ConsoleUpgradeLog(), azureDatabaseEdition: azureDatabaseEdition, collation: 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="DbUp.Engine.Output.IUpgradeLog"/> used to record actions.</param>
/// <returns></returns>
public static void SqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, IUpgradeLog logger)
{
if (supported == null)
{
throw new ArgumentNullException("supported");
}
if (string.IsNullOrEmpty(connectionString) || connectionString.Trim() == string.Empty)
{
throw new ArgumentNullException("connectionString");
}
if (logger == null)
{
throw new ArgumentNullException("logger");
}
var masterConnectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
var databaseName = masterConnectionStringBuilder.InitialCatalog;
if (string.IsNullOrEmpty(databaseName) || databaseName.Trim() == string.Empty)
{
throw new InvalidOperationException("The connection string does not specify a database name.");
}
masterConnectionStringBuilder.InitialCatalog = "master";
var logMasterConnectionStringBuilder = new SqlConnectionStringBuilder(masterConnectionStringBuilder.ConnectionString)
{
Password = String.Empty.PadRight(masterConnectionStringBuilder.Password.Length, '*')
};
logger.WriteInformation("Master ConnectionString => {0}", logMasterConnectionStringBuilder.ConnectionString);
using (var connection = new SqlConnection(masterConnectionStringBuilder.ConnectionString))
{
connection.Open();
var sqlCommandText = string.Format
(
@"select case when db_id('{0}') is not null then 1 else 0 end;",
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 == 1)
{
return;
}
}
sqlCommandText = string.Format
(
@"create database [{0}];",
databaseName
);
// Create the database...
using (var command = new SqlCommand(sqlCommandText, connection)
{
CommandType = CommandType.Text
})
{
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="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="collation">The collation name to set during database creation</param>
/// <returns></returns>
public static void SnowflakeDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, int commandTimeout, string collation)
{
SnowflakeDatabase(supported, connectionString, new ConsoleUpgradeLog(), commandTimeout, collation: 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="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="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 FlywaySqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, int commandTimeout, AzureDatabaseEdition azureDatabaseEdition, string collation)
{
FlywaySqlDatabase(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="azureDatabaseEdition">Azure edition to Create</param>
/// <returns></returns>
public static void FlywaySqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, AzureDatabaseEdition azureDatabaseEdition)
{
FlywaySqlDatabase(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="collation">The collation name to set during database creation</param>
/// <returns></returns>
public static void MySqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, string collation)
{
MySqlDatabase(supported, connectionString, new ConsoleUpgradeLog(), collation: 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>
/// <returns></returns>
public static void AzureSqlDwDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString)
{
SqlServerExtensions.SqlDatabase(supported, connectionString, new ConsoleUpgradeLog());
}
/// <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="certificateFile">Optional SSL client pfx certificate for db.</param>
/// <param name="logger">The <see cref="DbUp.Engine.Output.IUpgradeLog"/> used to record actions.</param>
/// <returns></returns>
public static void PostgresqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, string certificateFile, IUpgradeLog logger)
{
if (string.IsNullOrEmpty(connectionString) || connectionString.Trim() == string.Empty)
{
throw new ArgumentNullException(nameof(connectionString));
}
if (logger == null)
{
throw new ArgumentNullException(nameof(logger));
}
GetMasterConnectionStringBuilder(connectionString, logger, out var masterConnectionString, out var databaseName);
using (var connection = new NpgsqlConnection(masterConnectionString))
{
if (!string.IsNullOrEmpty(certificateFile) && certificateFile.Trim() != string.Empty)
{
connection.ProvideClientCertificatesCallback += certs => certs.Add(new X509Certificate2(certificateFile));
}
connection.Open();
var sqlCommandText = string.Format
(
@"SELECT case WHEN oid IS NOT NULL THEN 1 ELSE 0 end FROM pg_database WHERE datname = '{0}' limit 1;",
databaseName
);
// check to see if the database already exists..
using (var command = new NpgsqlCommand(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
);
// Create the database...
using (var command = new NpgsqlCommand(sqlCommandText, connection)
{
CommandType = CommandType.Text
})
{
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="certificateFile">Optional SSL client pfx certificate for db.</param>
/// <returns></returns>
public static void PostgresqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, string certificateFile = null)
{
PostgresqlDatabase(supported, connectionString, certificateFile, new ConsoleUpgradeLog());
}
/// <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="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>
/// <returns></returns>
public static void SqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, IUpgradeLog logger, int timeout = -1)
{
if (supported == null)
{
throw new ArgumentNullException("supported");
}
if (string.IsNullOrEmpty(connectionString) || connectionString.Trim() == string.Empty)
{
throw new ArgumentNullException("connectionString");
}
if (logger == null)
{
throw new ArgumentNullException("logger");
}
var masterConnectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
var databaseName = masterConnectionStringBuilder.InitialCatalog;
if (string.IsNullOrEmpty(databaseName) || databaseName.Trim() == string.Empty)
{
throw new InvalidOperationException("The connection string does not specify a database name.");
}
masterConnectionStringBuilder.InitialCatalog = "master";
var logMasterConnectionStringBuilder = new SqlConnectionStringBuilder(masterConnectionStringBuilder.ConnectionString)
{
Password = String.Empty.PadRight(masterConnectionStringBuilder.Password.Length, '*')
};
logger.WriteInformation("Master ConnectionString => {0}", logMasterConnectionStringBuilder.ConnectionString);
using (var connection = new SqlConnection(masterConnectionStringBuilder.ConnectionString))
{
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
);
// 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 using SSL for the connection.
/// </summary>
/// <param name="supported">Fluent helper type.</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="certificate">Certificate for securing connection.</param>
/// <returns></returns>
public static void PostgresqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, X509Certificate2 certificate)
{
PostgresqlDatabase(supported, connectionString, new ConsoleUpgradeLog(), certificate);
}
/// <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>
/// <returns></returns>
public static void PostgresqlDatabase(this SupportedDatabasesForEnsureDatabase supported, string connectionString, IUpgradeLog logger)
{
if (supported == null)
{
throw new ArgumentNullException("supported");
}
if (string.IsNullOrEmpty(connectionString) || connectionString.Trim() == string.Empty)
{
throw new ArgumentNullException("connectionString");
}
if (logger == null)
{
throw new ArgumentNullException("logger");
}
var masterConnectionStringBuilder = new NpgsqlConnectionStringBuilder(connectionString);
var databaseName = masterConnectionStringBuilder.Database;
if (string.IsNullOrEmpty(databaseName) || databaseName.Trim() == string.Empty)
{
throw new InvalidOperationException("The connection string does not specify a database name.");
}
masterConnectionStringBuilder.Database = "postgres";
var logMasterConnectionStringBuilder = new NpgsqlConnectionStringBuilder(masterConnectionStringBuilder.ConnectionString);
if (!string.IsNullOrEmpty(logMasterConnectionStringBuilder.Password))
{
logMasterConnectionStringBuilder.Password = string.Empty.PadRight(masterConnectionStringBuilder.Password.Length, '*');
}
logger.WriteInformation("Master ConnectionString => {0}", logMasterConnectionStringBuilder.ConnectionString);
using (var connection = new NpgsqlConnection(masterConnectionStringBuilder.ConnectionString))
{
connection.Open();
var sqlCommandText = string.Format
(
@"SELECT case WHEN oid IS NOT NULL THEN 1 ELSE 0 end FROM pg_database WHERE datname = '{0}' limit 1;",
databaseName
);
// check to see if the database already exists..
using (var command = new NpgsqlCommand(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
);
// Create the database...
using (var command = new NpgsqlCommand(sqlCommandText, connection)
{
CommandType = CommandType.Text
})
{
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 AzureSqlDatabase(
this SupportedDatabasesForEnsureDatabase supported,
string connectionString,
IUpgradeLog logger,
int timeout = -1,
string collation = null)
{
string databaseName;
string masterConnectionString;
GetMasterConnectionStringBuilder(connectionString, logger, out masterConnectionString, out databaseName);
using (var connection = new SqlConnection(masterConnectionString))
{
if (connection.UseManagedIdentity())
{
logger.WriteInformation("Use ManagedIndentity for the connection: {0}", 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;
}
}
string collationString = string.IsNullOrEmpty(collation) ? "" : string.Format(@" COLLATE {0}", collation);
sqlCommandText = string.Format
(
@"create database [{0}]{1};",
databaseName,
collationString
);
// 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);
}
}
