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

VB.NET Debug Visualizer for Guids for Visual Studio 2010

Every once in a while I must program in VB.NET. My largest frustration is how Visual Studio handles Guids while debugging VB.NET code.

Seeing "System.Guid" as the value for a variable that contains a Guid is really not helpful. Debugging in C# correctly displays the actual value in the same situation.

Visual Studio provides an avenue to rectify this issue. You can create a new class library project and replace the contents of the default class file with the below code snippet.

<Assembly: DebuggerDisplay("{ToString()}", Target:=GetType(Guid))>

Public Class EmptyGuidVisualizerClass
    ' Empty
End Class

After compiling, copy the DLL to "%USERPROFILE%\My Documents\Visual Studio 2010\Visualizers" and restart Visual Studio. The Guids should resolve to their actual values.

After installing this in Visual Studio 2010, I found that it also works in Visual Studio 2008. The DLL should, likewise, be placed in the Visualizers directory in the Visual Studio 2008 folder in My Documents.

I created a batch file that can be used to install the DLL for 2010 and 2008. The batch file and the DLL must be in the same directory.

@ECHO OFF
SET STARTDIR=%CD%

pushd .

cd "%USERPROFILE%\My Documents"

IF EXIST ".\Visual Studio 2008" (
pushd .
echo "2008 Exists!"
cd ".\Visual Studio 2008"

IF NOT EXIST ".\Visualizers" (
 echo "Creating Visualizers Directory."
 mkdir Visualizers
)

copy "%STARTDIR%\GuidVisualizer.dll" .\Visualizers

popd
)

IF EXIST ".\Visual Studio 2010" (
pushd .
echo "2010 Exists!"
cd ".\Visual Studio 2010"

IF NOT EXIST ".\Visualizers" (
 echo "Creating Visualizers Directory."
 mkdir Visualizers
)

copy "%STARTDIR%\GuidVisualizer.dll" .\Visualizers

popd
)

popd

After creating it, I googled and found someone who came up with almost the same code, http://www.michelrenaud.com/?p=7 . This blog post contains pictures if you care to see the end result. Microsoft Connect has a similar resolved issue: https://connect.microsoft.com/VisualStudio/feedback/details/89801/show-guid-value-in-debug.