Using SQL Transactions in Powershell

It occurred to me that I have not seen a write up on System.Transactions.TransactionScope usage in powershell. I have tried this on Powershell v2, not sure if this works in Powershell 1. So here it goes...

The example below just shows the usage, I leave it as an exercise to the reader to add in the database query code.

"Start. " + [DateTime]::Now().ToString();

try {
 $transScope = New-Object System.Transactions.TransactionScope;

 ## Insert Queries

 "Complete";
 $transScope.Complete();
}
catch [Exception] {
    #$_ | fl * -Force
 Write-Host $_.Exception.ToString();
}
finally {
 if ($transScope) {
  $transScope.Dispose();
 }
}

"Done. " + [DateTime]::Now().ToString();

A more complex example just waiting for SQL statements. It shows some nested transactions.

"Start. " + [DateTime]::Now().ToString();

try {
 $transScope = New-Object System.Transactions.TransactionScope;
 
 ## Insert Queries
 
 try {
  ## Performs queries that are not included in a transaction.
  $transScope2 = New-Object System.Transactions.TransactionScope([System.Transactions.TransactionScopeOption]::Suppress);
  
  ## Insert Queries
  
  $transScope2.Complete();
 }
 finally {
  if ($transScope2) {
   $transScope2.Dispose();
  }
 }
 
 ## Insert Queries

 try {
  ## Perform actions that should be included in a side transaction outside of the enclosing transaction
  $transScope3 = New-Object System.Transactions.TransactionScope([System.Transactions.TransactionScopeOption]::RequiresNew);
  
  ## Insert Queries
  
  $transScope3.Complete();
 }
 finally {
  if ($transScope3) {
   $transScope3.Dispose();
  }
 }
 
 ## Insert Queries
 
 "Complete";
 $transScope.Complete();
}
catch [Exception] {
    #$_ | fl * -Force
 Write-Host $_.Exception.ToString();
}
finally {
 if ($transScope) {
  $transScope.Dispose();
 }
}

"Done. " + [DateTime]::Now().ToString();