public static void ApplyDacpac(Stream dacpac, string connectionString, string databaseName, ILogger log)
{
var options = new DacDeployOptions()
{
BlockOnPossibleDataLoss = true,
IncludeTransactionalScripts = true,
DropConstraintsNotInSource = false,
DropIndexesNotInSource = false,
DropDmlTriggersNotInSource = false,
DropObjectsNotInSource = false,
DropExtendedPropertiesNotInSource = false,
DropPermissionsNotInSource = false,
DropStatisticsNotInSource = false,
DropRoleMembersNotInSource = false,
};
var service = new DacServices(connectionString);
service.Message += (x, y) =>
{
log.Log(y.Message.Message);
};
try
{
using (var package = DacPackage.Load(dacpac))
{
service.Deploy(package, databaseName, true, options);
}
}
catch (Exception e)
{
log.Log(e.Message, true);
}
}
public static void Deploy(string connectionString, string dacpacFileName, string databaseName)
{
try
{
var dacServices = new DacServices(connectionString);
dacServices.Message += DacServices_Message;
dacServices.ProgressChanged += DacServices_ProgressChanged;
var options = new DacDeployOptions
{
CreateNewDatabase = true,
BlockOnPossibleDataLoss = true,
GenerateSmartDefaults = true,
VerifyDeployment = true,
};
//TODO na pszyłość można to wykorzystać
//dacServices.GenerateDeployReport(DacPackage.Load(DacpacFileName), DatabaseName, options);
var dacpackPackage = DacPackage.Load(dacpacFileName);
dacServices.Deploy(dacpackPackage, databaseName, true, options);
GlobalApplicationSettings.IsDbExists = true;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Publish dacpac to database
/// </summary>
/// <param name="connectionString">connection string</param>
/// <param name="targetDatbase">taget database name</param>
/// <param name="dacpacfile">dacpac file</param>
public void Publish(string connectionString, string targetDatbase, string dacpacfile)
{
var dac = new DacServices(connectionString);
var dacpac = DacPackage.Load(dacpacfile);
dac.Deploy(dacpac, targetDatbase, true);
}
private static void DeployByDacpac(string databaseName)
{
string DatabaseConnectionString = ConfigurationManager.ConnectionStrings[databaseName].ConnectionString;
string DatabaseName = databaseName;
var instance = new DacServices(DatabaseConnectionString);
var path = System.IO.Path.GetFullPath(@"..\..\..\MGPRM\bin\Debug\MGPRM.dacpac");
bool success = true;
var dacOptions = new DacDeployOptions();
dacOptions.BlockOnPossibleDataLoss = false;
var dacServiceInstance = new DacServices(DatabaseConnectionString);
//If the DB has database reference then DACPAC technology not allow to deploy with SQL Variables
//then temporary you should not use it
try
{
using (DacPackage dacpac = DacPackage.Load(path))
{
dacServiceInstance.Deploy(dacpac, DatabaseName,
upgradeExisting: true,
options: dacOptions);
}
}
catch (Exception ex)
{
success = false;
}
}
internal static Task Run(DacPackageOptions settings, DacDeployOptions option)
{
// new DacPackageOptions();
var package = settings.FindDacPackage();
foreach (var connection in settings.Connections)
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Starting deploy on {0}", connection.Key);
var dacService = new DacServices(connection.Value);
dacService.ProgressChanged += (object sender, DacProgressEventArgs e) =>
{
Console.WriteLine($"{e.Message}: {DateTimeOffset.Now:HH:mm:sss tt zzzz}");
if (e.Status == DacOperationStatus.Completed)
{
Console.WriteLine("-".PadRight(15, '-'));
}
};
dacService.Deploy(package, connection.Key, true, option);
Console.WriteLine("Finished {0}", connection.Key);
Console.ResetColor();
}
return(Task.CompletedTask);
}
public static void Main(string[] args)
{
try
{
var appEnv =
CallContextServiceLocator.Locator.ServiceProvider.GetService(typeof (IApplicationEnvironment)) as
IApplicationEnvironment;
if (appEnv == null) throw new Exception("Couldn't get application environment to get base path.");
var dacPackageLocation = Path.Combine(appEnv.ApplicationBasePath, "database.dacpac");
var dacPackage = DacPackage.Load(dacPackageLocation);
var dacServices = new DacServices(ConnectionString);
dacServices.Deploy(dacPackage, "DOESNTMATTER", true, new DacDeployOptions
{
GenerateSmartDefaults = true,
CreateNewDatabase = true
});
}
catch (Exception ex)
{
// Normally, you should get a "connection can't be made exception", becase the connection string is garbage.
// This is expected and normal.
// However, we get a "resource" not found exception. This exception goes away when we move
// "Microsoft.Data.Tools.Schema.Sql.dll" and "Microsoft.SqlServer.Dac.dll" into the directory
// where "dnx.exe" is located.
Console.WriteLine(ex.Message);
}
}
private static bool DeployDatabase(string databaseServerName, string connString)
{
MessageList = new List <string>();
bool success = true;
var dacSvc = new DacServices(connString);
var dacOptions = new DacDeployOptions();
dacOptions.BlockOnPossibleDataLoss = false;
try
{
using (DacPackage dacpac = DacPackage.Load(@"Database Deployment\SqlResultsCompare.dacpac"))
{
dacSvc.Deploy(dacpac, "SqlResultsCompare", upgradeExisting: true, options: dacOptions);
}
}
catch (Exception ex)
{
success = false;
//TODO: add logging
//MessageList.Add(ex.Message);
Console.WriteLine(ex.Message);
Console.WriteLine(ex.InnerException.ToString());
}
return(success);
}
public override ITestDatabase Build()
{
_configurationDatabaseOptions?.Invoke(_databaseOptions);
if (!_databaseOptions.AlwayCreate && CheckDatabaseExists())
{
return(this);
}
Drop();
var deployOptions = new DacDeployOptions();
foreach (var variable in _databaseOptions.DeployVariables)
{
deployOptions.SqlCommandVariableValues.Add(variable.Key, variable.Value);
}
var dacpacPath = _databaseOptions.DacpacPath;
var instance = new DacServices(_dbBuilder.ConnectionString);
deployOptions.AllowIncompatiblePlatform = _databaseOptions.AllowIncompatiblePlatform;
using (var dacpac = DacPackage.Load(dacpacPath))
{
instance.Deploy(dacpac, _dbBuilder.InitialCatalog, upgradeExisting: true, options: deployOptions);
}
return(this);
}
public static void Deploy(Stream dacpac, string connectionString, string databaseName)
{
var options = new DacDeployOptions()
{
BlockOnPossibleDataLoss = true,
IncludeTransactionalScripts = true,
DropConstraintsNotInSource = false,
DropIndexesNotInSource = false,
DropDmlTriggersNotInSource = false,
DropObjectsNotInSource = false,
DropExtendedPropertiesNotInSource = false,
DropPermissionsNotInSource = false,
DropStatisticsNotInSource = false,
DropRoleMembersNotInSource = false,
};
var service = new DacServices(connectionString);
service.Message += (x, y) =>
{
Console.WriteLine(y.Message.Message);
};
try
{
using (var package = DacPackage.Load(dacpac))
{
service.Deploy(package, databaseName, true, options);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message, true);
}
}
private string GenerateScriptAndOptionallyDeploy(string dbName, DacDeployOptions options, bool runDeployment, int currentIteration)
{
if (options == null)
{
options = new DacDeployOptions();
}
using (DacPackage dacpac = DacPackage.Load(_dacpacPath, DacSchemaModelStorageType.Memory))
{
string connectionString = "Data Source=" + DataSourceName + ";Integrated Security=True";
DacServices dacServices = new DacServices(connectionString);
// Script then deploy, to support debugging of the generated plan
string script = dacServices.GenerateDeployScript(dacpac, dbName, options);
string filePath = GetTestFilePath(string.Format(CultureInfo.CurrentCulture, "deployscript{0}.sql", currentIteration));
File.WriteAllText(filePath, script);
Console.WriteLine("Deployment script written to {0}", filePath);
if (runDeployment)
{
dacServices.Deploy(dacpac, dbName, true, options);
AssertDeploySucceeded(ServerConnectionString, dbName);
}
return(script);
}
}
public static void Main(string[] args)
{
DacpacFileName = args.Any() ? args[0] : DacpacFileName;
var dacServices = new DacServices(ConnectionString);
dacServices.Message += DacServices_Message;
var options = new DacDeployOptions
{
CreateNewDatabase = false,
BlockOnPossibleDataLoss = false,
GenerateSmartDefaults = true,
VerifyDeployment = true,
DropObjectsNotInSource = true
};
Console.WriteLine();
Console.WriteLine("Start process...");
Console.WriteLine();
//TODO na pszyłość można to wykorzystać
//dacServices.GenerateDeployReport(DacPackage.Load(DacpacFileName), DatabaseName, options);
dacServices.Deploy(DacPackage.Load(DacpacFileName), DatabaseName, true, options);
Console.ReadKey();
}
public void Deploy(string targetDatabaseName)
{
EnsurePackageLoaded();
EnsureConnectionStringComplete();
_console.WriteLine($"Deploying to database '{targetDatabaseName}'");
try
{
var services = new DacServices(ConnectionStringBuilder.ConnectionString);
services.Message += HandleDacServicesMessage;
services.Deploy(Package, targetDatabaseName, true, DeployOptions);
_console.WriteLine($"Successfully deployed database '{targetDatabaseName}'");
}
catch (DacServicesException ex)
{
if (ex.InnerException != null)
{
_console.WriteLine($"ERROR: Deployment of database '{targetDatabaseName}' failed: {ex.InnerException.Message}");
}
else
{
_console.WriteLine($"ERROR: Deployment of database '{targetDatabaseName}' failed: {ex.Message}");
}
}
catch (Exception ex)
{
_console.WriteLine($"ERROR: An unknown error occurred while deploying database '{targetDatabaseName}': {ex.Message}");
}
}
/// <summary>
/// Loads database from file.
/// </summary>
public void LoadDataBase()
{
var dacOptions = new DacDeployOptions {
CreateNewDatabase = true
};
var dacServiceInstance = new DacServices(_connectionString);
var dacpacPath = ConfigurationManager.AppSettings["dacpacFilePath"];
if (dacpacPath != null && dacpacPath.Contains("AppPath"))
{
dacpacPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + dacpacPath.Replace("AppPath", string.Empty);
}
if (File.Exists(dacpacPath))
{
using (DacPackage dacpac = DacPackage.Load(dacpacPath))
{
dacServiceInstance.Deploy(dacpac, "TicketManagement", true, dacOptions);
}
}
else
{
throw new ConfigurationErrorsException("Error load database from dacpac file.");
}
}
public void Deploy(FileInfo dacpacPackage, string targetDatabaseName)
{
EnsureConnectionStringComplete();
if (!dacpacPackage.Exists)
{
throw new ArgumentException($"File {dacpacPackage.FullName} does not exist.", nameof(dacpacPackage));
}
using var package = DacPackage.Load(dacpacPackage.FullName);
_console.WriteLine($"Deploying package '{package.Name}' version '{package.Version}' to database '{targetDatabaseName}'");
try
{
var services = new DacServices(ConnectionStringBuilder.ConnectionString);
services.Message += HandleDacServicesMessage;
services.Deploy(package, targetDatabaseName, true, DeployOptions);
_console.WriteLine($"Successfully deployed database '{targetDatabaseName}'");
}
catch (DacServicesException ex)
{
if (ex.InnerException != null)
{
_console.WriteLine($"ERROR: Deployment of database '{targetDatabaseName}' failed: {ex.InnerException.Message}");
}
else
{
_console.WriteLine($"ERROR: Deployment of database '{targetDatabaseName}' failed: {ex.Message}");
}
}
catch (Exception ex)
{
_console.WriteLine($"ERROR: An unknown error occurred while deploying database '{targetDatabaseName}': {ex.Message}");
}
}
public void Run()
{
DacServices services = new DacServices(@"Server=ITK\DEV17;Integrated Security=true;");
DacPackage package = DacPackage.Load(file01, DacSchemaModelStorageType.Memory, FileAccess.ReadWrite);
string dbName = @"MojaBazaDAC";
bool updateExisting = true;
TSqlModel tm01 = new TSqlModel(file01);
TSqlModel newModel = new TSqlModel(tm01.Version, tm01.CopyModelOptions());
//package.UpdateModel(filteredModel, new PackageMetadata())
DacDeployOptions opts = new DacDeployOptions
{
ExcludeObjectTypes = new ObjectType[]
{
ObjectType.Users,
ObjectType.RoleMembership
}
};
services.Deploy(package,
dbName,
updateExisting,
opts
);
}
/// <summary>
/// Deploys a given database from a dacpac file.
/// </summary>
public void Deploy(string connectionString,
string databaseName,
string dacPacFileName)
{
Messages.Add($"Deploying database: {databaseName}");
var dacOptions = new DacDeployOptions
{
BlockOnPossibleDataLoss = false,
TreatVerificationErrorsAsWarnings = true,
AllowIncompatiblePlatform = true,
IgnoreFileAndLogFilePath = true
};
var dacServiceInstance = new DacServices(connectionString);
dacServiceInstance.ProgressChanged += (s, e) => Messages.Add(e.Message);
dacServiceInstance.Message += (s, e) => Messages.Add(e.Message.Message);
try
{
using (var dacpac = DacPackage.Load(dacPacFileName))
{
dacServiceInstance.Deploy(dacpac, databaseName,
true, // upgrade existing
dacOptions);
}
}
catch (Exception ex)
{
Messages.Add(ex.Message);
throw;
}
}
public void Run(string connectionString)
{
var providerConnectionString = new EntityConnectionStringBuilder(connectionString).ProviderConnectionString;
DbConfiguration.SetConfiguration(new PocDbConfiguration());
var context = new DbContext(providerConnectionString);
context.Database.Initialize(true);
using (var connection = new SqlConnection(providerConnectionString))
{
connection.Open();
var connectionStringBuilder = new SqlConnectionStringBuilder(providerConnectionString)
{
AttachDBFilename = String.Empty
};
var dacServices = new DacServices(connectionStringBuilder.ToString());
#if DEBUG
const string dacpacFile = @"..\..\..\Database\bin\Debug\Database.dacpac";
#else
const string dacpacFile = @"..\..\..\Database\bin\Release\Database.dacpac";
#endif
var package = DacPackage.Load(dacpacFile);
dacServices.Deploy(package, connection.Database, true);
connection.Close();
}
}
public DacpacDbFixture()
{
Console.WriteLine("Running fixture constructor...");
_localDb = new SqlLocalDbApi();
DateTime nowUtc = DateTime.UtcNow;
LocalDbInstanceName = $"{nowUtc.ToString("yyyyMMddHHmmssFFFFFFF")}"; //something mostly unique
_instance = _localDb.GetOrCreateInstance(LocalDbInstanceName);
_manager = _instance.Manage();
if (!_instance.IsRunning)
{
_manager.Start();
}
var packagePath = "SimpleDb.dacpac";
var deployOptions = new DacDeployOptions
{
CreateNewDatabase = true,
GenerateSmartDefaults = true,
};
deployOptions.SqlCommandVariableValues["LoginName"] = DatabaseUserName;
deployOptions.SqlCommandVariableValues["Password"] = DatabasePassword;
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.DataSource = _manager.NamedPipe;
csb.IntegratedSecurity = true;
var databaseName = "SimpleDbUnitTest";
var debugConnectionString = csb.ConnectionString;
var dacServices = new DacServices(debugConnectionString);
using (var package = DacPackage.Load(packagePath))
{
dacServices.Deploy(package, databaseName, true, deployOptions);
}
csb.InitialCatalog = databaseName;
//csb.UserID = DatabaseUserName;
//csb.Password = DatabasePassword;
//csb.IntegratedSecurity = false;
ConnectionString = csb.ConnectionString;
EntityConnectionStringBuilder ecsb = new EntityConnectionStringBuilder();
string nameOfConnectionString = "SimpleDbModel"; //NOTE: HACK: this must match the name of my Entity Framework model (the .edmx guy)
string providerName = "System.Data.SqlClient";
ecsb.Provider = providerName;
ecsb.ProviderConnectionString = csb.ConnectionString;
ecsb.Metadata = $"res://*/{nameOfConnectionString}.csdl|res://*/{nameOfConnectionString}.ssdl|res://*/{nameOfConnectionString}.msl";
EfConnectionString = ecsb.ConnectionString;
NumberOfTimesDacpacWasApplied++;
Debug.WriteLine($">> The DACPAC has been applied {NumberOfTimesDacpacWasApplied} times");
Console.WriteLine($">> The DACPAC has been applied {NumberOfTimesDacpacWasApplied} times");
}
/// <summary>
/// Deploys a Dacpac package into a local database
/// </summary>
/// <param name="database"></param>
/// <param name="packageFilePath"></param>
/// <param name="deployOptions"></param>
public static void DeployDacpac(this LocalDatabase database, string packageFilePath, DacDeployOptions deployOptions = null)
{
var dacServices = new DacServices(database.ConnectionString);
using (var package = DacPackage.Load(packageFilePath))
{
dacServices.Deploy(package, database.DatabaseName, true, deployOptions);
}
}
static void Main(string[] args)
{
DacServices ds = new DacServices(@"Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=true");
using (DacPackage dp = DacPackage.Load(@"C:\temp\mydb.dacpac"))
{
ds.Deploy(dp, @"DATABASENAME", upgradeExisting: false, options: null, cancellationToken: null);
}
}
public void Deploy(string connectionString, string databaseName)
{
var services = new DacServices(connectionString);
var options = new DacDeployOptions {
ScriptDatabaseOptions = false,
BlockOnPossibleDataLoss = true
};
services.Deploy(_dacpac, databaseName, upgradeExisting: true, options: options);
}
public void DeployDacpac(string dacpacFileName, string dbName, string connectionstring)
{
dacpacFileName = Path.GetFullPath(dacpacFileName);
var dacServices = new DacServices(connectionstring);
dacServices.Message += (sender, args) => Debug.WriteLineIf(Debugger.IsAttached, args.Message);
dacServices.ProgressChanged += OnDacServerProcessChanged;
var package = DacPackage.Load(dacpacFileName);
CancellationToken? cancellationToken = new CancellationToken();
dacServices.Deploy(package, dbName, true, null, cancellationToken);
}
public void Deploy()
{
string path = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\..\\", _dacPacFileName));
DacServices ds = new DacServices(_connectionString);
using (DacPackage dp = DacPackage.Load(path))
{
ds.Deploy(dp, @"TicketManagementTest", upgradeExisting: false, options: null, cancellationToken: null);
}
}
static void Main(string[] args)
{
var Options = new Options();
string vDacDictionary = null;
string vDacProfile = null;
string vtargetDatabase = null;
string connectionString = null;
bool isValid = false;
try
{
isValid = CommandLine.Parser.Default.ParseArguments(args, Options);
}
catch (Exception e)
{
Console.WriteLine("there is an error in command line arguments , try reading it from configuration " + e.Message);
}
if (isValid)
{
vDacDictionary = Options.DacDictionary;
vDacProfile = Options.DacProfile;
vtargetDatabase = Options.TargetDatabase;
connectionString = ConnectionStringResolver(Options, connectionString);
}
else
{
vDacDictionary = ConfigurationManager.AppSettings.Get("dac directory");
vDacProfile = ConfigurationManager.AppSettings.Get("dac profile");
vtargetDatabase = ConfigurationManager.AppSettings.Get("targetdatabase");
connectionString = ConfigurationManager.ConnectionStrings["Mask"].ConnectionString;
}
string path = Directory.GetCurrentDirectory();
// connectionString = ConnectionStringResolver(Options, connectionString);
try
{
var dp = DacPackage.Load(path + $@"\" + vDacDictionary);
var dacService = new DacServices(connectionString);
var dacProfile = DacProfile.Load(path + $@"\" + vDacProfile);
dacService.Message += (object sender, Microsoft.SqlServer.Dac.DacMessageEventArgs eventArgs) => Console.WriteLine(eventArgs.Message.Message);
dacService.Deploy(dp, vtargetDatabase, true, dacProfile.DeployOptions);
Console.WriteLine("Deployment completed successfully !!!!");
}
catch (Exception ex)
{
Console.WriteLine("Error in the deployment. please correct the input " + ex.Message);
}
Console.ReadLine();
}
public void TestStopDeployment()
{
// Given database name
string dbName = TestContext.TestName;
// Delete any existing artifacts from a previous run
TestUtils.DropDatabase(TestUtils.ServerConnectionString, dbName);
// When deploying using the deployment stopping contributor
try
{
DacDeployOptions options = new DacDeployOptions
{
AdditionalDeploymentContributors = DeploymentStoppingContributor.ContributorId
};
// Deploy initial schema, should pass as no data motion
using (DacPackage dacpac = DacPackage.Load(_dacpacPath, DacSchemaModelStorageType.Memory))
{
DacServices dacServices = new DacServices(TestUtils.ServerConnectionString);
dacServices.Deploy(dacpac, dbName, false, options);
}
// Create schema that will cause data motion by adding column before existing one
using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, null))
{
model.AddObjects("CREATE TABLE [dbo].[t1] (motion int NOT NULL, c1 INT NOT NULL PRIMARY KEY)");
DacPackageExtensions.BuildPackage(_dacpacPath, model, new PackageMetadata());
}
// Attempt to deploy and verify it fails as there's now data motion blocking it
using (DacPackage dacpac = DacPackage.Load(_dacpacPath, DacSchemaModelStorageType.Memory))
{
DacServices dacServices = new DacServices(TestUtils.ServerConnectionString);
try
{
dacServices.GenerateDeployScript(dacpac, dbName, options);
Assert.Fail("Expected Deployment to fail and exception to be thrown");
}
catch (DacServicesException expectedException)
{
Assert.IsTrue(expectedException.Message.Contains(DeploymentStoppingContributor.ErrorViaPublishMessage),
"Expected Severity.Error message passed to base.PublishMessage to block deployment");
Assert.IsTrue(expectedException.Message.Contains(DeploymentStoppingContributor.ErrorViaThrownException),
"Expected thrown exception to block deployment");
}
}
}
finally
{
TestUtils.DropDatabase(TestUtils.ServerConnectionString, dbName);
}
}
public static void DeployDacpac(string testDatabase, string databaseName)
{
var dacpacFile = string.Format(TestRunnerHelper.DacpacLocation, testDatabase);
using (var package = DacPackage.Load(dacpacFile, DacSchemaModelStorageType.Memory, FileAccess.Read))
{
var service = new DacServices(ConnectionString(databaseName));
service.Deploy(package, databaseName);
}
}
public void TestIncludePlanFiltererInDacpac()
{
// Given a model with objects that use "dev", "test" and "prod" schemas
// and the contributor information built in
var model = CreateTestModel();
string existingPackagePath = GetTestFilePath("includesContributor.dacpac");
Console.WriteLine("Build dacpac to \n" + existingPackagePath);
DacPackageExtensions.BuildPackage(existingPackagePath, model, new PackageMetadata(), new PackageOptions()
{
DeploymentContributors = new[] { new DeploymentContributorInformation()
{
ExtensionId = PlanFilterer.PlanFiltererContributorId
} }
});
DacServices services = new DacServices("Server=(localdb)\\MSSQLLocalDB;Integrated Security=true;");
// When publishing to production (filtering to exclude "dev" and "test" schemas)
string productionDbName = "ProductionDB";
using (DacPackage package = DacPackage.Load(existingPackagePath, DacSchemaModelStorageType.Memory))
{
DacDeployOptions options = new DacDeployOptions();
// Specify the filter to use and what arguments it needs. Note that this is a little limited by
// having to pass string-based arguments. This could be worked around by serializing arguments to a
// file and passing the file path to the contributor if you need to do anything advanced.
options.AdditionalDeploymentContributorArguments =
PlanFilterer.BuildPlanFiltererArgumentString("SchemaBasedFilter", new Dictionary <string, string>()
{
{ "Schema1", "dev" },
{ "Schema2", "test" },
});
// For test purposes, always create a new database (otherwise previous failures might mess up our result)
options.CreateNewDatabase = true;
// Run the deployment with the options as specified
services.Deploy(package, productionDbName, upgradeExisting: true, options: options);
}
// Then expect only the "prod" schema objects to remain in the new package
// Extract the dacpac back from the database and ensure that only production elements are there
string extractedPackagePath = GetTestFilePath("extracted.dacpac");
services.Extract(extractedPackagePath, productionDbName, "AppName", new Version(1, 0));
var extractedModel = _trash.Add(new TSqlModel(extractedPackagePath, DacSchemaModelStorageType.Memory));
Assert.AreEqual(TopLevelProdElementCount, CountTablesViewsAndSchemas(extractedModel));
AssertAllObjectsHaveSchemaName(extractedModel, "prod");
}
public virtual void TestInitialize()
{
var instance = new DacServices(DatabaseConnectionString);
var path = Path.GetFullPath(Path.Combine(TestContext.TestDir,
@"..\..\..\Build\Database\Database.dacpac"));
using (var dacpac = DacPackage.Load(path))
{
instance.Deploy(dacpac, DatabaseName);
}
DatabaseContext = new DatabaseContext(DatabaseConnectionString);
}
public override Task Run(CancellationToken cancellationToken)
{
cancellationToken.ThrowIfCancellationRequested();
var stopwatch = Stopwatch.StartNew();
try
{
LogDebug("Internal", "DacPac Deployment AsyncTask Started.");
var dacPackage = DacPackage.Load(Arguments.DacPacFilePath);
var dacServices = new DacServices(GetConnectionString());
dacServices.Message += (_, args) =>
{
switch (args.Message.MessageType)
{
case DacMessageType.Error:
LogError("DacServices", args.Message.ToString());
break;
case DacMessageType.Warning:
LogWarning("DacServices", args.Message.ToString());
break;
case DacMessageType.Message:
LogInfo("DacServices", args.Message.ToString());
break;
default:
throw new ArgumentOutOfRangeException();
}
};
dacServices.ProgressChanged += (_, args) => LogInfo("DacServices Progress", "OperationId {0}, Status {1}, Message {2}", args.OperationId, args.Status, args.Message);
dacServices.Deploy(dacPackage, DatabaseInfo.Name, true, Arguments.DacDeployOptions, cancellationToken);
}
catch (Exception ex)
{
LogError("Internal", "DacPac Deployment AsyncTask failed after {0}ms with the following error: {1}", stopwatch.ElapsedMilliseconds, ex.Message);
ProgressUpdate(this, false, stopwatch.ElapsedMilliseconds);
return(Task.CompletedTask);
}
LogInfo("Internal", "DacPac Deployment AsyncTask completed successfully in {0}ms.", stopwatch.ElapsedMilliseconds);
ProgressUpdate(this, true, stopwatch.ElapsedMilliseconds);
return(Task.CompletedTask);
}
static void DeployTestDatabase()
{
DacServices sc = new DacServices(@"Server=DESKTOP-D0NSBJ1\SQLEXPRESS;Database=PersonalWebsiteDb_Test;Trusted_Connection=True;MultipleActiveResultSets=true");
DacDeployOptions options = new DacDeployOptions();
options.CreateNewDatabase = true;
string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "PersonalWebsite.Database.dacpac");
Console.WriteLine("Deploying database ...");
sc.Deploy(DacPackage.Load(path), "PersonalWebsiteDb_Test", true, options);
Console.WriteLine("Database deployed.");
}
public static void Initialize(TestContext context)
{
DacDeployOptions options = new DacDeployOptions {
CreateNewDatabase = true,
DeployDatabaseInSingleUserMode = true,
BlockOnPossibleDataLoss = false
};
DacPackage package = DacPackage.Load(@"Enkoni.Framework.Entities.Tests.Database.dacpac", DacSchemaModelStorageType.File);
DacServices services = new DacServices(@"Data Source=(localdb)\ProjectsV13;Initial Catalog=Enkoni.Framework.Entities.Tests.Database;Integrated Security=True;Pooling=False;Connect Timeout=30");
services.Deploy(package, "Enkoni.Framework.Entities.Tests.Database", upgradeExisting: true, options: options);
}
public static SqlTestDB CreateFromDacpac(InstanceInfo instance, string dacpacPath, DacDeployOptions deployOptions = null, bool dropDatabaseOnCleanup = false)
{
string dbName = Path.GetFileNameWithoutExtension(dacpacPath);
DacServices ds = new DacServices(instance.BuildConnectionString(dbName));
using (DacPackage dp = DacPackage.Load(dacpacPath, DacSchemaModelStorageType.Memory))
{
ds.Deploy(dp, dbName, true, deployOptions);
}
var sqlDb = new SqlTestDB(instance, dbName, dropDatabaseOnCleanup);
return(sqlDb);
}
public void DeployDacpac(string dacpacFileName, string dbName, string connectionstring)
{
dacpacFileName = Path.GetFullPath(dacpacFileName);
var dacServices = new DacServices(connectionstring);
dacServices.Message += (sender, args) => Debug.WriteLineIf(Debugger.IsAttached, args.Message);
dacServices.ProgressChanged += OnDacServerProcessChanged;
var package = DacPackage.Load(dacpacFileName);
CancellationToken?cancellationToken = new CancellationToken();
dacServices.Deploy(package, dbName, true, new DacDeployOptions()
{
}, cancellationToken);
}
public void Create(string targetDatabaseName)
{
var dacpac = GetDacPackage();
var service = new DacServices(_connBuilder.ConnectionString);
var options = new DacDeployOptions
{
IncludeTransactionalScripts = true,
BlockOnPossibleDataLoss = false,
};
service.Message += service_Message;
service.ProgressChanged += service_ProgressChanged;
service.Deploy(dacpac, targetDatabaseName, true, options);
}
public static void TestClassInitialize(TestContext context)
{
// Generate a name for the temporary database
testDatabaseName = string.Format(CultureInfo.InvariantCulture, "TestRun_{0}", DateTime.Now.ToString("s", CultureInfo.InvariantCulture).Replace(':', '_'));
// Use DAC service to deploy the temporary database
var dacService = new DacServices(string.Format(CultureInfo.InvariantCulture, ConnectionStringTemplate, "master"));
var dacPacLocation = Path.Combine(
// ReSharper disable once AssignNullToNotNullAttribute
Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location),
"Snek.ToolsSample.Database.dacpac");
using (var stream = File.OpenRead(dacPacLocation))
{
using (var package = DacPackage.Load(stream))
{
dacService.Deploy(package, testDatabaseName);
}
}
}
private string GenerateScriptAndOptionallyDeploy(string dbName, DacDeployOptions options, bool runDeployment, int currentIteration)
{
if (options == null)
{
options = new DacDeployOptions();
}
using (DacPackage dacpac = DacPackage.Load(_dacpacPath, DacSchemaModelStorageType.Memory))
{
DacServices dacServices = new DacServices(TestUtils.ServerConnectionString);
// Script then deploy, to support debugging of the generated plan
string script = dacServices.GenerateDeployScript(dacpac, dbName, options);
string filePath = GetTestFilePath(string.Format(CultureInfo.CurrentCulture, "deployscript{0}.sql", currentIteration));
File.WriteAllText(filePath, script);
Console.WriteLine("Deployment script written to {0}", filePath);
if (runDeployment)
{
dacServices.Deploy(dacpac, dbName, true, options);
AssertDeploySucceeded(TestUtils.ServerConnectionString, dbName);
}
return script;
}
}
private void Deploy(string dbName, DacDeployOptions options = null)
{
if (options == null)
{
options = new DacDeployOptions();
}
using (DacPackage dacpac = DacPackage.Load(_dacpacPath, DacSchemaModelStorageType.Memory))
{
DacServices dacServices = new DacServices(TestUtils.ServerConnectionString);
// Script then deploy, to support debugging of the generated plan
string script = dacServices.GenerateDeployScript(dacpac, dbName, options);
File.WriteAllText(GetTestFilePath("deployscript.sql"), script);
dacServices.Deploy(dacpac, dbName, true, options);
}
}
public void TestIncludePlanFiltererInDacpac()
{
// Given a model with objects that use "dev", "test" and "prod" schemas
// and the contributor information built in
var model = CreateTestModel();
string existingPackagePath = GetTestFilePath("includesContributor.dacpac");
Console.WriteLine("Build dacpac to \n" + existingPackagePath);
DacPackageExtensions.BuildPackage(existingPackagePath, model, new PackageMetadata(), new PackageOptions()
{
DeploymentContributors = new[] { new DeploymentContributorInformation() { ExtensionId = PlanFilterer.PlanFiltererContributorId } }
});
DacServices services = new DacServices("Server=(localdb)\\MSSQLLocalDB;Integrated Security=true;");
// When publishing to production (filtering to exclude "dev" and "test" schemas)
string productionDbName = "ProductionDB";
using (DacPackage package = DacPackage.Load(existingPackagePath, DacSchemaModelStorageType.Memory))
{
DacDeployOptions options = new DacDeployOptions();
// Specify the filter to use and what arguments it needs. Note that this is a little limited by
// having to pass string-based arguments. This could be worked around by serializing arguments to a
// file and passing the file path to the contributor if you need to do anything advanced.
options.AdditionalDeploymentContributorArguments =
PlanFilterer.BuildPlanFiltererArgumentString("SchemaBasedFilter", new Dictionary<string, string>()
{
{"Schema1", "dev"},
{"Schema2", "test"},
});
// For test purposes, always create a new database (otherwise previous failures might mess up our result)
options.CreateNewDatabase = true;
// Run the deployment with the options as specified
services.Deploy(package, productionDbName, upgradeExisting: true, options: options);
}
// Then expect only the "prod" schema objects to remain in the new package
// Extract the dacpac back from the database and ensure that only production elements are there
string extractedPackagePath = GetTestFilePath("extracted.dacpac");
services.Extract(extractedPackagePath, productionDbName, "AppName", new Version(1, 0));
var extractedModel = _trash.Add(new TSqlModel(extractedPackagePath, DacSchemaModelStorageType.Memory));
Assert.AreEqual(TopLevelProdElementCount, CountTablesViewsAndSchemas(extractedModel));
AssertAllObjectsHaveSchemaName(extractedModel, "prod");
}
private void DeployDacPac()
{
this.NotifyAboutProgress(Blobstore.DacPacDeploying, this.databaseName, this.serverInstance);
var connectionString = this.CreateConnectionString(this.databaseName);
var dacOptions = new DacDeployOptions { BlockOnPossibleDataLoss = false };
var dacServiceInstance = new DacServices(connectionString);
dacServiceInstance.ProgressChanged += (sender, args) => this.NotifyAboutProgress("..." + args.Message);
dacServiceInstance.Message += (sender, args) => this.NotifyAboutProgress("..." + args.Message.Message);
using (var stream = new MemoryStream(Blobstore.DacPac))
using (var dacpac = DacPackage.Load(stream))
{
dacServiceInstance.Deploy(dacpac, this.databaseName, upgradeExisting: true, options: dacOptions);
}
this.NotifyAboutProgress(Blobstore.DacPacDeployed, this.databaseName, this.serverInstance);
}
public void Deploy(string publishSettingsFile)
{
if (!File.Exists(publishSettingsFile))
{
throw new FileNotFoundException(string.Format("Provided publish settings '{0}' could not be found!", publishSettingsFile));
}
var latest = historyProvider.GetLatest();
var publishData = new Project(publishSettingsFile);
var connectionString = publishData.GetPropertyValue("TargetConnectionString");
var targetDatabaseName = publishData.GetPropertyValue("TargetDatabaseName");
var currentVersion = versionProvider.GetVersion(connectionString, targetDatabaseName);
Log("Deployment mode for {0} with version {1}.", targetDatabaseName, currentVersion);
if (latest.Version == currentVersion)
{
Log("Target is latest version: {0}. Skipping deployment.", latest.Version);
return;
}
var dacService = new DacServices(connectionString);
dacService.Message += (s, e) =>
{
Log("DAC Message: {0}", e.Message);
};
dacService.ProgressChanged += (s, e) =>
{
Log("{0}: {1}", e.Status, e.Message);
};
var options = new DacDeployOptions();
//Load the publish settings
foreach (var item in publishData.Properties)
{
var prop = options.GetType().GetProperty(item.Name);
if (prop != null)
{
var val = Convert.ChangeType(item.UnevaluatedValue, prop.PropertyType);
prop.SetValue(options, val);
}
}
if (currentVersion == null)
{
//Deploy latest
Log("Deploy latest version: {0}.", latest.Version);
dacService.Deploy(latest, targetDatabaseName, true, options);
return;
}
Log("Upgrading {0} -> {1}.", currentVersion, latest.Version);
try
{
var count = 0;
foreach (var package in historyProvider.GetHistory(currentVersion).OrderBy(x => x.Version))
{
Log();
Log("Applying upgrade #{0}: {1} -> {2}.", ++count, currentVersion, package.Version);
Log();
if (count > 0)
{
options.BackupDatabaseBeforeChanges = false;
}
dacService.Deploy(package, targetDatabaseName, true, options);
currentVersion = package.Version;
}
}
catch
{
var file = new FileInfo(publishSettingsFile);
var name = file.Name.Substring(0, file.Name.LastIndexOf(file.Extension));
File.WriteAllText(Path.Combine(publishData.DirectoryPath, string.Format("{0}v{1}_error.log", name, currentVersion)), logBuilder.ToString());
throw;
}
}
private void Deploy(DacServices svc, string TargetDatabaseName, string Path)
{
Console.WriteLine("\n\rPerforming Deploy of {0} to {1} at {2}", Path, TargetDatabaseName, System.DateTime.Now.ToLongTimeString());
using (DacPackage dacpac = DacPackage.Load(Path))
{
//svc.Deploy(dacpac, TargetDatabaseName);
svc.Deploy(dacpac, TargetDatabaseName, true);
}
}
public void DeployDacPac(string databaseName)
{
ProviderConfiguration config =
(ProviderConfiguration)ConfigurationManager.GetSection("dbTestMonkey/" + ConfigurationSectionName);
SqlDatabaseConfiguration databaseConfiguration = config.Databases[databaseName];
string dacpacPath = databaseConfiguration.DacPacFilePath;
_logAction("Loading Dacpac into memory");
Stopwatch totalTimer = Stopwatch.StartNew();
Stopwatch loadPackageTimer = Stopwatch.StartNew();
_logAction("current directory:" + Environment.CurrentDirectory);
_logAction("dacpacPath:" + dacpacPath);
using (DacPackage dacPackage = DacPackage.Load(dacpacPath, DacSchemaModelStorageType.Memory, FileAccess.Read))
{
databaseName = databaseName ?? dacPackage.Name;
loadPackageTimer.Stop();
_logAction("Package loaded, initialising DacServices");
using (IDbConnection connection = _connectionFactory())
{
try
{
connection.ChangeDatabase(databaseName);
}
catch
{
_logAction(
"Could not change connection to database " +
databaseName +
" before pre-deployment script. Database may not yet exist.");
}
// Execute the DAC pre-deployment script.
if (dacPackage.PreDeploymentScript != null)
{
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = new StreamReader(dacPackage.PreDeploymentScript).ReadToEnd();
command.CommandText = command.CommandText.Replace("\nGO", "");
command.ExecuteNonQuery();
}
}
_logAction("Deploying dacpac");
Stopwatch dacpacDeployTimer = Stopwatch.StartNew();
DacDeployOptions options = new DacDeployOptions()
{
CreateNewDatabase = true
};
Stopwatch dacpacServiceTimer = Stopwatch.StartNew();
DacServices dacServices = new DacServices(connection.ConnectionString);
dacpacServiceTimer.Stop();
_logAction("DacServices initialisation took " + dacpacServiceTimer.ElapsedMilliseconds + " ms");
dacServices.Message += dacServices_Message;
dacServices.ProgressChanged += dacServices_ProgressChanged;
dacServices.Deploy(dacPackage, databaseName, upgradeExisting: true, options: options);
dacpacDeployTimer.Stop();
_logAction(
"Deploying dacpac took " +
dacpacDeployTimer.ElapsedMilliseconds +
" ms");
// If the user has opted to only run the post-deployment script after the DACPAC
// deployment and not per-test, it needs to run once.
if (!config.Databases[databaseName].ExecutePostDeploymentScriptPerTest)
{
ExecutePostDeploymentScript(databaseName, dacPackage);
}
}
}
totalTimer.Stop();
_logAction("Total dacpac time was " + totalTimer.ElapsedMilliseconds + " ms");
}
private static void PublishProductionDacpacAndVerifyContents(string productionPackagePath)
{
string extractedPackagePath = GetFilePathInCurrentDirectory("extracted.dacpac");
using (DacPackage package = DacPackage.Load(productionPackagePath, DacSchemaModelStorageType.Memory))
{
Console.WriteLine("Deploying the production dacpac to 'ProductionDB'");
DacServices services = new DacServices("Server=(localdb)\\MSSQLLocalDB;Integrated Security=true;");
services.Deploy(package, "ProductionDB");
Console.WriteLine("Extracting the 'ProductionDB' back to a dacpac for comparison");
services.Extract(extractedPackagePath, "ProductionDB", "AppName", new Version(1, 0));
}
using (TSqlModel extractedModel = new TSqlModel(extractedPackagePath, DacSchemaModelStorageType.Memory))
{
Console.WriteLine("Objects found in extracted package: '" + productionPackagePath + "'");
PrintTablesViewsAndSchemas(extractedModel);
}
}
public void Deploy(string connectionString, string databaseName) {
var services = new DacServices(connectionString);
services.Deploy(_dacpac, databaseName, upgradeExisting: true);
}
public static SqlTestDB CreateFromDacpac(InstanceInfo instance, string dacpacPath, DacDeployOptions deployOptions = null, bool dropDatabaseOnCleanup = false)
{
string dbName = Path.GetFileNameWithoutExtension(dacpacPath);
DacServices ds = new DacServices(instance.BuildConnectionString(dbName));
using (DacPackage dp = DacPackage.Load(dacpacPath, DacSchemaModelStorageType.Memory))
{
ds.Deploy(dp, dbName, true, deployOptions);
}
var sqlDb = new SqlTestDB(instance, dbName, dropDatabaseOnCleanup);
return sqlDb;
}
private static void UpgradeDatabase(ConsoleAppConfig app)
{
var snapshotDir = Path.Combine(app.Project.DirectoryPath, "Snapshots");
if (!Directory.Exists(snapshotDir))
{
throw new IOException("No Snapshots folder found.");
}
var snapshots = Directory.GetFiles(snapshotDir).Where(x => x.EndsWith(".dacpac")).ToArray();
Array.Sort(snapshots);
//TODO: Make no assumptions to naming conventions
var latest = snapshots.Last();
var dac = DacPackage.Load(latest);
Console.WriteLine(string.Format("Latest DAC version is {0}.", dac.Version));
//Target
var connectionString = app.PublishSettings.GetPropertyValue("TargetConnectionString");
var targetDatabaseName = app.PublishSettings.GetPropertyValue("TargetDatabaseName");
Console.WriteLine("Connecting to target database to look up current version...");
Version existing = null;
using (var connection = new SqlConnection(connectionString))
{
var cmd = new SqlCommand(string.Format("select top(1) type_version from msdb.dbo.sysdac_instances_internal where instance_name = '{0}'", targetDatabaseName), connection);
connection.Open();
var result = cmd.ExecuteReader();
if (result.Read())
{
existing = new Version(result["type_version"].ToString());
}
}
if (existing == null || dac.Version > existing)
{
if (existing == null)
{
Console.WriteLine("No database found. Deploying...");
}
else
{
Console.WriteLine(string.Format("Database found. Running version is {0}. Starting upgrade...", existing));
}
var svc = new DacServices(connectionString);
svc.Message += dacServices_Message;
svc.ProgressChanged += dacServices_ProgressChanged;
var options = new DacDeployOptions();
//Load the publish settings
foreach (var item in app.PublishSettings.Properties)
{
var prop = options.GetType().GetProperty(item.Name);
if (prop != null)
{
var val = Convert.ChangeType(item.UnevaluatedValue, prop.PropertyType);
prop.SetValue(options, val);
}
}
svc.Deploy(dac, targetDatabaseName, existing != null, options);
}
else
{
Console.WriteLine("Version is up to date. Skipping deployment.");
}
}
