How to include created database project created dacpac into a cs project

Question

I have a separate database project that I’d like to build within the same solution and then reference the created dacpac. When I try adding the database project it builds fine and the dll is added to the secondary project file, but the dacpac is not.

Is there a way that I can have the dacpac copied into my main project through the msbuild? I keep on thinking that there should be a way to modify either the sqlproj file or csproj file so that the dacpac is included as one of the project outputs. My knowledge of msbuild is not extensive, I’ve not been able to figure it out.

It seems to me that I need to add the dacpac somehow to say the '@(ReferenceCopyLocalPaths)' item but I have not been able to figure it out. Any tips or suggestions would be appreciated.

I tried doing something a little like what is referenced here MSBuild - ItemGroup of all bin directories within subdirectories by doing:

   <Target Name="AfterBuild">
    <Message Text="@(MainAssembly)" />
    <!--<DacPacs Include="%(ProjectReference.Directory)**"  />-->
    <ItemGroup>
      <DacPacs Include="%(ProjectReference.Directory)**/*bin*/*.dac" />
    </ItemGroup>
    <Message Text="@(ReferenceCopyLocalPaths)" />
    <Message Text="DacPacs: @(DacPacs)" />
    <Message Text="Target Database: $(TargetDatabase)" />
  </Target>

which gives nothing for DacPacs (when the wildcard is added). Also I tried referencing one of the item groups from the sqlproj file but it comes out empty to:

Nad

Did you get this working? – Nickolas

Obviously no... I have the same problem here :( – Withindoors

Answer 1

Add this to your csproj:

  <ItemGroup>
    <Content Include="..\DatabaseProject\bin\Debug\DatabaseProject.dacpac">
      <Link>DatabaseProject.dacpac</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>
  </ItemGroup>

Inspan

That would be great, but it won't work when I switch to Release configuration. – Vocative

Maybe I found a solution. You can copy, a file from another configuration to Debug in DatabaseProject Post-build Event: if "$(Configuration)" NEQ "Debug" xcopy /y DatabaseProject.dacpac ..\Debug\ – Vocative

You are right, it's currently hard coded to debug in my answer; you might be able to use a placeholder for the output path based on the current sln configuration, but I didn't get that far, I'm just hoping we can set fire to the dacpacs in our sln at some point and forget this ever happened. Shudder. – Inspan

The $(Configuration) MSBuild macro can be used to adjust based on the build config: <Content Include="..\DatabaseProject\bin\$(Configuration)\DatabaseProject.dacpac">. – Stagnant

Answer 2

This solution worked for me with a .NET 6 project which references a .sqlproj project using Micrsoft.Sql.Sdk. Assume there are two projects: DacpacDepender (.NET 6 project) and Dacpac (SQL project).

In DacpacDepender.csproj:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <!-- some properties redacted for brevity -->
    <TargetFramework>net6.0</TargetFramework>
    <RestorePackagesWithLockFile>true</RestorePackagesWithLockFile>
  </PropertyGroup>

  <ItemGroup>
    <!-- Ensures build order, the DACPAC is _guaranteed_ to exist when included -->
    <ProjectReference Include="..\Dacpac\Dacpac.sqlproj">
      <!-- Companion to .sqlproj CopyBuildOutputToOutputDirectory=false  -->
      <ReferenceOutputAssembly>false</ReferenceOutputAssembly>
      <!-- Suppresses a warning about this project not being compatible with SQL project -->     
      <SkipGetTargetFrameworkProperties>true</SkipGetTargetFrameworkProperties>
    </ProjectReference>
  </ItemGroup>

  <ItemGroup>
    <!-- Use `$(Configuration)` to reference the DACPAC output location regardless of build configuration. -->
    <Content Include="$(ProjectDir)..\Dacpac\bin\$(Configuration)\Dacpac.dacpac" CopyToOutputDirectory="PreserveNewest" />
  </ItemGroup>
</Project>

In Dacpac.sqlproj:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.3-preview" />
  <PropertyGroup>
    <!-- some properties redacted for brevity -->
    <!-- Prevents outputting the DLL (some may need it) --> 
    <CopyBuildOutputToOutputDirectory>false</CopyBuildOutputToOutputDirectory>
    <CopyOutputSymbolsToOutputDirectory>false</CopyOutputSymbolsToOutputDirectory>
    <Name>Dacpac</Name>
    <NetCoreBuild>True</NetCoreBuild>
    <RestorePackagesWithLockFile>true</RestorePackagesWithLockFile>
  </PropertyGroup>
</Project>

Please note that <RestorePackagesWithLockFile /> is not strictly necessary but ensures dotnet restore --locked-mode works, which is a nicety for reproducible builds.

Stagnant