internal/functions/get-sqlparametervalue.ps1


<#
    .SYNOPSIS
        Get the value from the parameter
         
    .DESCRIPTION
        Get the value that is assigned to the SqlParameter object
         
    .PARAMETER SqlParameter
        The SqlParameter object that you want to work against
         
    .EXAMPLE
        PS C:\> $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        PS C:\> $SqlCmd.Parameters.AddWithValue("@Parm1", "1234")
        PS C:\> Get-SqlParameterValue -SqlParameter $SqlCmd.Parameters[0]
         
        This will extract the value from the first parameter from the SqlCommand object.
         
    .NOTES
        Author: Mötz Jensen (@Splaxi)
         
#>

function Get-SqlParameterValue {
    [CmdletBinding()]
    [OutputType('System.String')]
    param (
        [System.Data.SqlClient.SqlParameter] $SqlParameter
    )

    $result = $null

    $stringEscaped = @(
        [System.Data.SqlDbType]::Char,
        [System.Data.SqlDbType]::DateTime,
        [System.Data.SqlDbType]::NChar,
        [System.Data.SqlDbType]::NText,
        [System.Data.SqlDbType]::NVarChar,
        [System.Data.SqlDbType]::Text,
        [System.Data.SqlDbType]::VarChar,
        [System.Data.SqlDbType]::Xml,
        [System.Data.SqlDbType]::Date,
        [System.Data.SqlDbType]::Time,
        [System.Data.SqlDbType]::DateTime2,
        [System.Data.SqlDbType]::DateTimeOffset
    )
    
    $stringNumbers = @([System.Data.SqlDbType]::Float, [System.Data.SqlDbType]::Decimal)
    
    switch ($SqlParameter.SqlDbType) {
        { $stringEscaped -contains $_ } {
            $result = "'{0}'" -f $SqlParameter.Value.ToString().Replace("'", "''")
            break
        }

        { [System.Data.SqlDbType]::Bit } {
            if ((ConvertTo-BooleanOrDefault -Object $SqlParameter.Value.ToString() -Default $true)) {
                $result = '1'
            }
            else {
                $result = '0'
            }
                        
            break
        }
        
        { $stringNumbers -contains $_ } {
            $SqlParameter.Value
            $result = ([System.Double]$SqlParameter.Value).ToString([System.Globalization.CultureInfo]::InvariantCulture).Replace("'", "''")
            break
        }

        default {
            $result = $SqlParameter.Value.ToString().Replace("'", "''")
            break
        }
    }

    $result
}