=== UPDATE 2014-10-19 === I created a separate module that uses the SqlClient where you do not have to specify the command type and where you can also use LocalDB connections. ===
For the full module and description see my Post Module: biz.dfch.PS.System.Data.
=== UPDATE 2013-12-21 === added an additional parameter to specify the command type manually ===
Sometimes we have to access data from a (SQL) database from PowerShell script but do not want to install the management tools from SQL Server just to get the ‘SqlServerCmdletSnapin100’ snapin to use ‘Invoke-SqlCmd’. However we created enough scripts that got used to the syntax of Microsoft’s Cmdlet. So here we are and present a ‘clone’ to the original ‘Invoke-SqlCmd’. We actually use it as a module so we can define default parameters within the module configuration file (that means you can ignore the default values in the ‘PARAM’ block). However, standalone it is just as good to use …
| # [biz.dfch.PS.System.Data] Invoke-SqlCmd without the overhead | |
| # https://d-fens.ch/2013/11/26/biz-dfch-ps-system-data-invoke-sqlcmd-without-the-overhead | |
| function Invoke-SqlCmd { | |
| [CmdletBinding( | |
| SupportsShouldProcess=$true, | |
| ConfirmImpact="Medium", | |
| HelpURI='http://dfch.biz/PS/System/Data/Invoke-SqlCmd/' | |
| )] | |
| Param ( | |
| [Parameter(Mandatory = $false)] | |
| [string] $ServerInstance = $biz_dfch_PS_System_Data.ServerInstance | |
| , | |
| [Parameter(Mandatory = $false)] | |
| [string] $Database = $biz_dfch_PS_System_Data.Database | |
| , | |
| [Parameter(Mandatory = $true, Position = 0)] | |
| [string] $Query | |
| , | |
| [Parameter(Mandatory = $false, ParameterSetName = 'plain')] | |
| [string] $Username = $biz_dfch_PS_System_Data.Username | |
| , | |
| [Parameter(Mandatory = $false, ParameterSetName = 'plain')] | |
| [string] $Password = $biz_dfch_PS_System_Data.Password | |
| , | |
| [ValidateSet('SELECT', 'INSERT', 'UPDATE', 'DELETE')] | |
| [Parameter(Mandatory = $false)] | |
| [string] $CommandType = 'SELECT' | |
| ) # Param | |
| BEGIN { | |
| $datBegin = [datetime]::Now; | |
| [string] $fn = $MyInvocation.MyCommand.Name; | |
| } # BEGIN | |
| PROCESS { | |
| # Default test variable for checking function response codes. | |
| [Boolean] $fReturn = $false; | |
| # Return values are always and only returned via OutputParameter. | |
| $OutputParameter = $null; | |
| try { | |
| $builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder; | |
| $SqlConnection = New-Object System.Data.SqlClient.SqlConnection; | |
| $SqlCmd = New-Object System.Data.SqlClient.SqlCommand; | |
| $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter; | |
| $DataSet = New-Object System.Data.DataSet; | |
| $builder.'Data Source' = $ServerInstance; | |
| $builder.'Initial Catalog' = $Database; | |
| if($PsCmdlet.ParameterSetName -eq 'cred') { | |
| $Username = $Credential.Username | |
| $Password = $Credential.GetNetworkCredential().Password; | |
| } # if | |
| if(!$Username -Or !$Password) { | |
| $builder.'Integrated Security' = $True; | |
| $builder.'User ID' = $ENV:USERNAME; | |
| } else { | |
| $builder.'Integrated Security' = $False; | |
| $builder.'User ID' = $Username; | |
| $builder.Password = $Password; | |
| } # if | |
| $SqlConnection.ConnectionString = $builder.ConnectionString; | |
| $SqlConnection.Open(); | |
| $SqlCmd.CommandText = $Query; | |
| $SqlCmd.Connection = $SqlConnection; | |
| if(!$PSBoundParameters.ContainsKey('CommandType')) { | |
| $fReturn = $Query -match '(\w+)'; | |
| if(!$fReturn) { | |
| $CommandType = 'SELECT'; | |
| } else { | |
| $fReturn = $false; | |
| $CommandType = $Matches[1]; | |
| } # if | |
| } # if | |
| switch($CommandType) { | |
| 'INSERT' { $SqlAdapter.InsertCommand = $SqlCmd; } | |
| 'UPDATE' { $SqlAdapter.UpdateCommand = $SqlCmd; } | |
| 'DELETE' { $SqlAdapter.DeleteCommand = $SqlCmd; } | |
| default { $SqlAdapter.SelectCommand = $SqlCmd; } | |
| } # switch | |
| switch($CommandType) { | |
| 'INSERT' { $n = $SqlCmd.ExecuteNonQuery(); $fReturn = $true; $OutputParameter = $n; } | |
| 'UPDATE' { $n = $SqlCmd.ExecuteNonQuery(); $fReturn = $true; $OutputParameter = $n; } | |
| 'DELETE' { $n = $SqlCmd.ExecuteNonQuery(); $fReturn = $true; $OutputParameter = $n; } | |
| default { | |
| $n = $SqlAdapter.Fill($DataSet); | |
| $rs = $null; | |
| if($n -And $DataSet.Tables.Rows) { | |
| $rs = $DataSet.Tables.Rows | | |
| Select-Object ($DataSet.Tables.Rows | | |
| gm –Type Properties).Name; | |
| } # if | |
| $fReturn = $true; | |
| $OutputParameter = $rs; | |
| } | |
| } # switch | |
| } # try | |
| catch { | |
| if($gotoSuccess -eq $_.Exception.Message) { | |
| $fReturn = $true; | |
| } else { | |
| [string] $ErrorText = "catch [$($_.FullyQualifiedErrorId)]"; | |
| $ErrorText += (($_ | fl * –Force) | Out-String); | |
| $ErrorText += (($_.Exception | fl * –Force) | Out-String); | |
| $ErrorText += (Get-PSCallStack | Out-String); | |
| if($_.Exception.InnerException -is [System.Data.SqlClient.SqlException]) { | |
| Write-Host ("[SqlException] ClientConnectionId: '{0}'. {1}.`n[{2}]" -f | |
| $_.Exception.InnerException.ClientConnectionId, | |
| $_.Exception.InnerException.Message, $_); | |
| Write-Error $ErrorText; | |
| } # [System.Net.WebException] | |
| else { | |
| Log–Error $fn $ErrorText –fac 3; | |
| if($gotoError -eq $_.Exception.Message) { | |
| Write-Error $e.Exception.Message; | |
| $PSCmdlet.ThrowTerminatingError($e); | |
| } elseif($gotoFailure -ne $_.Exception.Message) { | |
| Write-Verbose ("$fn`n$ErrorText"); | |
| } else { | |
| # N/A | |
| } # if | |
| } # other exceptions | |
| $fReturn = $false; | |
| $OutputParameter = $null; | |
| } # !$gotoSuccess | |
| } # catch | |
| finally { | |
| # Clean up | |
| if($DataSet) { $DataSet.Dispose(); } | |
| if(Test-Path variable:DataSet) { Remove-Variable DataSet; } | |
| if($SqlAdapter) { $SqlAdapter.Dispose(); } | |
| if(Test-Path variable:SqlAdapter) { Remove-Variable SqlAdapter; } | |
| if($SqlCmd) { $SqlCmd.Dispose(); } | |
| if(Test-Path variable:SqlCmd) { Remove-Variable SqlCmd }; | |
| if($SqlConnection) { $SqlConnection.Close(); } | |
| if($SqlConnection) { $SqlConnection.Dispose(); } | |
| if(Test-Path variable:SqlConnection) { Remove-Variable SqlConnection; } | |
| if(Test-Path variable:builder) { Remove-Variable builder; } | |
| } # finally | |
| # Return values are always and only returned via OutputParameter. | |
| return $OutputParameter; | |
| } # PROCESS | |
| END { | |
| $datEnd = [datetime]::Now; | |
| } # END | |
| } # function | |
| Export-ModuleMember –Function Invoke-SqlCmd; |
See Gist at https://gist.github.com/dfch/ee8ac6e4995988edc1e2
Logging has mainly been removed from the function code. In addition, the returned result set is actually an array of PSCustomObjects and the order of the properties might change from the original one defined in the query or the table.
Note: if you have installed both, the PSSnapin from Microsoft and this module, calling ‘Invoke-SqlCmd’ by default will use the module as this gets implicitly loaded in PowerShell 3.0, whereas the PSSnapin has to be loaded explicitly via ‘Add-PSSnapin’.
Ronald Rink
I am a senior auditor and consultant at d-fens for business processes and information systems.
1 Comment »