How can I programmatically publish a SQL Server database project?

Question

Consider the following situation:

Now I have a C # application that parses the file to get the details (tables, columns, etc.) and launches a new SQL connection to execute the SQL command to create these tables in the database.

I want to create an SQL project in which I will manually create these tables, and from a C # application I want to programmatically publish an SQL project on a specific server and database.

Is it possible?

Answer 1

If you are using a project under sqlproj in .NET 4 and above, you can easily and simply create and publish it using classes in the Microsoft.Build namespace. Taken from my answer here :

using Microsoft.Build.Framework; using Microsoft.Build.Execution; public void UpdateSchema() { var props = new Dictionary<string, string> { { "UpdateDatabase", "True" }, { "PublishScriptFileName", "schema-update.sql" }, { "SqlPublishProfilePath", "path/to/publish.xml") } }; var projPath = "path/to/database.sqlproj"; var result = BuildManager.DefaultBuildManager.Build( new BuildParameters { Loggers = new[] { new ConsoleLogger() } }, new BuildRequestData(new ProjectInstance(projPath, props, null), new[] { "Publish" })); if (result.OverallResult == BuildResultCode.Success) { Console.WriteLine("Schema update succeeded!"); } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("Schema update failed!"); Console.ResetColor(); } } private class ConsoleLogger : ILogger { public void Initialize(IEventSource eventSource) { eventSource.ErrorRaised += (sender, e) => { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(e.Message); Console.ResetColor(); }; eventSource.MessageRaised += (sender, e) => { if (e.Importance != MessageImportance.Low) Console.WriteLine(e.Message); }; } public void Shutdown() { } public LoggerVerbosity Verbosity { get; set; } public string Parameters { get; set; } } 

This is for .NET 4 and above. Be sure to include links to Microsoft.Build and Microsoft.Build.Framework .

Answer 2

There are several ways to do this. In our application (~ 7 large databases) we manage them all with the databases from SQL Server Data Tools . This allowed us to easily manage versions, as well as use some amazing comparison tools during deployment and many other options. We have expanded our capabilities to deal with some nuances in our environment, but for most people who should not be a problem.

Part of this toolkit includes DAC (data-tier applications) that allow you to easily transform the database in your project into various environments. This will support the vast majority of projects that exist today.

If you want to upgrade to a clean program, you can use Code First (and Code First Migrations , which is pretty smooth), which is kind of like you go, and MS will determine the rest for you (mostly by convention, but the flexibility to go beyond scope of this). This is very convenient when it comes to version upgrades. Again IMHO.

Database projects also exist, but usually require a bit more understanding / work in order to get them to fit the way you want (but also offer a familiar layout like SQL Explorer).