Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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())'"
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment