使用 Microsoft.Build.Evaluation 发布数据库项目 (.sql

Using Microsoft.Build.Evaluation to publish a database project (.sqlproj)(使用 Microsoft.Build.Evaluation 发布数据库项目 (.sqlproj))

问题描述

我需要能够以编程方式发布 SSDT 项目.我正在考虑使用 Microsoft.Build 来执行此操作，但找不到任何文档.创建 .dacpac 似乎很简单，但我将如何发布到现有数据库或至少发布到 .sql 文件.这个想法是让它做当我右键单击项目并选择发布时所做的事情.它应该与选定的数据库进行比较并生成升级脚本.

I need to be able to publish an SSDT project programmatically. I am looking at using Microsoft.Build to do so but can not find any documentation. It seems pretty simple to create the .dacpac, but how would I either publish to an existing database or at the very least to a .sql file. The idea is to have it do what it does when I right click on the project and select publish. It should compare with a selected database and generate an upgrade script.

这是我迄今为止创建的 .dacpac:

This is what I have so far to create the .dacpac:

partial class DBDeploy
{
  Project project;


  internal void publishChanges()
  {
     Console.WriteLine("Building project " + ProjectPath);
     Stopwatch sw = new Stopwatch();
     sw.Start();

     project = ProjectCollection.GlobalProjectCollection.LoadProject(ProjectPath);
     project.Build();
     //at this point the .dacpac is built and put in the debug folder for the project

     sw.Stop();
     Console.WriteLine("Project build Complete.  Total time: {0}", sw.Elapsed.ToString());

  }
}

基本上我正在尝试这样做 MSBuild 示例 显示但在代码中.

Essentially I am trying to do what this MSBuild Example shows but in code.

抱歉，这就是我的全部.Build 类的文档很差.任何帮助将不胜感激.

Sorry that this is all I have. The doecumentation on the Build classes is very poor. Any help would be appreciated.

谢谢.

推荐答案

我不得不做类似的事情，因为我们之前使用的 VSDBCMD 没有部署到 SQL Server 2012，我们需要支持它.我发现的是 Microsoft.SqlServer.Dac 程序集，它似乎是 SQL Server 数据工具的一部分 (http://msdn.microsoft.com/en-us/data/tools.aspx)

I had to do something similar to this because VSDBCMD which we previously used does not deploy to SQL Server 2012 and we needed to support it. What I found was the Microsoft.SqlServer.Dac assembly which seems to come as part of the SQL Server data tools (http://msdn.microsoft.com/en-us/data/tools.aspx)

当您在客户端计算机上运行此程序时，您将需要完整版本的 .NET 4 框架以及 SQL CLR 类型和 SQL T-SQL ScriptDOM 包，可在此处找到:http://www.microsoft.com/en-us/download/details.aspx?id=29065

When you run this on the client machine you will need the full version of the .NET 4 framework and the SQL CLR types and SQL T-SQL ScriptDOM pack found here: http://www.microsoft.com/en-us/download/details.aspx?id=29065

以下代码来自我为测试新部署方法并部署给定 .dacpac 文件而制作的模型

Code below is from a mockup I made for testing the new deployment method and deploys a given .dacpac file

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.SqlServer.Dac;
    using System.IO;

    namespace ConsoleApplication3
    {
        class Program
        {
            private static TextWriter output = new StreamWriter("output.txt", false);
            static void Main(string[] args)
            {

                Console.Write("Connection String:");
                //Class responsible for the deployment. (Connection string supplied by console input for now)
                DacServices dbServices = new DacServices(Console.ReadLine());

                //Wire up events for Deploy messages and for task progress (For less verbose output, don't subscribe to Message Event (handy for debugging perhaps?)
                dbServices.Message += new EventHandler<DacMessageEventArgs>(dbServices_Message);
                dbServices.ProgressChanged += new EventHandler<DacProgressEventArgs>(dbServices_ProgressChanged);


                //This Snapshot should be created by our build process using MSDeploy
                Console.WriteLine("Snapshot Path:");

                DacPackage dbPackage = DacPackage.Load(Console.ReadLine());




                DacDeployOptions dbDeployOptions = new DacDeployOptions();
                //Cut out a lot of options here for configuring deployment, but are all part of DacDeployOptions
                dbDeployOptions.SqlCommandVariableValues.Add("debug", "false");


                dbServices.Deploy(dbPackage, "trunk", true, dbDeployOptions);
                output.Close();

            }

            static void dbServices_Message(object sender, DacMessageEventArgs e)
            {
                output.WriteLine("DAC Message: {0}", e.Message);
            }

            static void dbServices_ProgressChanged(object sender, DacProgressEventArgs e)
            {
                output.WriteLine(e.Status + ": " + e.Message);
            }
        }
    }

这似乎适用于 2005 年及更高版本的所有 SQL Server 版本.Microsoft.SqlServer.Management.Dac 中有一组类似的对象可用，但我相信这是在 DACFx 的先前版本中，并且不包含在最新版本中.因此，如果可以，请使用最新版本.

This seems to work on all versions of SQL Server from 2005 and up. There is a similar set of objects available in Microsoft.SqlServer.Management.Dac, however I believe this is in the previous version of DACFx and is not included in the latest version. So use the latest version if you can.

这篇关于使用 Microsoft.Build.Evaluation 发布数据库项目 (.sqlproj)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！