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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

"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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

"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();