May I know if there is any library in .Net that can allow me to pass in 2 connection string and do Schema Compare and update?
The reason I need this is because I maintain a golden database which deploy to multiple databases for different customers. If I need to do the schema compare manually from VS every time there is DB update will consume long time.
I plan to loop thru all the clients connection string and compare to the golden database and automatically update them.
Please advise, thanks.
This is what I have done. A very simple solution. The nuget library needed are listed as below, search and include them in the project.
<package id="Microsoft.SqlServer.Dac" version="1.0.3" targetFramework="net45" />
<package id="Microsoft.SqlServer.DacFx.x86" version="130.3485.1" targetFramework="net45" />
Below is the sample code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using Microsoft.SqlServer.Dac.Compare;
namespace SchemaComparer
{
class Program
{
//The directory where all the scmp files are located
const string schemaDirectory = "C:\SchemaCompare\\";
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();
}
}
}
Edited on 3/Nov/2017 I probably forget to mention that in order for this to works, you'll have to create the scmp files and store it in the directory, in my case "C:\SchemaCompare\". The program will pick up all the scmp files and do the compare and update. So if you have additional database needed to compare in the future, just create the scmp and keep in the directory.
Devart has some schema comparison tools that support command line automation and might be able to do what you need: https://www.devart.com/dbforge/sql/schemacompare/
