internal static SqlConnection OpenSqlConnection(ICakeContext context, string connectionString)
{
Initializer.InitializeNativeSearchPath();
try
{
var connection = new SqlConnection(connectionString);
context.Log.Debug($"About to open connection with this connection string: {connectionString}");
connection.Open();
return(connection);
}
catch (SqlException exception)
{
if (exception.Message.StartsWith("A network-related or instance-specific error", StringComparison.InvariantCultureIgnoreCase) &&
(connectionString.ToLower(CultureInfo.InvariantCulture).Contains("localdb", StringComparison.OrdinalIgnoreCase) ||
connectionString.ToLower(CultureInfo.InvariantCulture).Contains('\v', StringComparison.OrdinalIgnoreCase)))
{
const string errorMessage = "Looks like you are trying to connect to LocalDb. Have you correctly escaped your connection string with '@'? It should look like 'var connString = @\"(localDb)\\v12.0\"'";
context.Log.Error(errorMessage);
var newException = new Exception(errorMessage, exception);
throw newException;
}
throw;
}
}
internal static void ExecuteSqlCommand(ICakeContext context, SqlConnection connection, string sqlCommands)
{
Initializer.InitializeNativeSearchPath();
var commandStrings = Regex.Split(
sqlCommands,
@"^\s*GO\s*$",
RegexOptions.Multiline | RegexOptions.IgnoreCase,
TimeSpan.FromSeconds(5));
foreach (var sqlCommand in commandStrings)
{
if (sqlCommand.Trim() != string.Empty)
{
context.Log.Debug($"Executing SQL : {sqlCommand}");
try
{
using (var command = CreateSqlCommand(sqlCommand, connection))
{
command.ExecuteNonQuery();
}
}
catch (Exception)
{
context.Log.Warning($"Exception happened while executing this command: {sqlCommand}");
throw;
}
}
}
}
internal static void PublishDacpacFile(
ICakeContext context,
string connectionString,
string targetDatabaseName,
string?dacpacFilePath,
PublishDacpacSettings?settings = null)
{
if (string.IsNullOrEmpty(dacpacFilePath))
{
throw new ArgumentNullException(nameof(dacpacFilePath));
}
Initializer.InitializeNativeSearchPath();
context.Log.Information($"About to publish dacpac from {dacpacFilePath} into database {targetDatabaseName}");
using (var dacPackage = DacPackage.Load(dacpacFilePath))
{
context.Log.Debug($"Loaded dacpac file {dacpacFilePath}");
var service = new DacServices(connectionString);
var options = GetPublishOptions(settings);
service.Publish(dacPackage, targetDatabaseName, options);
}
context.Log.Information($"Finished restoring dacpac file into database {targetDatabaseName}");
}
internal static void Execute(ICakeContext context, string connectionString, string databaseName, BackupDatabaseSettings settings)
{
Initializer.InitializeNativeSearchPath();
Guard.ArgumentIsNotNull(context, nameof(context));
Guard.ArgumentIsNotNull(connectionString, nameof(connectionString));
Guard.ArgumentIsNotNull(settings, nameof(settings));
var backupFile = GetBackupFileName(databaseName, settings.Path);
var compress = settings.Compress ? ", COMPRESSION" : string.Empty;
using (var connection = SqlServerAliasesImpl.OpenSqlConnection(context, connectionString))
{
context.Log.Information($"Backing up database '{databaseName}' to {backupFile}");
var sql = $@"
BACKUP DATABASE {Sql.EscapeName(databaseName)}
TO DISK = @backupFile
WITH FORMAT, INIT, COPY_ONLY, NAME = '{databaseName} Full Backup',
SKIP, REWIND, NOUNLOAD, STATS = 10 {compress}";
context.Log.Information(sql);
using (var command = SqlServerAliasesImpl.CreateSqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@backupFile", backupFile);
command.ExecuteNonQuery();
}
}
}
internal static void ExecuteSqlFile(ICakeContext context, string connectionString, FilePath sqlFile)
{
Initializer.InitializeNativeSearchPath();
using (var connection = OpenSqlConnection(context, connectionString))
{
ExecuteSqlFile(context, connection, sqlFile);
}
}
private static void ExecuteRunner(this ICakeContext context, LocalDbSettings settings)
{
Initializer.InitializeNativeSearchPath();
context.Log.Information(Verbosity.Normal, "Executing SQLLocalDB.exe with action {0} on instance {1}", settings.Action, settings.InstanceName);
var localDbRunner = new LocalDbToolRunner(context.FileSystem, context.Environment, context.ProcessRunner, context.Tools, context.Log);
localDbRunner.Run(settings);
}
public static void CreateBacpacFile(ICakeContext context, string connectionString, string databaseName, string resultingFilePath)
{
Initializer.InitializeNativeSearchPath();
context.Log.Information($"About to create a bacpac file from database {databaseName}");
var service = new DacServices(connectionString);
service.ExportBacpac(resultingFilePath, databaseName);
context.Log.Information($"Finished creating bacpac file from database {databaseName}. File location is {resultingFilePath}");
}
internal static void ExecuteSqlFile(ICakeContext context, SqlConnection connection, FilePath sqlFile)
{
Initializer.InitializeNativeSearchPath();
var sqlFilePath = sqlFile.FullPath;
context.Log.Information($"Executing sql file {sqlFilePath}");
var allSqlCommands = File.ReadAllText(sqlFilePath);
context.ExecuteSqlCommand(connection, allSqlCommands);
context.Log.Information($"Finished executing SQL from {sqlFilePath}");
}
internal static bool DatabaseExists(ICakeContext context, string connectionString, string databaseName)
{
Initializer.InitializeNativeSearchPath();
const string databaseExistsSql = "select DB_ID(@DatabaseName) as Id";
using (var connection = OpenSqlConnection(context, connectionString))
{
context.Log.Debug($"Executing SQL : {databaseExistsSql}");
using (var command = CreateSqlCommand(databaseExistsSql, connection))
{
command.Parameters.AddWithValue("@DatabaseName", databaseName);
return(command.ExecuteScalar() != DBNull.Value);
}
}
}
internal static void ExtractDacpacFile(
ICakeContext context,
string connectionString,
string targetDatabaseName,
ExtractDacpacSettings settings)
{
Initializer.InitializeNativeSearchPath();
context.Log.Information($"About to extract a dacpac file from database {targetDatabaseName}");
var service = new DacServices(connectionString);
service.Extract(settings.OutputFile.FullPath, targetDatabaseName, settings.Name, settings.Version, settings.Description, settings.Tables);
context.Log.Information($"Finished creating dacpac file from database {targetDatabaseName}. File location is {settings.OutputFile}");
}
internal static void CreateDatabase(ICakeContext context, string connectionString, string databaseName)
{
Initializer.InitializeNativeSearchPath();
var createDbSql = $"create database {Sql.EscapeName(databaseName)}";
using (var connection = OpenSqlConnection(context, connectionString))
{
context.Log.Debug($"Executing SQL : {createDbSql}");
using (var command = CreateSqlCommand(createDbSql, connection))
{
command.ExecuteNonQuery();
}
context.Log.Information($"Database {databaseName} is created");
}
}
internal static void CreateDatabase(ICakeContext context, string connectionString, string databaseName, CreateDatabaseSettings settings)
{
Initializer.InitializeNativeSearchPath();
settings.AssignNames(databaseName);
var sql = GenerateCreateDbSql(databaseName, settings);
using (var connection = OpenSqlConnection(context, connectionString))
{
context.Log.Debug($"Executing SQL : {sql}");
using (var command = CreateSqlCommand(sql, connection))
{
command.ExecuteNonQuery();
}
context.Log.Information($"Database {databaseName} is created if it was not there");
}
}
internal static void CreateDatabaseIfNotExists(ICakeContext context, string connectionString, string databaseName)
{
Initializer.InitializeNativeSearchPath();
var createDbSql = $"if (select DB_ID(@DatabaseName)) is null create database {Sql.EscapeName(databaseName)}";
using (var connection = OpenSqlConnection(context, connectionString))
{
context.Log.Debug($"Executing SQL : {createDbSql}");
using (var command = CreateSqlCommand(createDbSql, connection))
{
command.Parameters.AddWithValue("@DatabaseName", databaseName);
command.ExecuteNonQuery();
}
context.Log.Information($"Database {databaseName} is created if it was not there");
}
}
public static void RestoreBacpac(ICakeContext context, string connectionString, string newDatabaseName, string?bacpacFilePath)
{
if (string.IsNullOrEmpty(bacpacFilePath))
{
throw new ArgumentNullException(nameof(bacpacFilePath));
}
Initializer.InitializeNativeSearchPath();
context.Log.Information($"About to restore bacpac from {bacpacFilePath} into database {newDatabaseName}");
using (var bacPackage = BacPackage.Load(bacpacFilePath))
{
context.Log.Debug($"Loaded bacpac file {bacpacFilePath}");
var service = new DacServices(connectionString);
service.ImportBacpac(bacPackage, newDatabaseName);
}
context.Log.Information($"Finished restoring bacpac file into database {newDatabaseName}");
}
internal static void CreateDatabaseIfNotExists(ICakeContext context, string connectionString, string databaseName, CreateDatabaseSettings settings)
{
Initializer.InitializeNativeSearchPath();
settings.AssignNames(databaseName);
var sql = GenerateCreateDbSql(databaseName, settings);
sql = "if (select DB_ID(@DatabaseName)) is null " + sql;
using (var connection = OpenSqlConnection(context, connectionString))
{
context.Log.Debug($"Executing SQL : {sql}");
using (var command = CreateSqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@DatabaseName", databaseName);
command.ExecuteNonQuery();
}
context.Log.Information($"Database {databaseName} is created if it was not there");
}
}
internal static void DropDatabase(ICakeContext context, string connectionString, string databaseName)
{
Initializer.InitializeNativeSearchPath();
var dropDatabaseSql =
$@"if (select DB_ID(@DatabaseName)) is not null
begin
alter database {Sql.EscapeName(databaseName)} set offline with rollback immediate;
alter database {Sql.EscapeName(databaseName)} set online;
drop database {Sql.EscapeName(databaseName)};
end";
try
{
using (var connection = OpenSqlConnection(context, connectionString))
{
using (var command = CreateSqlCommand(dropDatabaseSql, connection))
{
command.Parameters.AddWithValue("@DatabaseName", databaseName);
context.Log.Information($"About to drop database {databaseName}");
command.ExecuteNonQuery();
}
context.Log.Information($"Database {databaseName} is dropped");
}
}
catch (SqlException sqlException)
{
if (sqlException.Message.StartsWith("Cannot open database", StringComparison.OrdinalIgnoreCase))
{
context.Log.Error($"Database {databaseName} does not exits");
return;
}
throw;
}
}
internal static void DropAndCreateDatabase(ICakeContext context, string connectionString, string databaseName, CreateDatabaseSettings settings)
{
Initializer.InitializeNativeSearchPath();
DropDatabase(context, connectionString, databaseName);
CreateDatabase(context, connectionString, databaseName, settings);
}
// if database name is not provided, dbname from the backup is used.
// if newStoragePath is not provided, system defaults are used
internal static void RestoreSqlBackup(
ICakeContext context,
string connectionString,
RestoreSqlBackupSettings settings,
IList <FilePath> backupFiles,
IList <FilePath>?differentialBackupFiles = null)
{
Initializer.InitializeNativeSearchPath();
using (var connection = SqlServerAliasesImpl.OpenSqlConnection(context, connectionString))
{
var firstBackupFile = backupFiles.First();
var oldDbName = GetDatabaseName(firstBackupFile, connection);
var databaseName = settings.NewDatabaseName ?? oldDbName;
if (settings.SwitchToUserMode != DbUserMode.MultiUser)
{
using (var singleModeCommand = GetSetDatabaseUserModeCommand(
context,
connection,
databaseName,
settings.SwitchToUserMode))
{
singleModeCommand.ExecuteNonQuery();
}
}
var hasDifferentialBackup = differentialBackupFiles?.Count > 0;
using (var fullRestoreCommand = GetRestoreSqlBackupCommand(
context,
connection,
settings.BackupSetFile,
settings.WithReplace,
hasDifferentialBackup,
databaseName,
settings.NewStorageFolder ?? Environment.GetFolderPath(Environment.SpecialFolder.UserProfile),
backupFiles.ToArray()))
{
fullRestoreCommand.ExecuteNonQuery();
}
if (hasDifferentialBackup)
{
using (var differentialRestoreCommand = GetRestoreSqlBackupCommand(
context,
connection,
settings.DifferentialBackupSetFile,
false,
false,
databaseName,
settings.NewStorageFolder ?? Environment.GetFolderPath(Environment.SpecialFolder.UserProfile),
differentialBackupFiles?.ToArray() ?? Array.Empty <FilePath>()))
{
differentialRestoreCommand.ExecuteNonQuery();
}
}
if (settings.SwitchToUserMode != DbUserMode.MultiUser)
{
using (var singleModeCommand =
GetSetDatabaseUserModeCommand(context, connection, databaseName, DbUserMode.MultiUser))
{
singleModeCommand.ExecuteNonQuery();
}
}
}
}
