Azure DevOps中基于LocalDB的数据库项目测试流水线配置问题

目标 #

需要搭建一个包含SQL Server数据库项目、单元测试（可扩展集成测试）的解决方案，通过Azure DevOps Git仓库管理代码。流水线需自动启动LocalDB（mssqllocaldb），部署数据库项目的结构和数据，最后运行能访问该本地数据库的单元测试。

当前进展 #

已编写一个基于Dapper的简单测试（后续计划迁移到API项目）：

[TestMethod]
public void TestDBConnection()
{
    var connString = "Server=(localdb)\\mssqllocaldb;Database=custom_db_name_here;Trusted_Connection=True;";
    using (var connection = new SqlConnection(connString))
    {
        var result = connection.Query<int>(sql: "select 1", commandType: CommandType.Text);
        Assert.AreEqual(result.Count(), 1);
        Assert.AreEqual(result.FirstOrDefault(), 1);
    }
}

已生成Azure DevOps YAML流水线并添加了启动LocalDB的任务：

trigger:
- master

pool:
  vmImage: 'windows-latest'

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

steps:
- task: NuGetToolInstaller@1

- task: NuGetCommand@2
  inputs:
    restoreSolution: '$(solution)'

- task: VSBuild@1
  inputs:
    solution: '$(solution)'
    msbuildArgs: '/p:DeployOnBuild=true /p:WebPublishMethod=Package /p:PackageAsSingleFile=true /p:SkipInvalidConfigurations=true /p:DesktopBuildPackageLocation="$(build.artifactStagingDirectory)\WebApp.zip" /p:DeployIisAppPath="Default Web Site"'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

- task: PowerShell@2
  displayName: 'start mssqllocaldb'
  inputs:
    targetType: 'inline'
    script: 'sqllocaldb start mssqllocaldb'

# Publish probably goes here, not sure how though?

- task: VSTest@2
  inputs:
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

问题 #

不清楚如何在流水线中创建新数据库，部署数据库结构（表、存储过程等）及初始化数据。

解决方案 #

1. 确保数据库项目生成DACPAC文件 #

SQL Server数据库项目默认会生成.dacpac文件（包含完整的数据库结构定义），可以单独添加MSBuild任务编译数据库项目，指定输出路径：

- task: MSBuild@1
  displayName: 'Build Database Project'
  inputs:
    solution: '**/*.sqlproj'
    msbuildArguments: '/p:Configuration=$(buildConfiguration) /p:OutputPath=$(build.artifactStagingDirectory)/DB'

执行后，$(build.artifactStagingDirectory)/DB目录下会生成对应的.dacpac文件。

2. 使用SqlPackage.exe部署DACPAC到LocalDB #

windows-latest镜像预装了Visual Studio，SqlPackage.exe通常位于C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe（版本号可能随VS更新变化）。添加PowerShell任务执行部署：

- task: PowerShell@2
  displayName: 'Deploy DACPAC to LocalDB'
  inputs:
    targetType: 'inline'
    script: |
      $sqlPackagePath = "C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe"
      # 自动获取目录中的DACPAC文件
      $dacpacFile = Get-ChildItem -Path $(build.artifactStagingDirectory)/DB -Filter *.dacpac | Select-Object -First 1
      $connString = "Server=(localdb)\mssqllocaldb;Trusted_Connection=True;Database=custom_db_name_here"
      
      & $sqlPackagePath /Action:Publish /SourceFile:$dacpacFile.FullName /TargetConnectionString:$connString

• /Action:Publish：自动创建目标数据库（如果不存在）并同步所有结构（表、存储过程等）
• /TargetConnectionString：指定目标数据库的连接信息，包含要创建的数据库名称

3. （可选）初始化测试数据 #

如果需要导入测试数据，可使用sqlcmd执行SQL脚本：

- task: PowerShell@2
  displayName: 'Run Test Data Script'
  inputs:
    targetType: 'inline'
    script: |
      $scriptPath = "$(Build.SourcesDirectory)/Tests/TestData.sql"
      if (Test-Path $scriptPath) {
          sqlcmd -S "(localdb)\mssqllocaldb" -d "custom_db_name_here" -i $scriptPath -E
      }

完整流水线YAML示例 #

trigger:
- master

pool:
  vmImage: 'windows-latest'

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'
  dbName: 'custom_db_name_here'
  dacpacOutputPath: '$(build.artifactStagingDirectory)/DB'

steps:
- task: NuGetToolInstaller@1

- task: NuGetCommand@2
  inputs:
    restoreSolution: '$(solution)'

# 单独构建Web/API项目（排除数据库项目）
- task: VSBuild@1
  displayName: 'Build Web Project'
  inputs:
    solution: '**/*.csproj'
    msbuildArgs: '/p:DeployOnBuild=true /p:WebPublishMethod=Package /p:PackageAsSingleFile=true /p:SkipInvalidConfigurations=true /p:DesktopBuildPackageLocation="$(build.artifactStagingDirectory)\WebApp.zip" /p:DeployIisAppPath="Default Web Site"'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'
    excludeProjects: '**/*.sqlproj'

# 构建数据库项目生成DACPAC
- task: MSBuild@1
  displayName: 'Build Database Project'
  inputs:
    solution: '**/*.sqlproj'
    msbuildArguments: '/p:Configuration=$(buildConfiguration) /p:OutputPath=$(dacpacOutputPath)'

# 启动LocalDB
- task: PowerShell@2
  displayName: 'Start LocalDB'
  inputs:
    targetType: 'inline'
    script: 'sqllocaldb start mssqllocaldb'

# 部署DACPAC到LocalDB
- task: PowerShell@2
  displayName: 'Deploy DACPAC'
  inputs:
    targetType: 'inline'
    script: |
      $sqlPackagePath = "C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe"
      $dacpacFile = Get-ChildItem -Path $(dacpacOutputPath) -Filter *.dacpac | Select-Object -First 1
      if (-not $dacpacFile) {
          throw "DACPAC file not found in $(dacpacOutputPath)"
      }
      $connString = "Server=(localdb)\mssqllocaldb;Trusted_Connection=True;Database=$(dbName)"
      
      & $sqlPackagePath /Action:Publish /SourceFile:$dacpacFile.FullName /TargetConnectionString:$connString

# （可选）初始化测试数据
- task: PowerShell@2
  displayName: 'Initialize Test Data'
  inputs:
    targetType: 'inline'
    script: |
      $scriptPath = "$(Build.SourcesDirectory)/Tests/TestData.sql"
      if (Test-Path $scriptPath) {
          sqlcmd -S "(localdb)\mssqllocaldb" -d $(dbName) -i $scriptPath -E
      } else {
          Write-Host "Test data script not found, skipping..."
      }

# 运行单元测试
- task: VSTest@2
  inputs:
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

内容的提问来源于stack exchange，提问作者nick zoum