Invoke-SqlCommand

In several previous posts I have presented functions to create different kinds of database connections. But a connection to a database is just the first step. The function I’m presenting here will let you use the connection object you get from those functions and send SQL commands to the database. If you expect data back you can indicate that by using the GetResults parameter, and you will get a DataTable object returned to you.

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())'"
}
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s