public void ExtractDacpac(string filePath, IEnumerable<Tuple<string, string>> tables = null, DacExtractOptions extractOptions = null)
{
DacServices ds = new DacServices(this.BuildConnectionString());
ds.Extract(filePath, this.DatabaseName, this.DatabaseName, new Version(1, 0, 0), string.Empty, tables, extractOptions);
}
private static DacPackage Extract(string serverName, string databaseName, FileInfo packageFile, string label)
{
SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder
{
DataSource = serverName,
InitialCatalog = databaseName,
IntegratedSecurity = true
};
DacServices dacServices = new DacServices(connectionStringBuilder.ConnectionString);
dacServices.ProgressChanged += (s, e) =>
{
if (e.Status == DacOperationStatus.Running)
{
Console.WriteLine("{0} ({1})", e.Message, label);
}
};
DacExtractOptions extractOptions = new DacExtractOptions
{
IgnorePermissions = false,
IgnoreUserLoginMappings = true,
Storage = DacSchemaModelStorageType.Memory
};
// Ensure the package file directory exists.
packageFile.Directory.Create();
dacServices.Extract(packageFile.FullName, databaseName, databaseName, new Version(1, 0), extractOptions: extractOptions);
return(DacPackage.Load(packageFile.FullName, DacSchemaModelStorageType.Memory));
}
public static bool Export(string sourceDbConString, string outDacFilePath)
{
bool result = false;
var sourceConBuilder = new SqlConnectionStringBuilder(sourceDbConString);
var services = new DacServices(sourceConBuilder.ConnectionString);
services.ProgressChanged += ((s, e) => { Console.WriteLine("Ëxporting Dacpack Status:{0} , Message:{1}.", e.Status, e.Message); });
string blobName;
if (System.IO.File.Exists(outDacFilePath))
{
System.IO.File.Delete(outDacFilePath);
}
using (FileStream stream = File.Open(outDacFilePath, FileMode.Create, FileAccess.ReadWrite))
{
Console.WriteLine("starting bacpac export");
DacExportOptions opts = new DacExportOptions()
{
TargetEngineVersion = EngineVersion.Default,
Storage = DacSchemaModelStorageType.Memory,
VerifyFullTextDocumentTypesSupported = false
};
services.Extract(packageStream: stream, databaseName: sourceConBuilder.InitialCatalog, applicationName: "Schema_Exporter", applicationVersion: Version.Parse("1.0.0.0"));
//services.ExportBacpac(stream, sourceConBuilder.InitialCatalog, options:opts,tables:null);
stream.Flush();
return(true);
}
return(result);
}
/// <summary>
/// Clone an existing database schema and deploy.
/// </summary>
/// <param name="sourceDatabase">Source database connection string.</param>
/// <param name="databaseName">Database name.</param>
/// <exception cref="ArgumentException">
/// <paramref name="sourceDatabase"/> is <c>null</c> or <see cref="string.Empty"/>
/// or
/// <paramref name="databaseName"/> is <c>null</c> or <see cref="string.Empty"/>
/// </exception>
/// <returns>Database connection string.</returns>
public string CloneDatabase(string sourceDatabase, string databaseName)
{
if (string.IsNullOrEmpty(sourceDatabase))
{
throw new ArgumentException("The source database cannot be null or empty.", nameof(sourceDatabase));
}
if (string.IsNullOrEmpty(databaseName))
{
throw new ArgumentException("The database name cannot be null or empty.", nameof(databaseName));
}
lock (_sync)
{
using (var stream = new MemoryStream())
{
var extractService = new DacServices(sourceDatabase);
extractService.Extract(stream, databaseName, "SqlServer.Test",
Version.Parse("0.0.1"), "SqlServer.Test", null, DacpacOptions.Extract);
Manager.DeployAsync(Settings, stream, _serverConnectionString, databaseName)
.Wait();
}
_databases.Add(databaseName);
return CreateDatabaseConnectionString(databaseName);
}
}
private byte[] GenerateDacPac(string databaseName)
{
using (var stream = new MemoryStream())
{
dacServices.Extract(stream, databaseName, applicationName, version);
stream.Seek(0, SeekOrigin.Begin);
return(stream.ToArray());
}
}
public void RunAnalysisAgainstDatabase(string Server,string Database,string OutFile)
{
string extractedPackagePath = System.IO.Path.GetTempPath()+System.IO.Path.GetRandomFileName() + ".dacpac";
DacServices services = new DacServices("Server="+Server+";Integrated Security=true;");
services.Extract(extractedPackagePath, Database, "AppName", new Version(1, 0));
RunDacpacAnalysis(extractedPackagePath,OutFile);
}
internal static void ExtractDacpacFile(ICakeContext context, string connectionString, string targetDatabaseName, ExtractDacpacSettings settings)
{
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}");
}
public void RunAnalysisAgainstDatabase(string Server, string Database, string OutFile)
{
string extractedPackagePath = System.IO.Path.GetTempPath() + System.IO.Path.GetRandomFileName() + ".dacpac";
DacServices services = new DacServices("Server=" + Server + ";Integrated Security=true;");
services.Extract(extractedPackagePath, Database, "AppName", new Version(1, 0));
RunDacpacAnalysis(extractedPackagePath, OutFile);
}
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 CreateOrReplaceDacPac()
{
if (File.Exists(_dacpacPath))
{
// When a .dacpac is regenerated there is a generated id and timestamp for the operation, which causes a git commit even if nothing
// else has changed. To prevent that, only replace the dacpac if the actual model inside it has changed.
var tempDacpacPath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString() + ".dacpac");
_dacServices.Extract(tempDacpacPath, _umbracoDatabasePath, _databaseName, new Version(1, 0, 0));
var checksumBefore = ReadDacpacModelChecksum(_dacpacPath);
var checksumAfter = ReadDacpacModelChecksum(tempDacpacPath);
if (checksumAfter != checksumBefore)
{
File.Copy(tempDacpacPath, _dacpacPath, true);
}
File.Delete(tempDacpacPath);
}
else
{
_dacServices.Extract(_dacpacPath, _umbracoDatabasePath, _databaseName, new Version(1, 0, 0));
}
}
/// <summary>
/// Currently there is supported method for creating a TSqlModel by targeting a database. However extracting
/// the database to a dacpac is supported, and this is the best way to do analysis against that database.
/// Note that
/// </summary>
private static void RunAnalysisAgainstDatabase(string productionPackagePath, string resultsFilePath)
{
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", true);
Console.WriteLine("Extracting the 'ProductionDB' back to a dacpac for comparison");
services.Extract(extractedPackagePath, "ProductionDB", "AppName", new Version(1, 0));
}
RunDacpacAnalysis(extractedPackagePath, resultsFilePath);
}
private static void CreateDatabase()
{
try
{
// Create DacDeployOptions
DacDeployOptions options = new DacDeployOptions();
options.CreateNewDatabase = true;
// Specify Connection string for Source DB
string sourceConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
// Create DacServices to access Source DB
DacServices sourceDacServices = new DacServices(sourceConnectionString);
// Create memory object to hold the DACPAC package vs saving to file system
Stream dacPackage = new MemoryStream();
// Extract database into DACPAC memory object
//NOTE: The second "EventRegistration" is the application name. I'm not sure how this is really used in the creation of the DACPAC. Same with the version.
sourceDacServices.Extract(dacPackage, "EventRegistration", "EventRegistration", new Version("1.0.0.0"));
// Load DACPAC memory object into an actual DACPAC typed object
DacPackage dacPac = DacPackage.Load(dacPackage);
// This version will create a NEW database with a new name, provided in quotes, on the same DB Server as the Source DB because we are using the same DacServices instance.
sourceDacServices.Deploy(dacPac, "EventRegistration2");
// This will give you the text version of the script in the event you want to save the script to a file and run the changes manually or have a written record of the changes that occurred.
//string script = DacServices.GenerateCreateScript(x, "EventRegistration2", null);
/*************** I have provided the below as an example only. I haven't been able to test this yet. ********************/
// To update an existing database you will need to create a new DacServices instance with the Destination Connection String
string destConnectionString = "";
DacServices destDacServices = new DacServices(destConnectionString);
// Deploy to destination
destDacServices.Deploy(dacPac, "EventRegistration", true, options);
/*********************************************/
Console.WriteLine("DAC Created");
}
catch (Exception ex)
{
}
}
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 static bool ExtractDacPac(string sourceDatabase, string sourceServer, AuthenticationType authType, string userName, string password, string dacPacFileName)
{
try
{
log.LogInformation($"Extracting dacpac from {sourceServer} : {sourceDatabase}");
DacExtractOptions opts = new DacExtractOptions();
opts.IgnoreExtendedProperties = true;
opts.IgnoreUserLoginMappings = true;
SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder();
if (authType == AuthenticationType.Windows)
{
connBuilder.IntegratedSecurity = true;
}
if (!string.IsNullOrWhiteSpace(userName))
{
connBuilder.UserID = userName;
}
if (!string.IsNullOrWhiteSpace(password))
{
connBuilder.Password = password;
}
connBuilder.DataSource = sourceServer;
connBuilder.InitialCatalog = sourceDatabase;
Version ver = Assembly.GetExecutingAssembly().GetName().Version;
DacServices service = new DacServices(connBuilder.ConnectionString);
service.Extract(dacPacFileName, sourceDatabase, "Sql Build Manager", ver);
log.LogInformation($"dacpac from {sourceServer}.{sourceDatabase} saved to {dacPacFileName}");
return(true);
}
catch (Exception exe)
{
log.LogError($"Problem creating DACPAC from {sourceServer}.{sourceDatabase}: {exe.ToString()}");
return(false);
}
}
public void ExtractDacpac(string filePath, IEnumerable <Tuple <string, string> > tables = null, DacExtractOptions extractOptions = null)
{
DacServices ds = new DacServices(this.BuildConnectionString());
ds.Extract(filePath, this.DatabaseName, this.DatabaseName, new Version(1, 0, 0), string.Empty, tables, extractOptions);
}
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 Extract(DacServices svc, string SourceDatabaseName, string Path)
{
Console.WriteLine("\n\rPerforming Extract of {0} to {1} at {2}", SourceDatabaseName, Path, System.DateTime.Now.ToLongTimeString());
DacExtractOptions dacExtractOptions = new DacExtractOptions
{
ExtractApplicationScopedObjectsOnly = true,
ExtractReferencedServerScopedElements = false,
VerifyExtraction = true,
Storage = DacSchemaModelStorageType.Memory
};
svc.Extract(Path, SourceDatabaseName, "Sample DACPAC", new Version(1, 0, 0), "Sample Extract", null, dacExtractOptions);
}
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);
}
}
