问Azure管道托管在数据库单元测试中与Azure SQL数据库的代理连接

EN

trigger:
- test

name: $(TeamProject)_$(Date:yyyyMMdd)$(Rev:_r)

variables:
- group: "DatabaseDeploy"
- name:  databaseName
  value: sqldb-AzureDevOps_$(Build.BuildNumber)
#- name: ExecutionContext.__Database__
#  value: $(variables.databaseName)

stages:

### Building the code in the Pull-Request

- stage: Build
  displayName: Build the project

  jobs:
  - job: Build
    displayName: Build DB project
    pool:
      vmImage: windows-latest

    steps:


    - script: echo '$(Build.BuildNumber)'
      displayName: BuildName

    #- powershell: 
    #    Get-ChildItem -Path $(Agent.WorkFolder)\1\s\BuilEnDeployTest_Unittest -recurse
    #  displayName: Folder structure

    #- script: echo '$(Agent.WorkFolder)\1\s\BuilEnDeployTest_Unittest\app.config'
    #  displayName: app.config

    - powershell: 
         Get-Content -path $(Agent.WorkFolder)/1/s/BuilEnDeployTest_Unittest/app.config
      displayName: app.config

    - powershell: >
         (Get-Content -path $(Agent.WorkFolder)/1/s/BuilEnDeployTest_Unittest/app.config -Raw) 
         -replace 'MyDatabase', '$(databaseName)' 
         -replace 'MyServer', '$(ServerName_unittst).database.windows.net'
         -replace 'MyUser', '$(DatabaseUser_unittst)'
         -replace 'MyPassword', '$(DatabasePassword_unittst)'| Set-Content -Force -Path $(Agent.WorkFolder)/1/s/BuilEnDeployTest_Unittest/app.config

    - powershell: 
         Get-Content -path $(Agent.WorkFolder)/1/s/BuilEnDeployTest_Unittest/app.config
      displayName: app.config
    
    - task: VSBuild@1
      displayName: Building the database project
      inputs:
        solution: '**\*.sln'

    - task: CopyFiles@2
      displayName: Copy Build Artifacts
      inputs:
        SourceFolder: '$(agent.builddirectory)\s'
        Contents: '**'
        TargetFolder: '$(build.artifactstagingdirectory)'

         
        
   ### In the commands below, 'pipeline' is used instead of 'Container', allthough
   ### 'Container' is often seen in the online documentation. 
   ### I have noticed that changing 'pipeline' to 'Container' breaks my code
   ### I haven't spend time in figuring out why. It could be because of the path I use 
   ### down the line. 

    - task: PublishPipelineArtifact@1
      displayName: Publish Build Artifacts
      inputs:
        PathtoPublish: '$(Build.ArtifactStagingDirectory)'
        ArtifactName: 'database'
        publishLocation: 'pipeline'

#### Unit testing the Pull-Request

- stage: Unit_testing
  displayName: Database UnitTest
  condition: and(succeeded(), eq(variables['Build.Reason'], 'PullRequest'))


  jobs:
  - job: Deploy_to_temporary_database
    displayName: Deploy to temporary database
    pool:
      vmImage: windows-latest

 

    steps:

    ############ Create the Azure Resources as defined in the AzureSQLARMTemplate.json

    ## When the Azure SQL server does not exist it is also created by the ARM deployment.
    
    ## NOTE: I experienced errors when deleting and recreating the Azure SQL server. This could be 
    ## because of the little time between deletion and creation. 
    ## Deployment through the ARM template can deal with an existing server. In that case
    ## the server creation is skipped. So best to not delete the Azure SQL server untill the project
    ## is abandoned. 

    - task: AzurePowerShell@5
      displayName: 'Create unittest DB'
      inputs:
        azureSubscription: $(connectedServiceNameARM)
        ScriptType: 'FilePath'
        ScriptPath: './ARMtemplate/CreateAzureSQLAndDB.ps1'
        ScriptArguments: -azuresqlserverName $(ServerName_unittst) -sqlserverAdminLogin $(DatabaseUser_unittst) -sqlserverAdminPassword $(DatabasePassword_unittst) -databaseName $(databaseName) -Location 'northeurope' -ResourceGroupName 'rg-temp' -TemplateFile ARMtemplate\AzureSQLARMTemplate.json
        azurePowerShellVersion: 'LatestVersion'





    ############ Download the build artifacts from the build stage

    - task: DownloadPipelineArtifact@2
      displayName: 'Download artifacts'
      inputs: 
        buildType: 'current'
        artifactName: 'database'
        targetPath: '$(Pipeline.Workspace)/database'

    - powershell: 
        Get-ChildItem -Path $(Pipeline.Workspace)/database -recurse
      displayName: Folder structure

    - powershell: 
         Get-Content -path $(Pipeline.Workspace)/database/a/BuilEnDeployTest_Unittest/bin/Debug/BuilEnDeployTest_Unittest.dll.config
      displayName: BuilEnDeployTest_Unittest.dll.config

    ############ Deploy the database dacpac file to the newly created database

    - task: SqlAzureDacpacDeployment@1
      displayName: 'Deploy unittest DB'
      inputs:
        connectedServiceNameARM: $(connectedServiceNameARM)
        ServerName: $(ServerName_unittst).database.windows.net
        DatabaseName: $(databaseName)
        SqlUsername: $(DatabaseUser_unittst)
        SqlPassword: $(DatabasePassword_unittst)
        DacpacFile: $(Pipeline.Workspace)/database/a/$(dacpacfile_location)
        PublishProfile: $(Pipeline.Workspace)/database/a/$(publishprofile_location)

    ## Create a firewall rule for the IP adress of the build agent

    - task: AzurePowerShell@5
      displayName: 'Add buildserver public ip'
      inputs:
        azureSubscription: $(connectedServiceNameARM)
        ScriptType: InlineScript
        Inline: |
          $ip = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content
          $AzureSQLFirewallRule = Get-AzSqlServerFirewallRule -ResourceGroupName 'rg-temp' -ServerName $(ServerName_unittst) -FirewallRuleName "azuredevops"
          if ($AzureSQLFirewallRule -eq $null) {New-AzSqlServerFirewallRule -ResourceGroupName 'rg-temp' -ServerName $(ServerName_unittst) -FirewallRuleName "azuredevops" -StartIpAddress $ip -EndIpAddress $ip}
        azurePowerShellVersion: 'LatestVersion'     

    ############ Run the unittest

    - task: VSTest@2
      inputs:
        testAssemblyVer2: $(Pipeline.Workspace)/database/a/$(Unittestdll_location)
        testConfiguration: 'Debug'
        runOnlyImpactedTests: true
        runInParallel: false

    ############ Delete the newly created database
         
    - task: AzurePowerShell@5
      displayName: 'Delete unittest DB'
      inputs:
        azureSubscription: $(connectedServiceNameARM)
        ScriptType: 'FilePath'
        ScriptPath: './ARMtemplate/CleanupAzureSQLAndDB.ps1'
        ScriptArguments: -azuresqlserverName $(ServerName_unittst) -databaseName $(databaseName) -ResourceGroupName 'rg-temp'
        azurePowerShellVersion: 'LatestVersion'

     ## Delete a firewall rule for the IP adress of the build agent

    - task: AzurePowerShell@5
      displayName: 'remove buildserver public ip'
      inputs:
        azureSubscription: $(connectedServiceNameARM)
        ScriptType: InlineScript
        Inline: |
          $ip = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content
          Remove-AzSqlServerFirewallRule -ResourceGroupName 'rg-temp' -ServerName $(ServerName_unittst) -FirewallRuleName "azuredevops"
        azurePowerShellVersion: 'LatestVersion'


## Deploying the merged result to the database

- stage: Deploy_to_tst
  displayName: Deploy
  condition: and(succeeded(), ne(variables['Build.Reason'], 'PullRequest'))

  jobs:
  - job: Deploy
    displayName: Deploy
    pool:
      vmImage: windows-latest
  
    steps:
    - task: DownloadPipelineArtifact@2
      inputs: 
        buildType: 'current'
        artifactName: 'database'
        targetPath: '$(Pipeline.Workspace)/database'

    - powershell: 
        #Get-ChildItem -Path $(Pipeline.Workspace)/database -recurse
        #Get-ChildItem -Path $(Agent.Workspace) -recurse

    - task: SqlAzureDacpacDeployment@1
      displayName: 'Deploying the database to Azure'
      inputs:
        connectedServiceNameARM: $(connectedServiceNameARM)
        ServerName: $(Server_tst)
        DatabaseName: $(DatabaseName_tst)
        SqlUsername: $(DatabaseUser_tst)
        SqlPassword: $(DatabasePassword_tst)
        DacpacFile: $(Pipeline.Workspace)/database/a/$(dacpacfile_location)
        PublishProfile: $(Pipeline.Workspace)/database/a/$(publishprofile_location)