Сборка и публикация sqlproj из кода для интеграционного тестирования

Question

У меня есть (2015) решение с проектом базы данных (SQL Server 2008) (.sqlproj) и я хочу написать интеграционные тесты, используя этот проект базы данных. Мне нужно пересоздавать базу данных для каждого теста, но я не могу заставить ее работать, используя Microsoft.Build,

В моей тестовой сборке я ссылался на следующее:

<Reference Include="Microsoft.Build, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" />
<Reference Include="Microsoft.Build.Framework, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" />
<Reference Include="Microsoft.Build.Tasks.Core, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" />
<Reference Include="Microsoft.Build.Utilities.Core, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" />

Мой файл профиля публикации для тестов:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>OnTime</TargetDatabaseName>
    <DeployScriptFileName>OnTime.Database.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=.\SQLX;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <CreateNewDatabase>True</CreateNewDatabase>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

И код, который я использую для настройки:

[OneTimeSetUp]
public void Setup()
{
    var connectionString = ConfigurationManager.ConnectionStrings["OnTime"].ConnectionString;

    //var directory = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName;

    ILogger logger = new DebugLogger();

    // Set up properties.
    //https://msdn.microsoft.com/en-us/library/microsoft.data.tools.schema.tasks.sql.sqlpublishtask(v=vs.103).aspx
    var projects = ProjectCollection.GlobalProjectCollection;
    projects.SetGlobalProperty("Configuration", "Debug");
    projects.SetGlobalProperty("CreateNewDatabase", "True");
    projects.SetGlobalProperty("VisualStudioVersion", "14.0");
    projects.SetGlobalProperty("SqlPublishProfilePath", @"C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\Publish\OnTime.Database.publish.SQLEXPRESS.xml");
    // Load and build project.
    var dbProject = ProjectCollection.GlobalProjectCollection.LoadProject(@"C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj");
    var result = dbProject.Build(new[] { "Build", "Publish" }, new[] { logger });
}

Выходные данные, которые я получаю из DebugLogger: (вывод только из задачи публикации)

Build started.
Project "OnTime.Database.sqlproj" (Publish target(s)):
Building with tools version "14.0".
Target "CheckRequiredProperties" in file "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" from project "C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj" (entry point):
Done building target "CheckRequiredProperties" in project "OnTime.Database.sqlproj".
Target "_CheckForInvalidConfigurationAndPlatform" in file "C:\Program Files (x86)\MSBuild\14.0\bin\Microsoft.Common.CurrentVersion.targets" from project "C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj" (entry point):
Task "Error" skipped, due to false condition; ( '$(_InvalidConfigurationError)' == 'true' ) was evaluated as ( '' == 'true' ).
Task "Warning" skipped, due to false condition; ( '$(_InvalidConfigurationWarning)' == 'true' ) was evaluated as ( '' == 'true' ).
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.Build.Tasks.Core\v4.0_14.0.0.0__b03f5f7f11d50a3a\Microsoft.Build.Tasks.Core.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Using "Message" task from assembly "Microsoft.Build.Tasks.Core, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a".
Task "Message"
Configuration=Debug
Done executing task "Message".
Task "Message"
Platform=AnyCPU
Done executing task "Message".
Task "Error" skipped, due to false condition; ('$(OutDir)' != '' and !HasTrailingSlash('$(OutDir)')) was evaluated as ('bin\Debug\' != '' and !HasTrailingSlash('bin\Debug\')).
Task "Error" skipped, due to false condition; ('$(BaseIntermediateOutputPath)' != '' and !HasTrailingSlash('$(BaseIntermediateOutputPath)')) was evaluated as ('obj\' != '' and !HasTrailingSlash('obj\')).
Task "Error" skipped, due to false condition; ('$(IntermediateOutputPath)' != '' and !HasTrailingSlash('$(IntermediateOutputPath)')) was evaluated as ('obj\Debug\' != '' and !HasTrailingSlash('obj\Debug\')).
Done building target "_CheckForInvalidConfigurationAndPlatform" in project "OnTime.Database.sqlproj".
Target "BeforePublish" in file "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" from project "C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj" (target "Publish" depends on it):
Done building target "BeforePublish" in project "OnTime.Database.sqlproj".
Target "PrePublishEvent" skipped, due to false condition; ('$(PrePublishEvent)'!='' And Exists($(TargetDir))) was evaluated as (''!='' And Exists(C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\bin\Debug\)).
Target "_SetupSqlPublishInputs" in file "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" from project "C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj" (target "SqlPublish" depends on it):
Done building target "_SetupSqlPublishInputs" in project "OnTime.Database.sqlproj".
Target "_SetupSqlPublishOutputs" in file "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" from project "C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj" (target "SqlPublish" depends on it):
Done building target "_SetupSqlPublishOutputs" in project "OnTime.Database.sqlproj".
Target "SqlPublish" in file "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" from project "C:\Users\SNUY\Documents\Code\OnTime\OnTime.Database\OnTime.Database.sqlproj" (target "Publish" depends on it):
Building target "SqlPublish" completely.
Input file "ForceScriptRecalculation.txt" does not exist.
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Schema.Tasks.Sql.dll'. Cannot find or open the PDB file.
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.Build.Utilities.v4.0\v4.0_4.0.0.0__b03f5f7f11d50a3a\Microsoft.Build.Utilities.v4.0.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.Build.Framework\v4.0_4.0.0.0__b03f5f7f11d50a3a\Microsoft.Build.Framework.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Using "SqlPublishTask" task from assembly "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Schema.Tasks.Sql.dll".
Task "SqlPublishTask"
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Schema.Utilities.Sql.dll'. Cannot find or open the PDB file.
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Schema.Sql.dll'. Cannot find or open the PDB file.
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Utilities.dll'. Cannot find or open the PDB file.
'vstest.executionengine.x86.exe' (CLR v4.0.30319: test-domain-OnTime.Data.Test.dll): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.SqlServer.Dac.Extensions.dll'. Cannot find or open the PDB file.
The "SqlPublishTask" task could not be instantiated from the assembly "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Schema.Tasks.Sql.dll". Please verify the task assembly has been built using the same version of the Microsoft.Build.Framework assembly as the one installed on your computer and that your host application is not missing a binding redirect for Microsoft.Build.Framework. Unable to cast object of type 'Microsoft.Data.Tools.Schema.Tasks.Sql.SqlPublishTask' to type 'Microsoft.Build.Framework.ITask'.
The "SqlPublishTask" task has been declared or used incorrectly, or failed during construction. Check the spelling of the task name and the assembly name.
The thread 0x34b8 has exited with code 0 (0x0).
Done executing task "SqlPublishTask" -- FAILED.
Done building target "SqlPublish" in project "OnTime.Database.sqlproj" -- FAILED.
Done building project "OnTime.Database.sqlproj" -- FAILED.
Build FAILED.

Запуск с использованием MSBuild из командной строки работает нормально

msbuild /t:Build;Publish /p:SqlPublishProfilePath=Publish\OnTime.Database.publish.SQLEXPRESS.xml

Содержимое файла sqlproj:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">
  <Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <Name>OnTime.Database</Name>
    <SchemaVersion>2.0</SchemaVersion>
    <ProjectVersion>4.1</ProjectVersion>
    <ProjectGuid>{7ce126e0-b091-428f-8983-0c3db87922ad}</ProjectGuid>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql100DatabaseSchemaProvider</DSP>
    <OutputType>Database</OutputType>
    <RootPath>
    </RootPath>
    <RootNamespace>OnTime.Database</RootNamespace>
    <AssemblyName>OnTime.Database</AssemblyName>
    <ModelCollation>1033, CI</ModelCollation>
    <DefaultFileStructure>BySchemaAndSchemaType</DefaultFileStructure>
    <DeployToDatabase>True</DeployToDatabase>
    <TargetFrameworkVersion>v4.5.2</TargetFrameworkVersion>
    <TargetLanguage>CS</TargetLanguage>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <SqlServerVerification>False</SqlServerVerification>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseSet>True</TargetDatabaseSet>
    <DefaultSchema>OnTime</DefaultSchema>
    <GenerateCreateScript>True</GenerateCreateScript>
    <IsChangeTrackingOn>True</IsChangeTrackingOn>
    <ChangeTrackingRetentionPeriod>7</ChangeTrackingRetentionPeriod>
    <SccProjectName>SAK</SccProjectName>
    <SccProvider>SAK</SccProvider>
    <SccAuxPath>SAK</SccAuxPath>
    <SccLocalPath>SAK</SccLocalPath>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <OutputPath>bin\Release\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>False</TreatWarningsAsErrors>
    <DebugType>pdbonly</DebugType>
    <Optimize>true</Optimize>
    <DefineDebug>false</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <OutputPath>bin\Debug\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>false</TreatWarningsAsErrors>
    <DebugSymbols>true</DebugSymbols>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <DefineDebug>true</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup>
    <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">11.0</VisualStudioVersion>
    <!-- Default to the v11.0 targets path if the targets file for the current VS version is not found -->
    <SSDTExists Condition="Exists('$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets')">True</SSDTExists>
    <VisualStudioVersion Condition="'$(SSDTExists)' == ''">11.0</VisualStudioVersion>
  </PropertyGroup>
  <Import Condition="'$(SQLDBExtensionsRefPath)' != ''" Project="$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <Import Condition="'$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <ItemGroup>
    <Folder Include="Properties" />
    <Folder Include="Tables" />
    <Folder Include="StoredProcedures" />
    <Folder Include="Views" />
    <Folder Include="Publish" />
    <Folder Include="Scripts" />
  </ItemGroup>
  <ItemGroup>
    <Build Include="Schema.sql" />
    <Build Include="Tables\Contract.sql" />
    <Build Include="Tables\Customer.sql" />
    <Build Include="Tables\Log.sql" />
    <Build Include="Tables\User.sql" />
    <Build Include="StoredProcedures\Report.sql" />
    <Build Include="Views\LogDetailView.sql" />
    <Build Include="Login.sql" />
    <Build Include="User.sql" />
  </ItemGroup>
  <ItemGroup>
    <PostDeploy Include="Scripts\Seed.sql" />
  </ItemGroup>
  <ItemGroup>
    <None Include="Publish\OnTime.Database.publish.SQLEXPRESS.xml" />
  </ItemGroup>
</Project>

Это моя главная проблема, я думаю:

Не удалось создать задачу "SqlPublishTask" из сборки "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\Dac\130\Microsoft.Data.Tools.Schema.Tasks.Sql.dll". Убедитесь, что сборка задачи была построена с использованием той же версии сборки Microsoft.Build.Framework, что и на вашем компьютере, и что ваше хост-приложение не пропускает перенаправление привязки для Microsoft.Build.Framework. Невозможно привести объект типа "Microsoft.Data.Tools.Schema.Tasks.Sql.SqlPublishTask" к типу "Microsoft.Build.Framework.ITask".

Answer 1

Посмотрев немного получше, я начал смотреть на упомянутые сборки Microsoft.Build. Похоже, у меня есть несколько установленных.

Обязательно добавьте следующее:

C: \ Program Files (x86) \ Справочные сборки \ Microsoft \ Framework.NETFramework \ v4.5.2 \ Microsoft.Build.dll

C: \ Program Files (x86) \ Справочные сборки \Microsoft\Framework.NETFramework\v4.5.2\Microsoft.Build.Framework.dll

Остальные были установлены в C:\Program Files (x86)\Reference Assemblies\Microsoft\MSBuild\v14.0 (версия 14.0.25420.1)

Answer 2

Я скачал ваш проект и воспроизвел вашу проблему на моей стороне, и вы добавили неверную ссылку (Microsoft.Build, Microsoft.Build.Framework,Microsoft.Build.Tasks.Core,Microsoft.Build.Utilities.Core). удалите ссылку, затем добавьте ссылку из.NetFramework 4.5.2.

Я загружаю проект в OneDrive, пожалуйста, проверьте его. https://1drv.ms/u/s!AlvaNEnglADDdiwzuwrNRtI_6n0

Answer 3

Кажется, что некоторые настройки по умолчанию запускаются, когда вы не поставили "Конфигурация" в cmdline, и это можно запустить, но в коде C# вы объявляете "Конфигурация". Трудно сказать без глаз на sqlproj.