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")); }