Invoke-SqlCommand.ps1

function Invoke-SqlCommand{
    <#
        .SYNOPSIS
            Invoke SQL command(s) to a database server.
        .DESCRIPTION
            This function will take a SQL query and a database connection and rund the SQL query/commands and optionally return data as a DataTable object.
        .EXAMPLES
            Invoke-SqlCommand -Query $myQuery -DBConnection $myDbConnection
            This will run the query in $myQuery using the connection object in $myDbConnection. No data will be retuned.
        .EXAMPLES
            $myData = Invoke-SqlCommand -Query $myQuery -DBConnection $myDBConnection -GetResults
            This will run the query in $myQuery using the connection object in $myDbConnection and save the returned data in $myData.
        .NOTES
            Author: Øyvind Kallstad
            Date: 05.02.2015
            Version: 1.0
    #>
    param(
        [Parameter(Position = 0, Mandatory = $true)]
        [string]$Query,
 
        [Parameter(Position = 1, Mandatory = $true, ValueFromPipeline = $true)]
        [System.Data.Common.DBConnection]$DbConnection,
 
        [Parameter()]
        [int]$Timeout = 30,
 
        [Parameter()]
        [switch]$GetResults = $false
    )
 
    try{
        $command = $DbConnection.CreateCommand()
        $command.CommandText = $Query
        $command.CommandTimeout = $Timeout
 
        if ($GetResults){
            $dbData = New-Object -TypeName 'System.Data.DataTable'
 
            switch($DbConnection.GetType().Name){
                'OracleConnection' {$dataAdapter = New-Object -TypeName 'Oracle.DataAccess.Client.OracleDataAdapter' -ArgumentList $command}
                'SqlConnection'    {$dataAdapter = New-Object -TypeName 'System.Data.SqlClient.SqlDataAdapter' -ArgumentList $command}
                'OdbcConnection'   {$dataAdapter = New-Object -TypeName 'System.Data.Odbc.OdbcDataAdapter' -ArgumentList $command}
                'MySqlConnection'  {$dataAdapter = New-Object -TypeName 'MySql.Data.MySqlClient.MySqlDataAdapter' -ArgumentList $command}
                default {
                    $dataAdapter = $null
                    Write-Warning -Message "Database connection type '$($DbConnection.GetType().Name)' is not supported by this function"
                }
            }
 
            if ($dataAdapter){
                [void]$dataAdapter.Fill($dbData)
                Write-Output (,($dbData))
            }
        }
        else{
            [void]$command.ExecuteNonQuery()
        }
    }
    catch{
        Write-Warning "At line:$($_.InvocationInfo.ScriptLineNumber) char:$($_.InvocationInfo.OffsetInLine) Command:$($_.InvocationInfo.InvocationName), Exception: '$($_.Exception.Message.Trim())'"
    }
}