MSBuild.Sdk.SqlProj

简介

这是一个能够从一组SQL脚本生成SQL Server数据层应用程序包(.dacpac)的MSBuild SDK,该包可以随后使用Microsoft.SqlPackage dotnet工具进行部署。它提供了与SQL Server Data Tools .sqlproj项目格式相似的大部分功能,但是基于Visual Studio 2017首次引入的新的SDK风格项目构建。

如果您想要视频介绍,请观看这个dotnetFlix片段。有关该项目的更多背景信息,请阅读以下博客文章:

• 介绍MSBuild.Sdk.SqlProj
• MSBuild.Sdk.SqlProj的更新

行为准则

请花点时间熟悉一下本仓库的行为准则。

使用方法

最简单的入门方式是使用以下命令通过dotnet new安装我们的模板:

dotnet new --install MSBuild.Sdk.SqlProj.Templates

然后您可以使用以下命令创建一个新的项目文件:

dotnet new sqlproj

如果您不想针对最新版本的SQL Server,可以使用-s Sql<version>开关指定要针对的版本。

参见如何确定SQL Server及其组件的版本、版本和更新级别以将SQL Server SKU名称映射到版本号,例如将SQL Server 2016映射到13.0。

参见SqlServerVersion枚举将SQL Server版本号映射到模板和项目文件所需的SqlServerVersion,例如将13.0映射到Sql130。

dotnet new sqlproj -s Sql130

现在您应该有一个包含以下内容的项目文件:

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
        <SqlServerVersion>Sql130</SqlServerVersion>
        <!-- 有关可以在此处设置的其他属性,请参阅 https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#model-properties -->
    </PropertyGroup>

    <PropertyGroup>
        <!-- 有关支持的发布选项,请参阅 https://github.com/rr-wfm/MSBuild.Sdk.SqlProj#publishing-support -->
    </PropertyGroup>
</Project>

然后运行dotnet build,您会在bin\Debug\netstandard2.0文件夹中找到一个与项目文件同名的.dacpac文件。如果您想更改.dacpac文件的名称,可以在项目文件中将<TargetName>属性设置为其他内容。

注意:对于PackageReferences,此SDK目前假定.dacpac文件与包具有相同的名称。如果您计划从项目创建NuGet包(参见下文),请确保<TargetName>与包的ID匹配。

注意:确保不要将TargetFramework元素从netstandard2.0更改为其他任何内容。否则,您将看到类似The "CopyRefAssembly" task was not given a value for the required parameter "SourcePath"的错误。

编辑项目文件

项目文件可以使用.csproj或.fsproj扩展名。

项目文件所在目录中的所有.sql文件,除了Pre-Deployment和Post-Deployment文件夹中的文件,默认都会添加到包中。

要从包中排除文件,添加<Content Remove="Directory\File.sql" />。

如果您使用Visual Studio,要使从包中排除的文件出现在解决方案资源管理器中(如用于部署前和部署后的脚本),添加<None Include="Directory\File.sql" />。

所有节点(Content、None等)都支持通配符。例如,<None Include="Directory\**" />。

项目模板

要创建数据库对象,您可以使用以下项目模板:

注意:在Visual Studio的未来更新中,您应该能够直接从Visual Studio使用项目模板和项目项模板。此功能目前处于预览状态,我们的一些早期测试显示它并不按预期工作。请继续关注此功能的更新。

如果您的解决方案中已经有一个SSDT(.sqlproj)项目,您可以将其保留为"伴随"项目,以享受Visual Studio设计器体验,如此博客文章中所述。

模型属性

生成的.dacpac文件中的模型可以设置许多属性,这些属性可以通过在项目文件中使用相同的名称设置这些属性来影响。例如,下面的片段将RecoveryMode属性设置为Simple:

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
        <RecoveryMode>Simple</RecoveryMode>
        <SqlServerVersion>SqlAzure</SqlServerVersion>
    </PropertyGroup>
</Project>

有关可用属性的更多详细信息,请参阅文档。还支持SqlServerVersion属性。

注意: 如果您要替换现有的.sqlproj,请确保将这些属性中的任何一个复制到新的项目文件中。

模型编译器选项

与.sqlproj项目一样,MSBuild.Sdk.SqlProj支持通过使用MSBuild属性来控制T-SQL构建错误和警告。 可以通过在项目文件中添加TreatTSqlWarningsAsErrors属性来选择性地启用将警告视为错误:

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <TreatTSqlWarningsAsErrors>True</TreatTSqlWarningsAsErrors>
        ...
    </PropertyGroup>
</Project>

注意:或者,您可以使用TreatWarningsAsErrors而不是TreatTSqlWarningsAsErrors来达到相同的效果。

要抑制特定警告被视为错误,请在项目文件中的SuppressTSqlWarnings属性中添加以逗号分隔的警告代码列表:

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <SuppressTSqlWarnings>71558,71502</SuppressTSqlWarnings>
        <TreatTSqlWarningsAsErrors>True</TreatTSqlWarningsAsErrors>
        ...
    </PropertyGroup>
</Project>

您可以通过为特定文件添加SuppressTSqlWarnings来抑制该文件的警告:

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        ...
    </PropertyGroup>

    <ItemGroup>
        <Content Include="Procedures\csp_Test.sql">
            <SuppressTSqlWarnings>71502</SuppressTSqlWarnings>
        </Content>
    </ItemGroup>
</Project>

注意:在项目级别抑制的警告始终应用于项目中的每个文件,无论在文件级别配置了什么。

部署前和部署后脚本

在1.1.0版本中添加了对部署前和部署后脚本的支持。在将.dacpac部署到SQL Server时,这些脚本将自动执行。

要将这些脚本包含到您的.dacpac中,请在您的.csproj中添加以下内容:

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        ...
    </PropertyGroup>

  <ItemGroup>
    <PostDeploy Include="Post-Deployment\Script.PostDeployment.sql" />
    <PreDeploy Include="Pre-Deployment\Script.PreDeployment.sql" />
  </ItemGroup>
</Project>

需要注意的是,Pre-Deployment和Post-Deployment文件夹中的脚本默认从构建过程中排除。这是因为这些脚本通常不定义数据库对象,如表和存储过程,而是执行其他无法在模型中表示的任务。如果不排除这些脚本,您的构建可能会因SQL46010错误而中断。相反,您应该创建一个包含所有这些脚本的脚本文件,使用:r <path-to-script>.sql语法,然后在项目文件中引用该脚本(如上所示)。

默认情况下,使用dotnet publish时不会运行引用包(包括PackageReference和ProjectReference)的部署前和/或部署后脚本。从1.11.0版本开始,可以通过在项目文件中添加RunScriptsFromReferences属性来选择性地启用此功能,如下例所示:

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <RunScriptsFromReferences>True</RunScriptsFromReferences>
        ...
    </PropertyGroup>

    <ItemGroup>
      <PackageReference Include="MyDatabasePackage" Version="1.0.0" />
    </ItemGroup>
</Project>

SQLCMD变量

特别是在使用部署前和部署后脚本时,但也在其他场景中,定义可以在部署时控制的变量可能很有用。这通过SQLCMD变量得到支持,在1.1.0版本中添加。这些变量可以使用以下语法在项目文件中定义:

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        ...
    </PropertyGroup>

  <ItemGroup>
    <SqlCmdVariable Include="MySqlCmdVariable">
      <DefaultValue>DefaultValue</DefaultValue>
      <Value>$(SqlCmdVar__1)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="MySqlCmdVariable2">
      <DefaultValue>DefaultValue</DefaultValue>
      <Value>$(SqlCmdVar__2)</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>

注意:在1.11.0版本之前,上面显示的DefaultValue元素未使用。从1.11.0版本开始,首先检查Value的值,如果发现为空,我们将退回到DefaultValue。

包引用

MSBuild.Sdk.SqlProj支持引用包含.dacpac包的NuGet包。这些可以使用.NET开发人员熟悉的PackageReference格式引用。它们也可以通过Visual Studio中的NuGet包管理器安装。

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="MyDatabasePackage" Version="1.1.0" />
    </ItemGroup>
</Project>

它会假设 .dacpac 文件位于引用包的 tools 文件夹中，并且与 NuGet 包同名。不符合此约定的引用包将被静默忽略。但是，您可以通过使用 PackageReference 上的 DacpacName 属性（在 2.5.0 版本中引入）来覆盖此约定。例如：

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
        <SqlServerVersion>Sql160</SqlServerVersion>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="MyDatabasePackage" Version="1.1.0" DacpacName="SomeOtherDacpac" />
    </ItemGroup>
</Project>

这将添加对 MyDatabasePackage 包中 tools\SomeOtherDacpac.dacpac 文件的引用。请注意，如果该文件在包中不存在，包引用仍将被静默忽略。但是，如果您的项目实际引用了引用包中的对象，构建很可能会失败。

默认情况下，包引用被视为同一数据库的一部分。例如，如果引用包包含一个具有表和存储过程的 .dacpac，当您 dotnet publish 项目时，该包中的表和存储过程将与您项目的内容一起部署到同一数据库。如果不希望这样，您可以向 PackageReference 添加 DatabaseVariableLiteralValue 项元数据，指定不同的数据库名称：

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="MyDatabasePackage" Version="1.1.0" DatabaseVariableLiteralValue="SomeOtherDatabase" />
    </ItemGroup>
</Project>

在这种情况下，您可以使用 [SomeOtherDatabase].[<schema>].[<object>] 语法访问 MyDatabasePackage 定义的对象。

您还可以使用 SQLCMD 变量设置引用，类似于 .sqlproj 项目的行为，方法是向 PackageReference 元素添加 DatabaseSqlCmdVariable 和可选的 ServerSqlCmdVariable 项元数据：

注意：别忘了定义适当的 SQLCMD 变量

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="MyDatabasePackage" Version="1.1.0" DatabaseSqlCmdVariable="SomeOtherDatabase" ServerSqlCmdVariable="SomeOtherServer"/>
    </ItemGroup>

  <ItemGroup>
    <SqlCmdVariable Include="SomeOtherDatabase">
      <DefaultValue>OtherDatabase</DefaultValue>
      <Value>$(SqlCmdVar__1)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="SomeOtherServer">
      <DefaultValue>OtherServer</DefaultValue>
      <Value>$(SqlCmdVar__2)</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>

在这种情况下，您可以使用 [$(SomeOtherServer)].[$(SomeOtherDatabase)].[<schema>].[<object>] 语法访问 MyDatabasePackage 定义的对象。 您还可以将 ServerSqlCmdVariable 与 DatabaseVariableLiteralValue 结合使用，并使用 [$(SomeOtherServer)].[SomeOtherDatabase].[<schema>].[<object>] 语法。

当部署带有对其他 dacpac 引用的 dacpac 时，如果希望将所有 dacpac 的内容部署到单个数据库，则需要指定 IncludeCompositeObjects 属性。例如：

sqlpackage
    /Action:Publish \
    /SourceFile:MyDatabase.dacpac \
    /TargetServerName:localhost \
    /TargetDatabaseName:MyDatabase \
    /TargetUser:sa \
    /TargetPassword: MyP@ssword \
    /Properties:IncludeCompositeObjects=True

引用系统数据库

Microsoft 最近发布了包含 master 和 msdb 数据库定义的 NuGet 包。如果您想在自己的项目中引用这些数据库中的对象而不出现警告，这将非常有用。要引用这些包，您需要使用至少 2.5.0 版本的 MSBuild.Sdk.SqlProj，因为您需要使用上面描述的包引用的 DacpacName 功能。例如：

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
        <SqlServerVersion>160</SqlServerVersion>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="Microsoft.SqlServer.Dacpacs.Master" Version="160.2.1" DacpacName="master" DatabaseVariableLiteralValue="master" />
    </ItemGroup>
</Project>

上面的示例引用了来自 Microsoft.SqlServer.Dacpacs.Master NuGet 包的 master 数据库。请注意，该包针对不同版本的 SQL Server 有不同的主要版本。建议引用与您项目目标 SqlServerVersion 相同的包的最新次要/补丁版本，如上例所示。

对于其他版本的 SQL Server / Azure SQL Database，有专门的包，如此处列出。

项目引用

类似于包引用，您还可以使用 ProjectReference 引用另一个项目。这些引用可以手动添加到项目文件中，也可以通过 Visual Studio 添加。例如，考虑以下示例：

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
    </PropertyGroup>

    <ItemGroup>
        <ProjectReference Include="../MyOtherProject/MyOtherProject.csproj" />
    </ItemGroup>
</Project>

这将确保先构建 MyOtherProject，并且生成的 .dacpac 将被此项目引用。这意味着您可以在此项目的范围内使用其他项目中定义的对象。如果其他项目代表完全不同的数据库，您也可以在 ProjectReference 上使用 DatabaseVariableLiteralValue 或 SQLCMD 变量，类似于 PackageReference：

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
    </PropertyGroup>

    <ItemGroup>
        <ProjectReference Include="../MyOtherProject/MyOtherProject.csproj" DatabaseVariableLiteralValue="SomeOtherDatabase" />
    </ItemGroup>
    <ItemGroup>
        <ProjectReference Include="../MySecondProject/MySecondProject.csproj" DatabaseSqlCmdVariable="SecondOtherDatabase" ServerSqlCmdVariable="SomeOtherServer" />
    </ItemGroup>

  <ItemGroup>
    <SqlCmdVariable Include="SecondOtherDatabase">
      <DefaultValue>SecondDatabase</DefaultValue>
      <Value>$(SqlCmdVar__1)</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="SomeOtherServer">
      <DefaultValue>OtherServer</DefaultValue>
      <Value>$(SqlCmdVar__2)</Value>
    </SqlCmdVariable>
  </ItemGroup>

</Project>

注意：我们不支持向现有的 .sqlproj 文件添加 ProjectReference。

循环引用和 SuppressMissingDependenciesErrors

为了解决可能已错误设置的数据库之间的循环引用，可以向包引用和项目引用添加 SuppressMissingDependenciesErrors：

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="MyDatabasePackage" Version="1.1.0" DatabaseVariableLiteralValue="SomeDatabase" SuppressMissingDependenciesErrors="True"/>
    </ItemGroup>

    <ItemGroup>
        <ProjectReference Include="../MyOtherProject/MyOtherProject.csproj" DatabaseVariableLiteralValue="SomeOtherDatabase" SuppressMissingDependenciesErrors="True"/>
    </ItemGroup>
</Project>

打包支持

MSBuild.Sdk.SqlProj 2.8.1 版本及更高版本支持使用 dotnet pack 命令将您的项目打包成 NuGet 包。

您需要在 .csproj 中设置 PackageProjectUrl 属性，如下所示：

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
  <PropertyGroup>
    ...
    <PackageProjectUrl>your-project-url</PackageProjectUrl>
  </PropertyGroup>

</Project>

包的其他元数据可以通过在项目文件中使用已记录的属性来控制。

打包独立的 dacpac

如果您有一个已编译的 .dacpac 文件，但没有相应的 .csproj，需要作为 PackageReference 引用，您可以使用现有的 NuGet 功能将 dacpac 打包成 NuGet 包。为此，创建一个引用您的 dacpac 的 .nuspec 文件：

<?xml version="1.0" encoding="utf-8" ?>
<package xmlns="http://schemas.microsoft.com/packaging/2011/10/nuspec.xsd">
  <metadata>
    <id>your-dacpac-name</id>
    <version>your-version-number</version>
    <description>your-description</description>
    <authors>your-author</authors>
    <owners>your-owner</owners>
  </metadata>
  <files>
    <file src="fileName.dacpac" target="tools/" />
  </files>
</package>

要创建包，运行：

nuget pack fileName.nuspec

然后将包推送到您的本地 NuGet 仓库：

nuget push fileName.version.nupkg -Source /your/nuget/repo/path

现在您可以将 dacpac 作为 PackageReference 引用了！

注意：要运行这些命令，您需要安装 NuGet CLI 工具。请参阅这些安装说明。如果您使用 Chocolatey，也可以通过运行 choco install nuget.commandline 来安装。在安装了 Homebrew 的 Mac 上，使用 brew install nuget。

发布支持

从 MSBuild.Sdk.SqlProj 的 1.2.0 版本开始，支持使用 dotnet publish 命令将项目发布到 SQL Server。此支持旨在供开发人员快速部署或更新本地开发数据库。对于更高级的部署场景，我们建议使用 SqlPackage，因为它提供了更多选项。

有几个属性控制部署过程，它们有一些默认值，以使本地开发体验尽可能顺畅。例如，在 Windows 上，如果您的本地机器上运行着默认的 SQL Server 实例，运行 dotnet publish 将创建一个与项目同名的数据库。不幸的是，在 Mac 和 Linux 上我们不能使用 Windows 身份验证，所以您需要指定用户名和密码：

dotnet publish /p:TargetUser=<username> /p:TargetPassword=<password>

要进一步自定义部署过程，您可以使用以下属性，这些属性可以在项目文件中设置，或在命令行上指定（使用上面显示的 /p:<property>=<value> 语法）。

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
    <PropertyGroup>
        ...
        <BackupDatabaseBeforeChanges>True</BackupDatabaseBeforeChanges>
        <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
        ...
    </PropertyGroup>
</Project>

大多数这些属性都是简单值（如布尔值、字符串和整数），但有几个属性需要更复杂的值：

脚本生成

除了使用dotnet publish将更改部署到数据库外，您还可以生成一个完整的SQL脚本，从头开始创建数据库，然后对SQL Server运行该脚本。这可以通过在项目文件中添加以下内容来实现：

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
  <PropertyGroup>
      <GenerateCreateScript>True</GenerateCreateScript>
      <IncludeCompositeObjects>True</IncludeCompositeObjects>
  </PropertyGroup>
</Project>

启用此功能后，您将在bin文件夹中找到一个名为<database-name>_Create.sql的SQL脚本。 创建脚本的数据库名称按以下方式解析：

1. TargetDatabaseName。
2. 包名。

注意：

• 生成的脚本还通过setvar命令使用解析后的数据库名称。
• 如果IncludeCompositeObjects为true，外部引用的复合对象（表等）也会包含在生成的脚本中。此属性默认为true

静态代码分析

从SDK的2.7.0版本开始，支持在构建期间运行静态代码分析。SDK包含以下规则集：

• Microsoft.Rules (1, 2和3)
• SqlServer.Rules
• Smells

可以通过在项目文件中添加RunSqlCodeAnalysis属性来启用静态代码分析：

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
  <PropertyGroup>
    <RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>
    <CodeAnalysisRules>-SqlServer.Rules.SRD0006;-Smells.*</CodeAnalysisRules>
  </PropertyGroup>
</Project>

分析结果的xml文件会创建在输出文件夹中。

可选的CodeAnalysisRules属性允许您禁用单个规则或规则组。

在分析过程中发现的任何规则违规都会作为构建警告报告。

可以将单个规则违规或规则组配置为报告为构建错误，如下所示。

<Project Sdk="MSBuild.Sdk.SqlProj/2.8.1">
  <PropertyGroup>
    <RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>
    <CodeAnalysisRules>+!SqlServer.Rules.SRN0005;+!SqlServer.Rules.SRD*</CodeAnalysisRules>
  </PropertyGroup>
</Project>

您还可以使用自己的规则。有关自定义规则的示例，请参见此存储库。

要使用自定义规则，请将规则.dll文件放在项目的Rules文件夹中，并将它们添加为Content项：

  <ItemGroup>
    <Content Include="Rules\My.Own.Rules.dll" />
  </ItemGroup>

集成

MSBuild.Sdk.SqlProj与许多其他技术和工具集成。以下是一些示例：

• MSBuild.Sdk.SqlProj.Aspire - 为MSBuild.Sdk.SqlPproj项目提供.NET Aspire集成的库。它允许您将SQL数据库项目作为.NET Aspire AppHost项目的一部分发布。

• dotnet-sqltest - 用于从MSBuild.Sdk.SqlProj项目运行tSQLt单元测试的命令行工具。

• EF Core Power Tools - Visual Studio扩展，可以直接从您的MSBuild.Sdk.SqlProj项目生成Entity Framework Core DbContext和模型类。

解析器错误（SQL46010）的解决方法

该项目依赖于公开可用的T-SQL解析器，可能不支持所有T-SQL语法结构。因此，如果您有一个包含不支持语法的脚本文件，可能会遇到SQL46010错误。如果发生这种情况，您可以尝试以下几种解决方法：

1. 通过将其构建操作更改为None，将文件完全排除在构建之外。
2. 将有问题的脚本移至预部署和/或后部署脚本。
3. 使用动态SQL代替，如下所示：

DECLARE @Query NVARCHAR(MAX) = '<your-script>'
EXEC (@Query)

从类库引用MSBuild.Sdk.SqlProj

MSBuild.Sdk.SqlProj的输出不是程序集，而是.dacpac。为了正确地从类库引用基于MSBuild.Sdk.SqlProj的项目，需要将ReferenceOutputAssembly提示设置为False：

<ItemGroup>
    <ProjectReference
      Include="../MyDacpacProj/MyDacpacProj.csproj"
      ReferenceOutputAssembly="False" />
</ItemGroup>

现在，在编译类库时，相关的.dacpac文件会被复制到输出目录。

已知限制

由于这不是一个完整的项目系统，而只是一个MSBuild SDK，我们无法为项目内定义的对象提供IntelliSense。可以通过将SQL编辑器连接到用于开发目的的实时数据库来规避这一限制。