=== 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 {
LogError $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’.

1 Comment »

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.