Visual Studio SSDT 数据库项目 (.sqlproj) 构建 - 使用 CLI 生成 .dacpac，无需 MSBUILD (msbuild.exe) [英] Visual Studio SSDT Database project (.sqlproj) build - generate .dacpac using CLI without MSBUILD (msbuild.exe)

问题描述

看起来好像 dotnet CLI 不支持数据库项目 (.sqlproj) 根据这个:https://github.com/dotnet/sdk/issues/8546

Looks as though dotnet CLI has no support for Database projects (.sqlproj) according to this: https://github.com/dotnet/sdk/issues/8546

在我的情况下 dotnet build 失败并出现以下错误:

in my case dotnet build fails with the following error:

C:...*.Database.sqlproj(59,3):错误 MSB4019:导入的项目C:\ProgramFiles\dotnet\sdk\3.1.301\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTask​​s.targets"没有找到.确认导入声明中的表达式"C:\程序Files\dotnet\sdk\3.1.301\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTask​​s.targets"是正确的，并且该文件存在于磁盘上.

C:...*.Database.sqlproj(59,3): error MSB4019: The imported project "C:\Program Files\dotnet\sdk\3.1.301\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the expression in the Import declaration "C:\Program Files\dotnet\sdk\3.1.301\Microsoft\VisualStudio\v11.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" is correct, and that the file exists on disk.

不用说，该解决方案通过 Visual Studio 中的 Build 编译并生成 .dacpac.我需要它从命令行工作.除了 msbuild.exe 之外，我还有其他解决方案吗?也许那里有一些可爱的 nuget 包可以提供帮助?

Needless to say, the solution compiles and generates .dacpac via Build in Visual Studio. I need this to work from command line. Do I have any solutions other than msbuild.exe? Perhaps some lovely nuget package out there that could help?

推荐答案

我已设法使其按照本文所述进行工作:https://erikej.github.io/efcore/2020/05/11/ssdt-dacpac-netcore.html

I have managed to make it work as described in this article: https://erikej.github.io/efcore/2020/05/11/ssdt-dacpac-netcore.html

它需要一种特殊类型的 .NET Standard Visual Studio 项目 - MSBuild.Sdk.SqlProj(nuget 下载)，它在编译时从数据库项目中复制脚本.

It requires a special type of .NET Standard Visual Studio project - MSBuild.Sdk.SqlProj (nuget download), which copies scripts from the Database project when compiled.

这是我的 build_database.cmd 代码:

here's my build_database.cmd code:

SET db_project=Database.Build
dotnet tool install -g dotnet-script  
cd %db_project%
dotnet build
dotnet-script Program.csx 

还有一个 C# 脚本 (Program.csx)，它使用 Microsoft.SqlServer.DACFx nuget 包创建和部署 .dacpac:

And a C# script (Program.csx), which creates and deploys a .dacpac using Microsoft.SqlServer.DACFx nuget package:

#r "nuget: Microsoft.SqlServer.DACFx, 150.4769.1"
using Microsoft.SqlServer.Dac;

var dbName = "SampleDatabase";
var connectionString = $"Data Source=.;Initial Catalog={dbName};Integrated Security=True;";
var dacpacLocation = Directory.GetFiles(@".\bin", "Database.Build.dacpac", 
                                                           SearchOption.AllDirectories)[0];
var dbPackage = DacPackage.Load(dacpacLocation);
var services = new DacServices(connectionString);

services.Deploy(dbPackage, dbName, true); 

这种方法是完全自动化的，可以在 CI 或本地开发环境中使用，而且显然也是跨平台的(尽管我只在 Windows 上测试过).希望这会有所帮助:)

this approach is fully automated and can be used in either CI or local dev environment and, apparently, is also cross-platform (albeit I've only tested on Windows). Hope this helps :)

这篇关于Visual Studio SSDT 数据库项目 (.sqlproj) 构建 - 使用 CLI 生成 .dacpac，无需 MSBUILD (msbuild.exe)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！