PSCI

1.1.0

baseModules/PPoShSqlTools/0.1.5/Private/Invoke-SqlDotNet.ps1

function Invoke-SqlDotNet {
    <#
    .SYNOPSIS
    Runs a T-SQL script using .NET SqlCommand.
    
    .DESCRIPTION
    Useful especially when neither SMO nor sqlcmd are available.
    
    .EXAMPLE
    Invoke-SqlDotNet -ConnectionString $connectionString -Sql $Query -SqlCmdVariables $param
    #> 

    [CmdletBinding()] 
    [OutputType([object])]
    param( 
        [Parameter(Mandatory=$true)] 
        [object]
        $ConnectionStringBuilder, 
    
        [Parameter(Mandatory=$false)] 
        [string]
        $Query,
        
        [Parameter(Mandatory=$false)] 
        [bool]
        $IgnoreErrors,
    
        [Parameter(Mandatory=$true)] 
        [int]
        $QueryTimeoutInSeconds,

        [Parameter(Mandatory=$true)] 
        [int]
        $ConnectTimeoutInSeconds,

        [Parameter(Mandatory=$false)]
        [ValidateSet($null, 'NonQuery', 'Scalar', 'Dataset')]
        [string]
        $Mode = 'Dataset',
    
        [Parameter(Mandatory=$false)] 
        [hashtable]
        $SqlCmdVariables,

        [Parameter(Mandatory=$false)]
        [Data.SqlClient.SqlParameter[]] 
        $SqlParameters,

        [Parameter(Mandatory=$false)]
        [PSCredential] 
        $Credential
    ) 

    #TODO: handle $Credential

    # Replace SqlCmdVariables in $Query
    if ($SqlCmdVariables) {
        foreach ($var in $SqlCmdVariables.GetEnumerator()) {
            $regex = '\$\({0}\)' -f $var.Key
            if (!$var.Value) {
                $value = ''
            } 
            else {
                $value = $var.Value
            }
            Write-Log -_Debug "Key: $($var.Key), value: $($var.Value)"
            $Query = [System.Text.RegularExpressions.Regex]::Replace($Query, $regex, $value, `
                        [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)
        }
    }

    # Split queries per each 'GO' instance - see http://stackoverflow.com/questions/18596876/go-statements-blowing-up-sql-execution-in-net/18597052#18597052
    $queriesSplit = [System.Text.RegularExpressions.Regex]::Split($Query, '^\s*GO\s* ($ | \-\- .*$)', `
        [System.Text.RegularExpressions.RegexOptions]::Multiline -bor `
        [System.Text.RegularExpressions.RegexOptions]::IgnorePatternWhitespace -bor `
        [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)

    $queriesSplit = $queriesSplit | Where-Object { ![System.String]::IsNullOrWhiteSpace($_) }

    $ConnectionStringBuilder.set_ConnectTimeout($ConnectTimeoutInSeconds)


    try { 
        $connection = New-Object -TypeName System.Data.SqlClient.SQLConnection -ArgumentList ($ConnectionStringBuilder.ToString())
        $connection.FireInfoMessageEventOnUserErrors = $true
        $errorOccurred = @{ Error = $false }
        $infoEventHandler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { 
            foreach ($err in $_.Errors) { 
                if ($err.Class -le 10) { 
                    Write-Log -Info $err.Message
                } 
                else { 
                    Write-Log -Error $err.Message
                    if (!$IgnoreErrors) {
                        $errorOccurred.Error = $true
                    }
                }
            }    
        } 
        $connection.add_InfoMessage($infoEventHandler)
        $connection.Open()

        foreach ($q in $queriesSplit) { 
            $command = New-Object -TypeName System.Data.SqlClient.SqlCommand -ArgumentList $q, $connection
            $command.CommandTimeout = $QueryTimeoutInSeconds

            if ($SqlParameters) {
                foreach ($sqlParam in $SqlParameters) {
                    Write-Log -_Debug "Key: $($sqlParam.ParameterName), value: $($sqlParam.Valuee)"
                    [void]($command.Parameters.Add($sqlParam))
                }
            }

            if ($Mode -eq 'Dataset') { 
                $dataset = New-Object -TypeName System.Data.DataSet 
                $dataAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter -ArgumentList $command
            
                [void]$dataAdapter.fill($dataset) 

                $dataset
            } 
            elseif ($Mode -eq 'NonQuery') {
                [void]($command.ExecuteNonQuery())
            } 
            elseif ($Mode -eq 'Scalar') {
                $command.ExecuteScalar()
            } 
            else {
                throw "Unsupported mode: ${Mode}."
            }

            if ($errorOccurred.Error) {
                throw "SQL error(s) occurred."
            }
        }
    
        $connection.Close();
    } finally {
        if ($connection) {
            $connection.Dispose();
        }
    }  
}