FlowSoft.DevOps.psm1

Function Add-EnvironmentPieplineVariable {
    param
    ( 
        [Parameter(Mandatory = $true)]
        [string] $PieplineName,
        [Parameter(Mandatory = $true)]
        [string] $EnvironmentName,
        [Parameter(Mandatory = $true)]
        [string] $VariableValue,
        [Parameter(Mandatory = $true)]
        [string] $VariableKey
    )
   
    Write-Host "ensure release definition variable PieplineName:$PieplineName EnvironmentName:$EnvironmentName VariableValue:$VariableValue VariableKey:$VariableKey"
    $apiUrl = "$($env:SYSTEM_TEAMFOUNDATIONSERVERURI)/$($env:SYSTEM_TEAMPROJECTID)/_apis"
    $url = "$apiUrl/release/definitions?api-version=5.1"

    $body = Invoke-RestMethod -Method GET -Uri $url -Headers @{
        Authorization = "Bearer $env:SYSTEM_ACCESSTOKEN"
    }
    $definitionId = (($body.value | Where-Object { $_.name -eq $PieplineName } | Select-Object -First 1).Id)
    $definitionId = "$definitionId"

    $url = "$apiUrl/release/definitions/$($definitionId)?api-version=5.1"

    $body = Invoke-RestMethod -Method GET -Uri $url -Headers @{
        Authorization = "Bearer $env:SYSTEM_ACCESSTOKEN"
    }

    $environment = $body.environments | Where-Object { $_.name -eq "$EnvironmentName" } | Select-Object -First 1
        
    if ($null -eq $environment.variables.$VariableKey) {
        Write-Output "add variable: $VariableKey"
        $environment.variables | Add-Member -NotePropertyName $VariableKey -NotePropertyValue $VariableValue
    }

    $environment.variables.$VariableKey = [PSCustomObject]@{value = "$VariableValue" }
    $body = ($body | ConvertTo-Json -Depth 100)
    Invoke-RestMethod -Uri $url -Method Put -Body $body -ContentType "application/json" -Headers @{Authorization = "Bearer $env:SYSTEM_ACCESSTOKEN" } | Out-Null

    Write-Host "completed!"
}

Function Get-EnvironmentPieplineVariable {
    param
    ( 
        [Parameter(Mandatory = $true)]
        [string] $PieplineName,
        [Parameter(Mandatory = $true)]
        [string] $EnvironmentName,
        [Parameter(Mandatory = $true)]
        [string] $VariableKey
    )
   
    $apiUrl = "$($env:SYSTEM_TEAMFOUNDATIONSERVERURI)/$($env:SYSTEM_TEAMPROJECTID)/_apis"
    $url = "$apiUrl/release/definitions?api-version=5.1"

    $body = Invoke-RestMethod -Method GET -Uri $url -Headers @{
        Authorization = "Bearer $env:SYSTEM_ACCESSTOKEN"
    }
    $definitionId = (($body.value | Where-Object { $_.name -eq $PieplineName } | Select-Object -First 1).Id)
    $definitionId = "$definitionId"

    $url = "$apiUrl/release/definitions/$($definitionId)?api-version=5.1"

    $body = Invoke-RestMethod -Method GET -Uri $url -Headers @{
        Authorization = "Bearer $env:SYSTEM_ACCESSTOKEN"
    }

    $environment = $body.environments | Where-Object { $_.name -eq "$EnvironmentName" } | Select-Object -First 1
    $variableValue = $environment.variables.$VariableKey.value
    Write-Host "get release definition variable PieplineName:$PieplineName EnvironmentName:$EnvironmentName VariableValue:$variableValue VariableKey:$VariableKey"
    return $variableValue
}


Function Invoke-SQL {
    param
    ( 
        [Parameter(Mandatory = $true)]
        [string] $SqlConnectionString,
        [Parameter(Mandatory = $true)]
        [string] $Query
    )
   
    Write-Host "execute sql query:$query"

    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = $SqlConnectionString
    try {
        $Connection.Open()
    }
    catch {
        throw "Error opening SQL Connection: $($Error[0].Exception)"
    }
        
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.CommandTimeout = 240
    $Command.Connection = $Connection
    $Command.CommandText = $Query
    
    $DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $Command
    $Dataset = new-object System.Data.Dataset
    $DataAdapter.Fill($Dataset) | Out-Null
    $Connection.Close()
    return $Dataset;
}