Skip to content

Instantly share code, notes, and snippets.

@dfch
Created October 19, 2014 18:38
Show Gist options
  • Save dfch/ee8ac6e4995988edc1e2 to your computer and use it in GitHub Desktop.
Save dfch/ee8ac6e4995988edc1e2 to your computer and use it in GitHub Desktop.
[biz.dfch.PS.System.Data] Invoke-SqlCmd without the overhead
# [biz.dfch.PS.System.Data] Invoke-SqlCmd without the overhead
# http://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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment