/// <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); } }