如何通过代码发布SSDT数据库 [英] How do I publish a SSDT Database from code

问题描述

当我双击此文件并发布时，我们有 SQLServer01.Publish.xml ，它将数据库发布到sqlServer01.

We have SQLServer01.Publish.xml when I double click this file and publish, it publishes a database to sqlServer01.

我想问一下我们是否可以通过代码发布此个人资料?

I wanted to ask can we publish this profile from code somehow ?

推荐答案

SSDT似乎喜欢DacPac.在 Microsoft.SqlServer.Dac 中有一个 DacServices 实用工具类.我认为这将要求在计划运行此代码的计算机上安装SSDT.

SSDT seems to like DacPac for this kind of thing. There is a DacServices utility class in Microsoft.SqlServer.Dac. I think this will require SSDT to be installed on the machine you plan on running this code.

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 );
    }
}

奖金:您可以使用 Microsoft.Build.Evaluation.Project 命名空间来创建 Project 对象并在本地构建以进行集成使用生成的内置dacpac进行测试以初始化测试.

Bonus: you can use the Microsoft.Build.Evaluation.Project namespace to new up a Project object and build it locally for integration testing using the resulting built dacpac to initialize a test.

[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

最新的SSDT

在哪里获取 Microsoft.SqlServer.Dac 以及 DacService

Where to get Microsoft.SqlServer.Dac and so DacService

有两种方法可以获取 Microsoft.SqlServer.Dac 库，或者:

There are a couple of ways to get the Microsoft.SqlServer.Dac library, either:

• 添加 Nuget软件包
• 或引用DLL .

这篇关于如何通过代码发布SSDT数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！