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 ?

Answer 1

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.

Answer 2

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).