Adjust SQL Project Files to enforce a given SqlCodeAnalysisRules set

adjust-sqlproj-rules.yml

parameters:
- name: rules
  type: string
- name: directory
  type: string

steps:
- powershell: |
    $rules = '${{ parameters.rules }}'
    $dir = '${{ parameters.directory }}'

    $extension = "*.sqlproj"

    $projFiles = Get-ChildItem -Path $dir -Recurse -Include $extension | Select-Object -ExpandProperty FullName | ForEach-Object {
    $content = Get-Content $_  
    $regex = "(?<=<SqlCodeAnalysisRules>).*?(?=<\/SqlCodeAnalysisRules>)"

    if (-not ($content -match "SqlCodeAnalysisRules")) {
        Write-Host "No SQL Rules found in $_ .. Adding them"
        $newContent = $content -replace "</Project>", "  <PropertyGroup>`r`n    <SqlCodeAnalysisRules>$rules</SqlCodeAnalysisRules>`r`n  </PropertyGroup>`r`r</Project>"
    } else {
        Write-Host "Swapping out existing rules in $_ "
        $newContent = $content -replace $regex, $rules
    }

    Set-Content $_ -Value $newContent
    }
  displayName: Adjust SQL Project Ruleset