如何通过代码发布 SSDT 数据库

We have SQLServer01.Publish.xml当我双击该文件并发布时，它将数据库发布到 sqlServer01。

我想问我们可以通过代码以某种方式发布此配置文件吗？

SSDT 似乎很喜欢 DacPac 来做这种事情。有一个DacServices中的实用程序类Microsoft.SqlServer.Dac。我认为这需要在您计划运行此代码的计算机上安装 SSDT。

public class DacPacUtility
{
    public void DeployDacPac( string connString, string dacpacPath, string targetDbName )
    {
        var dbServices = new DacServices( connString );

        var dbPackage = DacPackage.Load( new FileStream( dacpacPath, FileMode.Open, FileAccess.Read ), DacSchemaModelStorageType.Memory, FileAccess.Read );

        var dbDeployOptions = new DacDeployOptions()
        {
            SqlCommandVariableValues =
            {
                new KeyValuePair< string, string >( "debug", "false" )
            },
            CreateNewDatabase = true,
            BlockOnPossibleDataLoss = false,
            BlockWhenDriftDetected = false
        };

        dbServices.Deploy( dbPackage, targetDbName, upgradeExisting : true, options : dbDeployOptions );
    }
}

Bonus: 你可以使用Microsoft.Build.Evaluation.Project命名空间来新建一个Project对象并在本地构建它以使用生成的 dacpac 初始化测试来进行集成测试。

[SetUpFixture]
public class TestSetup
{
    [SetUp]
    public void SetUpTests()
    {
        var projectPath = @"C:SomeDirectory";
        var project = new Project( projectPath );
        project.Build();
        ProjectCollection.GlobalProjectCollection.UnloadProject( project );

        var dacPac = new DacPacUtility();
        var connString = "Data Source=(localdb)\ProjectsV12;Initial Catalog=Tests;Integrated Security=True";
        var dacPacPath = projectPath + "..\bin\projectName.dacpac";
        dacPac.DeployDacPac(connString, dacPacPath, "Tests");
     }
     [TearDown]
     public void TearDownTests()
     {
       // TODO: delete db or run other cleanup scripts
     }
 }

参考:

Deborah 的开发人员 MindScape：部署 DACPAC http://blogs.msmvps.com/deborahk/deploying-a-dacpac/

最新SSDT https://msdn.microsoft.com/en-us/library/mt204009.aspx

VS 2010 的 SSDT http://blogs.msdn.com/b/ssdt/archive/2012/09/14/new-ssdt-power-tools-now-for-both-visual-studio-2010-and-visual-studio-2012.aspx

到哪里去Microsoft.SqlServer.Dac and so DacService

有几种方法可以获得Microsoft.SqlServer.Dac图书馆，要么：

• Add Nuget 包 https://www.nuget.org/packages/Microsoft.SqlServer.DacFx.x64/
• Or 在您的开发机器上安装 SSDT 工具 https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017， 进而引用DLL https://social.msdn.microsoft.com/Forums/sqlserver/en-US/be484b63-a6cc-4dac-a2c2-78a56ff5b502/where-is-the-microsoftsqlserverdacdll-that-includes-support-for-sql-server-2014?forum=ssdt.