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);
}
}
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 void Run()
{
// load dacpacs
DacPackage pk01 = DacPackage.Load(folderPath01 + file01);
DacPackage pk02 = DacPackage.Load(folderPath02 + file02);
// configure (same as .publish xml)
DacDeployOptions options = new DacDeployOptions
{
AdditionalDeploymentContributors = "DBContributorsPack.DropToTxtContributor",
ExcludeObjectTypes = new ObjectType[]
{
ObjectType.Users,
ObjectType.RoleMembership
},
DropObjectsNotInSource = true,
DoNotDropObjectTypes = new ObjectType[]
{
ObjectType.DatabaseRoles
}
};
// compare
string s = DacServices.GenerateDeployScript(pk01, pk02, "name", options);
Console.WriteLine(s);
Console.ReadLine();
}
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();
}
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 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);
}
}
/// <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 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>
/// 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 override void Execute()
{
DacPackage dacpac = DacPackage.Load(this.Parameters.PackageFilePath);
DacDeployOptions options = GetDefaultDeployOptions();
DeployReport = this.DacServices.GenerateDeployReport(dacpac, this.Parameters.DatabaseName, options, this.CancellationToken);
}
private void RunTestAndVerifyResult(DacDeployOptions options, Action <string> verifyAction, int currentIteration)
{
// Given database name, and path to save to
string dbName = TestContext.TestName;
// Delete any existing artifacts from a previous run
DropDbAndDeleteFiles(dbName);
// NOTE: Cannot turn on deployment by default in the sample test since we target LocalDB and the "Online" option
// being tested is only supported in enterprise editions. To fully validate that the output of this
// contributor is correct, change the DataSourceName to target an enterprise server and change
// runDeployment to true
bool runDeployment = false;
try
{
string deployScript = GenerateScriptAndOptionallyDeploy(dbName, options, runDeployment, currentIteration);
verifyAction(deployScript);
}
finally
{
if (runDeployment)
{
DropDbAndDeleteFiles(dbName);
}
}
}
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 override void Execute()
{
DacPackage dacpac = DacPackage.Load(this.Parameters.PackageFilePath);
DacDeployOptions options = this.GetDefaultDeployOptions();
this.DacServices.Deploy(dacpac, this.Parameters.DatabaseName, this.Parameters.UpgradeExisting, options, this.CancellationToken);
}
public DeploymentOptions()
{
DacDeployOptions options = new DacDeployOptions();
// Adding these defaults to ensure behavior similarity with other tools. Dacfx and SSMS import/export wizards use these defaults.
// Tracking the full fix : https://github.com/microsoft/azuredatastudio/issues/5599
options.AllowDropBlockingAssemblies = true;
options.AllowIncompatiblePlatform = true;
options.DropObjectsNotInSource = true;
options.DropPermissionsNotInSource = true;
options.DropRoleMembersNotInSource = true;
options.IgnoreKeywordCasing = false;
options.IgnoreSemicolonBetweenStatements = false;
options.IgnoreWhitespace = false;
System.Reflection.PropertyInfo[] deploymentOptionsProperties = this.GetType().GetProperties();
foreach (var deployOptionsProp in deploymentOptionsProperties)
{
var prop = options.GetType().GetProperty(deployOptionsProp.Name);
// Note that we set excluded object types here since dacfx has this value as null;
if (prop != null && deployOptionsProp.Name != "ExcludeObjectTypes")
{
deployOptionsProp.SetValue(this, prop.GetValue(options));
}
}
}
public void TestDbCreateDatabaseModifier()
{
// Given database name, and paths to save to
string dbName = TestContext.TestName;
string dataFolder = GetTestDir();
string mdfFilePath = Path.Combine(dataFolder, "mydb.mdf");
string ldfFilePath = Path.Combine(dataFolder, "mydb_log.ldf");
DeleteIfExists(mdfFilePath);
DeleteIfExists(ldfFilePath);
// When deploying using the create database statement changing contributor
try
{
// Deploy to nonexistent database - should work quickly
DacDeployOptions options = SetCreateDatabaseContributorOptions(mdfFilePath, ldfFilePath);
Deploy(dbName, options);
// Then expect the database to be saved under that path
AssertDeploySucceeded(ServerConnectionString, dbName);
Assert.IsTrue(File.Exists(mdfFilePath));
Assert.IsTrue(File.Exists(ldfFilePath));
// Note: for a real application, after creating the DB on the server they may want to
// detach it and reattach using the database path. We are not doing this since it's
// not relevant to this test
}
finally
{
TestUtils.DropDatabase(ServerConnectionString, dbName);
DeleteIfExists(mdfFilePath);
DeleteIfExists(ldfFilePath);
}
}
public void TestDbLocationModifier()
{
// Given database name, and path to save to
string dbName = TestContext.TestName;
string dataFolder = GetTestDir();
string filePrefix = "mydb";
string mdfFilePath = Path.Combine(dataFolder, filePrefix + "_Primary.mdf");
string ldfFilePath = Path.Combine(dataFolder, filePrefix + "_Primary.ldf");
// Delete any existing artifacts from a previous run
DropDbAndDeleteFiles(dbName, mdfFilePath, ldfFilePath);
// When deploying using the location modifying contributor
try
{
DacDeployOptions options = SetLocationChangingContributorOptions(dataFolder, filePrefix);
Deploy(dbName, options);
// Then expect the database to be saved under that path
AssertDeploySucceeded(ServerConnectionString, dbName);
Assert.IsTrue(File.Exists(mdfFilePath));
Assert.IsTrue(File.Exists(ldfFilePath));
// Note: for a real application, after creating the DB on the server they may want to
// detach it and reattach using the database path. We are not doing this since it's
// not relevant to this test
}
finally
{
DropDbAndDeleteFiles(dbName, mdfFilePath, ldfFilePath);
}
}
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 void ValidateSchemaCompareOptionsDefaultAgainstDacFx()
{
DeploymentOptions deployOptions = new DeploymentOptions();
DacDeployOptions dacOptions = new DacDeployOptions();
System.Reflection.PropertyInfo[] deploymentOptionsProperties = deployOptions.GetType().GetProperties();
System.Reflection.PropertyInfo[] ddProperties = dacOptions.GetType().GetProperties();
// Note that DatabaseSpecification and sql cmd variables list is not present in Sqltools service - its not settable and is not used by ADS options.
// TODO : update this test if the above options are added later
Assert.True(deploymentOptionsProperties.Length == ddProperties.Length - 2, $"Number of properties is not same Deployment options : {deploymentOptionsProperties.Length} DacFx options : {ddProperties.Length}");
foreach (var deployOptionsProp in deploymentOptionsProperties)
{
var dacProp = dacOptions.GetType().GetProperty(deployOptionsProp.Name);
Assert.True(dacProp != null, $"DacDeploy property not present for {deployOptionsProp.Name}");
var deployOptionsValue = deployOptionsProp.GetValue(deployOptions);
var dacValue = dacProp.GetValue(dacOptions);
if (deployOptionsProp.Name != "ExcludeObjectTypes") // do not compare for ExcludeObjectTypes because it will be different
{
Assert.True((deployOptionsValue == null && dacValue == null) || deployOptionsValue.Equals(dacValue), $"DacFx DacDeploy property not equal to Tools Service DeploymentOptions for { deployOptionsProp.Name}, SchemaCompareOptions value: {deployOptionsValue} and DacDeployOptions value: {dacValue} ");
}
}
}
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
);
}
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");
}
public SSDTExecutor(IConnectionStringBuilder connectionStringBuilder)
{
dacService = new DacServices(connectionStringBuilder.GetConnectioString());
options = new DacDeployOptions()
{
CreateNewDatabase = true, RegisterDataTierApplication = true
};
dacService.Message += statusupdate;
}
/// <summary>Find by dacpac file in directory</summary>
public static void Deploy(string dacPath, string namePattern, string connectionString
, DacDeployOptions options = null
, EventHandler <DacProgressEventArgs> progressChanged = null
, EventHandler <DacMessageEventArgs> messageEvent = null)
{
var package = FindDacPackage(dacPath, namePattern);
package.Deploy(connectionString, options, progressChanged, messageEvent);
}
private static string MainExec(string sourceDacFilePath, string sourceConnectionString, string targerConnectionString, string username, string password, DacDeployOptions options = null, CancellationTokenSource C_Token = null)
{
using (var impersonator = new ImpersonateIt())
{
impersonator.Impersonate(username, password);
//if (!System.IO.File.Exists(sourceDacFilePath))
//{
// Console.WriteLine("source dac file does not exists, Creating new file. ");
// if (string.IsNullOrWhiteSpace(sourceConnectionString))
// {
// Console.Error.WriteLine("Source Connection string is required for creating a bac file.");
// return string.Empty;
// }
//}
Export(sourceConnectionString, @"C:\Temp\Source_dacFile.dacpac");
Export(targerConnectionString, @"C:\Temp\Target_dacFile.dacpac");
var TargetCon = new SqlConnectionStringBuilder(targerConnectionString);
var TargetdacServices = new DacServices(TargetCon.ConnectionString);
TargetdacServices.Message += ((s, e) => { Console.WriteLine(e?.Message.ToString()); });
TargetdacServices.ProgressChanged += ((s, e) => { Console.WriteLine("Status:{0}, Message:{1}", e?.Status, e?.Message.ToString()); });
if (options == null)
{
options = new DacDeployOptions();
}
using (DacPackage dacpac = DacPackage.Load(sourceDacFilePath, DacSchemaModelStorageType.Memory))
{
// Script then deploy, to support debugging of the generated plan
// string script = dacServices.GenerateDeployScript(dacpac, dbName, options);
var deployReport = TargetdacServices.GenerateDeployReport(dacpac, TargetCon.InitialCatalog);
var deployScript = TargetdacServices.GenerateDeployScript(dacpac, TargetCon.InitialCatalog);
var DiffReport = TargetdacServices.GenerateDriftReport(TargetCon.InitialCatalog);
var outReportPath = Path.Combine(@"C:\Temp\", "DeployReport_" + DateTime.Now.ToString("yyyyMMMdd HHmmsstt") + ".sql");
System.IO.File.WriteAllText(outReportPath, deployReport);
var outScriptPath = Path.Combine(@"C:\Temp\", "DeployScript_" + DateTime.Now.ToString("yyyyMMMdd HHmmsstt") + ".sql");
System.IO.File.WriteAllText(outScriptPath, deployScript);
var outDiffReport = Path.Combine(@"C:\Temp\", "DeployDiff_" + DateTime.Now.ToString("yyyyMMMdd HHmmsstt") + ".sql");
System.IO.File.WriteAllText(outDiffReport, DiffReport);
Console.WriteLine("output Report and script generated.");
Console.WriteLine("DeployReport.{0}", deployReport);
Console.WriteLine("DiffReport.{0}", DiffReport);
Console.WriteLine("DeployScript.{0}", deployScript);
return("Done.");
}
}
return("");
}
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);
}
private DacDeployOptions BuildDacDeployOptions()
{
var dacDeployOptions = new DacDeployOptions()
{
BlockOnPossibleDataLoss = false,
CreateNewDatabase = false,
};
return(dacDeployOptions);
}
private static PublishProfile ConvertPublishProfile(DacDeployOptions deployOptions)
{
return(new PublishProfile
{
CreateNewDatabase = deployOptions.CreateNewDatabase,
BackupDatabaseBeforeChanges = deployOptions.BackupDatabaseBeforeChanges,
ScriptDatabaseOptions = deployOptions.ScriptDatabaseOptions,
ScriptDeployStateChecks = deployOptions.ScriptDeployStateChecks
});
}
public void GenerateCreateScript_ShouldNotModifyDeployOptions()
{
// Arrange
var packageName = "MyPackage";
var firstReference = new TestModelBuilder()
.AddTable("MyFirstTable", ("Column1", "nvarchar(100)"))
.SaveAsPackage();
var secondReference = new TestModelBuilder()
.AddTable("MySecondTable", ("Column1", "nvarchar(100)"))
.AddReference(firstReference)
.SaveAsPackage();
var tempFile = new FileInfo(Path.GetTempFileName());
var packageBuilder = new PackageBuilder();
packageBuilder.UsingVersion(SqlServerVersion.Sql150);
packageBuilder.SetMetadata(packageName, "1.0.0.0");
packageBuilder.AddReference(firstReference);
packageBuilder.AddReference(secondReference);
packageBuilder.ValidateModel();
var deployOptions = new DacDeployOptions();
deployOptions.IncludeCompositeObjects = true;
deployOptions.CreateNewDatabase = true;
var expectedCreateScriptFileName = $"{packageName}_Expected_Create.sql";
// Act - Generate expected script
packageBuilder.SaveToDisk(tempFile);
using var package = DacPackage.Load(tempFile.FullName);
using var expectedCreateScriptFile = File.Create(Path.Combine(tempFile.DirectoryName, expectedCreateScriptFileName));
DacServices.GenerateCreateScript(expectedCreateScriptFile, package, packageName, deployOptions);
expectedCreateScriptFile.Close();
// Act - Generate script
packageBuilder.GenerateCreateScript(tempFile, packageName, deployOptions);
// Assert
var expectedScriptContent = File.ReadAllText(Path.Combine(tempFile.DirectoryName, expectedCreateScriptFileName));
expectedScriptContent.ShouldNotBeNullOrEmpty();
var scriptContent = File.ReadAllText(Path.Combine(tempFile.DirectoryName, $"{packageName}_Create.sql"));
scriptContent.ShouldNotBeNullOrEmpty();
expectedScriptContent.ShouldBe(scriptContent);
// Cleanup
tempFile.Delete();
}
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);
}
}
/// <summary>
/// Extracts a <see cref="DacDeployOptions"/> from the <see cref="BuildOptions"/>
/// </summary>
/// <param name="options">The build options</param>
/// <returns>The <see cref="DacDeployOptions"/> object</returns>
public static DacDeployOptions ExtractDeployOptions(this BuildOptions options)
{
var deployOptions = new DacDeployOptions();
if (options.DeployProperty != null)
{
deployOptions.SetDeployProperties(options.DeployProperty);
}
return(deployOptions);
}
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 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 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 static DacDeployOptions SetCreateDatabaseContributorOptions(string mdfFilePath, string ldfFilePath)
{
DacDeployOptions options = new DacDeployOptions();
options.AdditionalDeploymentContributors = DbCreateDatabaseModifier.ContributorId;
options.AdditionalDeploymentContributorArguments =
Utils.BuildContributorArguments(new Dictionary<string, string>()
{
{DbCreateDatabaseModifier.MdfFilePathArg, mdfFilePath},
{DbCreateDatabaseModifier.LdfFilePathArg, ldfFilePath}
});
return options;
}
private static DacDeployOptions SetLocationChangingContributorOptions(string dataFolder, string filePrefix)
{
DacDeployOptions options = new DacDeployOptions();
options.AdditionalDeploymentContributors = DbLocationModifier.ContributorId;
options.AdditionalDeploymentContributorArguments =
Utils.BuildContributorArguments(new Dictionary<string, string>()
{
{DbLocationModifier.DbSaveLocationArg, dataFolder},
{DbLocationModifier.DbFilePrefixArg, filePrefix},
});
return options;
}
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);
}
}
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;
}
}
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
};
using (DacPackage dacpac = DacPackage.Load(_dacpacPath, DacSchemaModelStorageType.Memory))
{
DacServices dacServices = new DacServices(TestUtils.ServerConnectionString);
// Script then deploy, to support debugging of the generated plan
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");
}
}
// Also expect the deployment to fail
AssertDeployFailed(TestUtils.ServerConnectionString, dbName);
}
finally
{
TestUtils.DropDatabase(TestUtils.ServerConnectionString, dbName);
}
}
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");
}
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 void RunTestAndVerifyResult(DacDeployOptions options, Action<string> verifyAction, int currentIteration)
{
// Given database name, and path to save to
string dbName = TestContext.TestName;
// Delete any existing artifacts from a previous run
TestUtils.DropDbAndDeleteFiles(dbName);
// NOTE: Cannot turn on deployment by default in the sample test since we target LocalDB and the "Online" option
// being tested is only supported in enterprise editions. To fully validate that the output of this
// contributor is correct, change the DataSourceName to target an enterprise server and change
// runDeployment to true
bool runDeployment = false;
try
{
string deployScript = GenerateScriptAndOptionallyDeploy(dbName, options, runDeployment, currentIteration);
verifyAction(deployScript);
}
finally
{
if (runDeployment)
{
TestUtils.DropDbAndDeleteFiles(dbName);
}
}
}
private static DacDeployOptions SetCreateIndexContributorOptions(string onlineOption, string maxDopOption)
{
var optionsDictionary = new Dictionary<string, string>();
AddKeyIfValueNotNull(CreateIndexOperationalPropsModifier.OnlineArg, onlineOption, optionsDictionary);
AddKeyIfValueNotNull(CreateIndexOperationalPropsModifier.MaxDopArg, maxDopOption, optionsDictionary);
DacDeployOptions options = new DacDeployOptions();
options.AdditionalDeploymentContributors = CreateIndexOperationalPropsModifier.ContributorId;
options.AdditionalDeploymentContributorArguments = Utils.BuildContributorArguments(optionsDictionary);
return options;
}
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;
}
}
public static bool TryCreateFromDacpac(InstanceInfo instance, string dacpacPath, out SqlTestDB db, out string error, DacDeployOptions deployOptions = null, bool dropDatabaseOnCleanup = false)
{
error = null;
string dbName = string.Empty;
try
{
dbName = Path.GetFileNameWithoutExtension(dacpacPath);
db = SqlTestDB.CreateFromDacpac(instance, dacpacPath, deployOptions, dropDatabaseOnCleanup);
return true;
}
catch (Exception ex)
{
error = ExceptionText.GetText(ex);
db = null;
bool dbCreated = SafeDatabaseExists(instance, dbName);
if (dbCreated)
{
db = new SqlTestDB(instance, dbName, dropDatabaseOnCleanup);
}
return false;
}
}
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.");
}
}
