[biz.dfch.PS.System.Data] Invoke-SqlCmd without the overhead

Invoke-SqlCmd.ps1

# [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;