public void Execute(TaskExecutionMode mode)
{
if (this.CancellationToken.IsCancellationRequested)
{
throw new OperationCanceledException(this.CancellationToken);
}
try
{
SchemaCompareEndpoint sourceEndpoint = SchemaCompareUtils.CreateSchemaCompareEndpoint(this.Parameters.SourceEndpointInfo, this.SourceConnectionInfo);
SchemaCompareEndpoint targetEndpoint = SchemaCompareUtils.CreateSchemaCompareEndpoint(this.Parameters.TargetEndpointInfo, this.TargetConnectionInfo);
SchemaComparison comparison = new SchemaComparison(sourceEndpoint, targetEndpoint);
if (this.Parameters.DeploymentOptions != null)
{
comparison.Options = SchemaCompareUtils.CreateSchemaCompareOptions(this.Parameters.DeploymentOptions);
}
// for testing
schemaCompareStarted?.Invoke(this, new EventArgs());
this.ComparisonResult = comparison.Compare(this.CancellationToken);
// try one more time if it didn't work the first time
if (!this.ComparisonResult.IsValid)
{
this.ComparisonResult = comparison.Compare(this.CancellationToken);
}
// Since DacFx does not throw on schema comparison cancellation, throwing here explicitly to ensure consistency of behavior
if (!this.ComparisonResult.IsValid && this.CancellationToken.IsCancellationRequested)
{
throw new OperationCanceledException(this.CancellationToken);
}
this.Differences = new List <DiffEntry>();
if (this.ComparisonResult.Differences != null)
{
foreach (SchemaDifference difference in this.ComparisonResult.Differences)
{
DiffEntry diffEntry = SchemaCompareUtils.CreateDiffEntry(difference, null);
this.Differences.Add(diffEntry);
}
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
Logger.Write(TraceEventType.Error, string.Format("Schema compare operation {0} failed with exception {1}", this.OperationId, e.Message));
throw;
}
}
private bool CompareDatabaseSchemas(string databaseName1, string databaseName2)
{
var initialConnectionString = Environment.GetEnvironmentVariable("SqlServer:ConnectionString") ?? LocalConnectionString;
var testConnectionString1 = new SqlConnectionStringBuilder(initialConnectionString)
{
InitialCatalog = databaseName1
}.ToString();
var testConnectionString2 = new SqlConnectionStringBuilder(initialConnectionString)
{
InitialCatalog = databaseName2
}.ToString();
var source = new SchemaCompareDatabaseEndpoint(testConnectionString1);
var target = new SchemaCompareDatabaseEndpoint(testConnectionString2);
var comparison = new SchemaComparison(source, target)
{
Options = { IgnoreWhitespace = true, IgnoreComments = true },
};
SchemaComparisonResult result = comparison.Compare();
// These types were introduced in earlier schema versions but are no longer used in newer versions.
// They are not removed so as to no break compatibility with instances requiring an older schema version.
// Exclude them from the schema comparison differences.
(string type, string name)[] deprecatedObjectToIgnore =
public async Task GivenTwoSchemaInitializationMethods_WhenCreatingTwoDatabases_BothSchemasShouldBeEquivalent()
{
// Create two databases, one where we apply the the maximum supported version's snapshot SQL schema file
SqlDataStoreTestsFixture snapshotFixture = new SqlDataStoreTestsFixture(SqlDataStoreTestsFixture.GenerateDatabaseName("SNAPSHOT"));
// And one where we apply .diff.sql files to upgrade the schema version to the maximum supported version.
SqlDataStoreTestsFixture diffFixture = new SqlDataStoreTestsFixture(SqlDataStoreTestsFixture.GenerateDatabaseName("DIFF"));
await snapshotFixture.InitializeAsync(forceIncrementalSchemaUpgrade : false);
await diffFixture.InitializeAsync(forceIncrementalSchemaUpgrade : true);
SchemaCompareDatabaseEndpoint snapshotEndpoint = new SchemaCompareDatabaseEndpoint(snapshotFixture.TestConnectionString);
SchemaCompareDatabaseEndpoint diffEndpoint = new SchemaCompareDatabaseEndpoint(diffFixture.TestConnectionString);
var comparison = new SchemaComparison(snapshotEndpoint, diffEndpoint);
SchemaComparisonResult result = comparison.Compare();
Assert.True(result.IsEqual);
// cleanup if succeeds
await snapshotFixture.DisposeAsync();
await diffFixture.DisposeAsync();
}
static void Main(string[] args)
{
//Loop thru all the scmp from the directory. This set to max 2 thread that run parallel and update together
Parallel.ForEach(Directory.GetFiles(schemaDirectory), new ParallelOptions {
MaxDegreeOfParallelism = 2
}, (file) =>
{
try
{
// Load comparison from Schema Compare (.scmp) file
var comparison = new SchemaComparison(file);
Console.WriteLine("Processing " + Path.GetFileName(file));
Console.WriteLine("Comparing schema...");
SchemaComparisonResult comparisonResult = comparison.Compare();
// Publish the changes to the target database
Console.WriteLine("Publishing schema...");
SchemaComparePublishResult publishResult = comparisonResult.PublishChangesToTarget();
Console.WriteLine(publishResult.Success ? "Publish succeeded." : "Publish failed.");
Console.WriteLine(" ");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
});
Console.ReadLine();
}
public void ScmFileBasedCompare(string srcConString, string targetConString)
{
var src = new SqlConnectionStringBuilder(srcConString);
var bacPacFile = GetDacFileName(srcConString) + ".dacpac";
var dtVersion = DateTime.Now.ToString("yyyy.MM.dd.HHmmss");
//if Bac file not exists create new
var dacService = new Microsoft.SqlServer.Dac.DacServices(srcConString);
dacService.Extract(bacPacFile, src.InitialCatalog, "Test Application", Version.Parse(dtVersion));
var sourceDacpac = new SchemaCompareDacpacEndpoint(bacPacFile);
var target = new SqlConnectionStringBuilder(targetConString);
var targetDatabase = new SchemaCompareDatabaseEndpoint(targetConString);
var comparison = new SchemaComparison(sourceDacpac, targetDatabase);
// Persist comparison file to disk in Schema Compare (.scmp) format
comparison.SaveToFile(@"C:\temp\mycomparison.scmp");
// Load comparison from Schema Compare (.scmp) file
comparison = new SchemaComparison(@"C:\temp\mycomparison.scmp");
SchemaComparisonResult comparisonResult = comparison.Compare();
foreach (var d in comparisonResult.Differences)
{
Console.WriteLine(d.SourceObject.GetScript());
}
// Find the change to table1 and exclude it.
//foreach (SchemaDifference difference in comparisonResult.Differences)
//{
// if (difference.TargetObject.Name != null &&
// difference.TargetObject.Name.HasName &&
// difference.TargetObject.Name.Parts[1] == "DbConnections")
// {
// comparisonResult.Exclude(difference);
// break;
// }
//}
// Publish the changes to the target database
//SchemaComparePublishResult publishResult = comparisonResult.PublishChangesToTarget();
var publishResult = comparisonResult.GenerateScript(".");
Console.WriteLine(publishResult.MasterScript);
Console.WriteLine(publishResult.Script);
Console.WriteLine(publishResult.Success ? "Publish succeeded." : "Publish failed.");
}
public void Execute(TaskExecutionMode mode)
{
if (this.CancellationToken.IsCancellationRequested)
{
throw new OperationCanceledException(this.CancellationToken);
}
try
{
SchemaCompareEndpoint sourceEndpoint = CreateSchemaCompareEndpoint(this.Parameters.SourceEndpointInfo, this.SourceConnectionString);
SchemaCompareEndpoint targetEndpoint = CreateSchemaCompareEndpoint(this.Parameters.TargetEndpointInfo, this.TargetConnectionString);
SchemaComparison comparison = new SchemaComparison(sourceEndpoint, targetEndpoint);
if (this.Parameters.DeploymentOptions != null)
{
comparison.Options = this.CreateSchemaCompareOptions(this.Parameters.DeploymentOptions);
}
this.ComparisonResult = comparison.Compare();
// try one more time if it didn't work the first time
if (!this.ComparisonResult.IsValid)
{
this.ComparisonResult = comparison.Compare();
}
this.Differences = new List <DiffEntry>();
foreach (SchemaDifference difference in this.ComparisonResult.Differences)
{
DiffEntry diffEntry = CreateDiffEntry(difference, null);
this.Differences.Add(diffEntry);
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
Logger.Write(TraceEventType.Error, string.Format("Schema compare operation {0} failed with exception {1}", this.OperationId, e.Message));
throw;
}
}
static void Main(string[] args)
{
var target = new SchemaCompareDatabaseEndpoint(@"Data Source=localhost;Initial Catalog=Evolution;Integrated Security=True;Connect Timeout=30");
var source = new SchemaCompareDatabaseEndpoint(@"Data Source=localhost;Initial Catalog=qaDrmDevTrunk;Integrated Security=True;Connect Timeout=30");
var compare = new SchemaComparison(source, target);
var compRes = compare.Compare();
var script = compRes.GenerateScript("Evolution");
Console.Read();
}
//#r "C:\Program Files\Microsoft SQL Server\140\DAC\bin\Microsoft.SqlServer.Dac.Extensions.dll"
public void Compare()
{
var csb = new SqlConnectionStringBuilder
{
DataSource = ".",
InitialCatalog = "ICareMVCMaster",
IntegratedSecurity = true
};
var bacPacFile = @"C:\temp\icaremvcmaster.dacpac";
var s = new Microsoft.SqlServer.Dac.DacServices(csb.ConnectionString);
s.Extract(bacPacFile, "IcareMVCMaster", "Test Application", new Version("1.1.1.1"));
var sourceDacpac = new SchemaCompareDacpacEndpoint(bacPacFile);
var csb2 = new SqlConnectionStringBuilder
{
DataSource = ".",
InitialCatalog = "New_ICareMVCMaster",
IntegratedSecurity = true
};
var targetDatabase = new SchemaCompareDatabaseEndpoint(csb2.ToString());
var comparison = new SchemaComparison(sourceDacpac, targetDatabase);
// Persist comparison file to disk in Schema Compare (.scmp) format
comparison.SaveToFile(@"C:\temp\mycomparison.scmp");
// Load comparison from Schema Compare (.scmp) file
comparison = new SchemaComparison(@"C:\temp\mycomparison.scmp");
SchemaComparisonResult comparisonResult = comparison.Compare();
// Find the change to table1 and exclude it.
//foreach (SchemaDifference difference in comparisonResult.Differences)
//{
// if (difference.TargetObject.Name != null &&
// difference.TargetObject.Name.HasName &&
// difference.TargetObject.Name.Parts[1] == "DbConnections")
// {
// comparisonResult.Exclude(difference);
// break;
// }
//}
// Publish the changes to the target database
//SchemaComparePublishResult publishResult = comparisonResult.PublishChangesToTarget();
var publishResult = comparisonResult.GenerateScript(".");
Console.WriteLine(publishResult.MasterScript);
Console.WriteLine(publishResult.Script);
Console.WriteLine(publishResult.Success ? "Publish succeeded." : "Publish failed.");
}
public async Task GivenTwoSchemaInitializationMethods_WhenCreatingTwoDatabases_BothSchemasShouldBeEquivalent()
{
// Create two databases, one where we apply the the maximum supported version's snapshot SQL schema file
SqlDataStoreTestsFixture snapshotFixture = new SqlDataStoreTestsFixture(SqlDataStoreTestsFixture.GenerateDatabaseName("SNAPSHOT"));
// And one where we apply .diff.sql files to upgrade the schema version to the maximum supported version.
SqlDataStoreTestsFixture diffFixture = new SqlDataStoreTestsFixture(SqlDataStoreTestsFixture.GenerateDatabaseName("DIFF"));
await snapshotFixture.InitializeAsync(forceIncrementalSchemaUpgrade : false);
await diffFixture.InitializeAsync(forceIncrementalSchemaUpgrade : true);
SchemaCompareDatabaseEndpoint snapshotEndpoint = new SchemaCompareDatabaseEndpoint(snapshotFixture.TestConnectionString);
SchemaCompareDatabaseEndpoint diffEndpoint = new SchemaCompareDatabaseEndpoint(diffFixture.TestConnectionString);
var comparison = new SchemaComparison(snapshotEndpoint, diffEndpoint);
SchemaComparisonResult result = comparison.Compare();
// filter our sproc bodyscript differences because of auto-generation
var actualDiffs = new List <SchemaDifference>();
if (!result.IsEqual)
{
foreach (var diff in result.Differences)
{
if (diff.Name == "SqlProcedure")
{
foreach (var childDiff in diff.Children)
{
if (childDiff.Name != "BodyScript")
{
actualDiffs.Add(diff);
break;
}
}
}
else
{
actualDiffs.Add(diff);
}
}
}
Assert.Empty(actualDiffs);
// cleanup if succeeds
await snapshotFixture.DisposeAsync();
await diffFixture.DisposeAsync();
}
public SchemaComparisonResult Compare(string compareFileLocation = null)
{
var compare = new SchemaComparison(_sourceDacPac, _enpointDb);
compare.Options.BlockOnPossibleDataLoss = false;
if (compareFileLocation != null)
{
compare.SaveToFile(compareFileLocation);
}
compare = new SchemaComparison(compareFileLocation);
_compareResult = compare.Compare();
return(_compareResult);
}
public bool CompareDatabaseSchemas(string databaseName1, string databaseName2)
{
var testConnectionString1 = new SqlConnectionStringBuilder(_initialConnectionString)
{
InitialCatalog = databaseName1
}.ToString();
var testConnectionString2 = new SqlConnectionStringBuilder(_initialConnectionString)
{
InitialCatalog = databaseName2
}.ToString();
var source = new SchemaCompareDatabaseEndpoint(testConnectionString1);
var target = new SchemaCompareDatabaseEndpoint(testConnectionString2);
var comparison = new SchemaComparison(source, target);
SchemaComparisonResult result = comparison.Compare();
return(result.IsEqual);
}
public void Compare()
{
var sourceConn = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Identity;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
var targetConn = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=AspDataDb;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
//string sourceDacFile = "";
//string targetDacFile = "";
string TargetDataBaseName = "";
SourceEndPoint = GetEndPoint(sourceConn);
TargetEndPoint = GetEndPoint(targetConn);
var comparison = new SchemaComparison(SourceEndPoint, TargetEndPoint);
var result = comparison.Compare();
var differences = result.GenerateScript(TargetDataBaseName);
var changeScript = differences.Script;
}
private void btn_Apply_Click(object sender, RoutedEventArgs e)
{
MessageBox.Show(LaneDB);
MessageBox.Show(StationDB);
try
{
var source = new SchemaCompareDatabaseEndpoint(LaneDB);
var target = new SchemaCompareDatabaseEndpoint(StationDB);
var comparison = new SchemaComparison(source, target);
var result = comparison.Compare();
var differences = result.GenerateScript("ITD_SyncMonitor");
string script = differences.Script;
}
catch
{
MessageBox.Show("Lỗi");
}
}
public bool CompareDatabaseSchemas(string databaseName1, string databaseName2)
{
var testConnectionString1 = new SqlConnectionStringBuilder(_initialConnectionString)
{
InitialCatalog = databaseName1
}.ToString();
var testConnectionString2 = new SqlConnectionStringBuilder(_initialConnectionString)
{
InitialCatalog = databaseName2
}.ToString();
var source = new SchemaCompareDatabaseEndpoint(testConnectionString1);
var target = new SchemaCompareDatabaseEndpoint(testConnectionString2);
var comparison = new SchemaComparison(source, target);
SchemaComparisonResult result = comparison.Compare();
// These types were introduced in earlier schema versions but are no longer used in newer versions.
// They are not removed so as to no break compatibility with instances requiring an older schema version.
// Exclude them from the schema comparison differences.
(string type, string name)[] deprecatedObjectToIgnore =
public SchemaComparisonResult Compare()
{
SchemaComparisonResult = SchemaComparison.Compare();
return(SchemaComparisonResult);
}
private void Start()
{
var src = new SqlConnectionStringBuilder(this.SourceConString);
var target = new SqlConnectionStringBuilder(this.TargetConString);
//if Bac file not exists create new
//var bacPacFile = Path.Combine(GetWorkingFolder(), src.InitialCatalog + ".dacpac");
//var dacService = new Microsoft.SqlServer.Dac.DacServices(this.SourceConString);
//dacService.Extract(bacPacFile, src.InitialCatalog, "Test Application", Version.Parse(AppVersion));
//var sourceDacpac = new SchemaCompareDacpacEndpoint(bacPacFile);
Console.WriteLine("Creating Database end point connections.");
var sourceEndPoint = new SchemaCompareDatabaseEndpoint(this.SourceConString);
var targetEndPoint = new SchemaCompareDatabaseEndpoint(this.TargetConString);
Console.WriteLine("Initializing Schema Comparision with given endpoints");
var comparison = new SchemaComparison(sourceEndPoint, targetEndPoint);
//// Persist comparison file to disk in Schema Compare (.scmp) format
//var scmpFile = GetDacFileName(src.ConnectionString) + ".scmp";
//comparison.SaveToFile(scmpFile);
//// Load comparison from Schema Compare (.scmp) file
//comparison = new SchemaComparison(scmpFile);
Console.WriteLine("Schema Comparision Setting Options...");
comparison.Options = new Microsoft.SqlServer.Dac.DacDeployOptions()
{
};
if (Program.CommandArgs.ContainsKey("excludedTypes"))
{
var stringSplitter = new string[] { ",", ";", "|" };
var excludedTypes = new List <Microsoft.SqlServer.Dac.ObjectType>();
foreach (var type in Program.CommandArgs["excludedTypes"].ToString().Split(stringSplitter, StringSplitOptions.RemoveEmptyEntries))
{
Microsoft.SqlServer.Dac.ObjectType outType;
if (Enum.TryParse(type, out outType))
{
Console.WriteLine(" Adding excluded type {0}", outType.ToString());
excludedTypes.Add(outType);
}
}
comparison.Options.ExcludeObjectTypes = excludedTypes.ToArray();
}
Console.WriteLine("Schema Comparision Starting...");
//Set Options
SchemaComparisonResult comparisonResult = comparison.Compare();
Console.WriteLine("Schema Comparision Compated.");
var sourceFolder = GetSourceWorkingFolder(this.SourceConString);
var targetFolder = GetSourceWorkingFolder(this.TargetConString);
Console.WriteLine("Creating diff object scripts...");
foreach (var d in comparisonResult.Differences)
{
string objectName = string.Join(".", d.SourceObject?.Name.Parts ?? d.TargetObject.Name.Parts);
string objType = d.SourceObject?.ObjectType?.Name ?? d.TargetObject?.ObjectType?.Name;
if (string.IsNullOrWhiteSpace(objectName))
{
Console.WriteLine("Object Name is empty {0}", objectName);
continue;
}
WriteToFile(sourceFolder, objType, objectName, d.SourceObject?.ToString());
WriteToFile(targetFolder, objType, objectName, d.TargetObject?.GetScript());
}
// Find the change to table1 and exclude it.
//foreach (SchemaDifference difference in comparisonResult.Differences)
//{
// if (difference.TargetObject.Name != null &&
// difference.TargetObject.Name.HasName &&
// difference.TargetObject.Name.Parts[1] == "DbConnections")
// {
// comparisonResult.Exclude(difference);
// break;
// }
//}
// Publish the changes to the target database
//SchemaComparePublishResult publishResult = comparisonResult.PublishChangesToTarget();
var publishResult = comparisonResult.GenerateScript(target.InitialCatalog);
WriteToFile(GetWorkingFolder(), "", "MasterChangeScript.sql", publishResult.MasterScript);
WriteToFile(GetWorkingFolder(), "", "ChangeScript.sql", publishResult.Script);
Console.WriteLine(publishResult.Success ? "Publish succeeded." : "Publish failed.");
System.IO.Compression.ZipFile.CreateFromDirectory(GetWorkingFolder(), Path.Combine(_basePath, "Artifact" + DateTime.Now.ToString("ddMMMyyHHmmss") + ".zip"));
}
