Private/Get-SQLData.ps1

Function Get-SQLData {
    param (
        [parameter(Mandatory)] $sqlConn,
        [parameter(Mandatory)][ValidateNotNullOrEmpty()][string] $SQLQuery,
        [parameter()][string] $FileName,
        [parameter()][string] $TableName,
        [parameter()][string] $SiteCode,
        [parameter()][int] $NumberOfDays,
        [parameter()] $LogFile,
        [parameter()][string] $ServerName,
        [parameter()][bool] $ContinueOnError = $true,
        [parameter()] $HealthCheck,
        [parameter()] $Section,
        [parameter()][switch] $Detailed
    )
    Write-Log -Message "(Get-SQLData): Table = $TableName"
    if ($Detailed) { 
        Write-Log -Message " [detailed = True]" -LogFile $logfile
    }
    try {
        $SqlCommand = $sqlConn.CreateCommand()
        $logQuery       = Set-ReplaceString -value $SQLQuery -SiteCode $SiteCode -NumberOfDays $NumberOfDays -ServerName $ServerName
        $executionquery = Set-ReplaceString -value $SQLQuery -SiteCode $SiteCode -NumberOfDays $NumberOfDays -ServerName $ServerName -space $false
        Write-Log -Message "SQL Query...`n$executionquery" -LogFile $logfile
        Write-Log -Message "Log Query...`n$logQuery" -LogFile $logfile
        $SqlCommand.CommandTimeOut = 0
        $SqlCommand.CommandText = $executionquery
        $DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlCommand
        $dataset     = New-Object System.Data.Dataset
        $DataAdapter.Fill($dataset)
        if (($dataset.Tables.Count -eq 0) -or ($dataset.Tables[0].Rows.Count -eq 0)) { 
            Write-Log -Message "SQL Query returned 0 records" -LogFile $logfile
            Write-Log -Message "Table $tablename is empty. No file output to $filename ..." -LogFile $logfile
        }
        else {
            Write-Log -Message "SQL Query returned $($dataset.Tables[0].Rows.Count) records"
            foreach ($field in $healthCheck.Fields.Field) {
                Write-Log -Message (" field = $($Field.FieldName) description = $($Field.Description)") -LogFile $logfile
                if ($section -eq 5) {
                    if (($detailed) -and ($field.groupby -notin ('1','2'))) { continue }
                    elseif (($detailed -eq $false) -and ($field.groupby -notin ('2','3'))) { continue }
                }
                if (![string]::IsNullOrEmpty($field.format)) {
                    Write-Log -Message " custom format specified for this attribute: $($Field.Format)" -LogFile $logfile
                    foreach ($row in $dataset.Tables[0].Rows) {
                        $tempx = Set-FormattedValue -Value $row.$($field.FieldName) -Format $field.format -SiteCode $SiteCode
                        try {
                            $row.$($field.FieldName) = $tempx
                        }
                        catch {
                            $row
                            break
                        }
                    }
                }
            }
            Write-Log -Message "Export: Exporting xml data to $filename" -LogFile $logfile
            , $dataset.Tables[0] | Export-CliXml -Path $filename
        }
    }
    catch {
        $errorMessage = $Error[0].Exception.Message
        $errorCode = "0x{0:X}" -f $Error[0].Exception.ErrorCode
        if ($continueonerror -eq $false) { 
            Write-Log -Message "ERROR/EXCEPTION: The following error occurred (stop)." -Severity 3 -LogFile $logfile
        }
        else { 
            Write-Log -Message "ERROR/EXCEPTION: The following error occurred (continue)." -Severity 3 -LogFile $logfile
        }
        Write-Log -Message "Error $errorCode : $errorMessage connecting to $ServerName" -Severity 3 -LogFile $logfile
        $Error.Clear()
        Write-Log -Message "Unable to update file: $filename" -Severity 2 -LogFile $logfile
        if ($continueonerror -eq $false) {
            Throw "Error $errorCode : $errorMessage connecting to $ServerName"
        }
    }
}