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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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())'" | |
| } | |
| } |
Communary 