public SchemaCompareWrapper(string dacPacFileLocation, string enpointConnectionString)
{
_dacPacFileLocation = dacPacFileLocation;
_enpointConnectionString = enpointConnectionString;
_sourceDacPac = new SchemaCompareDacpacEndpoint(dacPacFileLocation);
_enpointDb = new SchemaCompareDatabaseEndpoint(enpointConnectionString);
}
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();
}
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.");
}
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 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);
}
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 =
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"));
}
