How can I programatically publish a SQL Server Database Project?

Question

Consider the following situation:

Now, I have a C# application that parses a file in order to get details (tables, columns etc) and starts a new SQL Connection in order to execute a SQL Command to create those tables in the database.

What I want is to create a SQL Project in which I will manually create those tables, and from the C# application I want to programatically publish the SQL project to a certain server and database.

Is this possible ?

score 8 · Accepted Answer · edited May 23 '17 at 12:32

If you are using a sqlproj based project in .NET 4 and above, you can build and publish it programatically fairly easily 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 and include assembly references to Microsoft.Build and Microsoft.Build.Framework.

For other people reading this, I found this documentation for the publishtask properties: https://msdn.microsoft.com/en-us/library/microsoft.data.tools.schema.tasks.sql.sqlpublishtask(v=vs.103).aspx — grmbl, Oct 26 '16 at 18:46
@Todd Menier is it possible to do sth. similar in .net core? — GoldenAge, Jun 01 '21 at 19:16

score 1 · Answer 2 · answered Dec 12 '12 at 16:28

1

There are a number of ways you could accomplish this. In our app (~ 7 large databases) we manage them all with Database Projects from SQL Server Data Tools. This has allowed us to version control easily as well as do some awesome comparison tools at deploy time and a plethora of other options. We did expand ours to deal with some nuances in our environment but for most people that shouldn't be an issue.

Part of that toolset includes DAC (Data Tier Applications) which allow you to transplant a database that is in your project to various environments pretty easily. This would support a great majority of projects in existence today.

If you wanted to go pure programatic you could use Code First (and Code First Migrations which is pretty slick) which is kind of build as you go and MS will figure out the rest for you (mainly by convention but flexibility to go beyond that). It's really friendly when it comes to upgrading versions. Again IMHO.

Database Projects exist as well but tend to require a little more insight/work to get them tweaked the way you want (but also offer a familar SQL Explorer type layout).

answered Dec 12 '12 at 16:28

Chris

731
1
7
18

Thanks for your response, but i am looking for a strict answer, regarding the publishing of the SQL Server Database Project programatically. If you know more details, please share them. – Cristi Pufu Dec 12 '12 at 19:03
That question is a bit vague. A lot of these use MsBuild or Powershell scripts to perform actions, both which are programming. You can access the MsBuild library directly and inside your own code execute the necessary tasks if you so wish. Code First is *purely* code -> SQL deployment. But this isn't a database project or DAC. DAC offers a number of tools that can be programatically called with either via command line or direct API. Please take a look at the first link, the sub sections likely will answer your questions in more detail as to what commands/applications to call. – Chris Dec 12 '12 at 21:09
Code first is great.. for new projects :) I have a DB with all sorts of stuff built with several dev's. Now I need to copy that DB, and deploy it to other servers so that it can be white labelled. It would be great a Database project can do this programatically for me.I suspect that is what the OP was after too. – Piotr Kula Feb 27 '15 at 10:41

How can I programatically publish a SQL Server Database Project?

2 Answers2