Your PowerShell code never does anything unexpected, right? Yea, mine doesn't either, but I'm sure there are some of you out there with this problem. If so, this post is for you!

Have you ever had a time when your code generates some transient state? I'm talking about the code that generates temporary files, creates a temporary variable, opens a database connection for a limited time and so on? Whatever the case, it's just temporary, and you'd like to tear it all down before exiting the script or function.

Let's say that you're opening up a connection to a SQL database to perform some queries. To do this, you might have some code like this:

## Create the connection object
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = 'SQLSERVER'; Database = 'MYDB'; Integrated Security = True"

## Build the SQL command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = 'SELET * FROM SomeTable'
$SqlCmd.Connection = $SqlConnection

## Execute the query
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

## Return the dataset
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0]

## Close the connection
$SqlConnection.Close()

This code is perfectly fine and will work (if everything goes to plan). In this code, you're assuming quite a bit and have no error handling. For example, what if SQLSSERVER is offline? What if MYDB doesn't exist? What if your SELECT query is off?

Some unexpected outcomes can occur here. You need to account for all of these issues through proper error handling. But error handling is a big topic, and I'm only here to talk about one way to better handle unexpected errors; ensuring cleanup code runs regardless if it runs successfully or not.

As a first step, a good idea is to wrap this up in a try catch block. This way if any exceptions are thrown, you will be able to catch that exception and handle it according. For this example, I'd just like to write the exception message out to the error stream.

try
{
� � ## Create the connection object
� � $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
� � $SqlConnection.ConnectionString = "Server = 'SQLSERVER'; Database = 'MYDB'; Integrated Security = True"

� � ## Build the SQL command
� � $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
� � $SqlCmd.CommandText = 'SELET * FROM SomeTable'
� � $SqlCmd.Connection = $SqlConnection

� � ## Execute the query
� � $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
� � $SqlAdapter.SelectCommand = $SqlCmd

� � ## Return the dataset
� � $DataSet = New-Object System.Data.DataSet
� � $SqlAdapter.Fill($DataSet)
� � $DataSet.Tables[0]

� � ## Close the connection
� � $SqlConnection.Close()
}
catch
{
� � Write-Error $_.Exception.Message
}

This is a great first step! But, it's still not the best. Notice the line where I'm closing the SQL connection. This is at the very bottom of the code. Regardless of what happens, I always want to make sure that connection is closed.

But what if an exception is through while I'm returning the dataset? PowerShell will stop execution and shift directly to the catch block. This will completely skip this step and leave an open database connection open. The only time it will work is if everything works flawlessly.

You're assuming too much here. We need a way to ensure we use the try/catch block for error handling but also need to make sure that database connection is closed.

This is where the less often used finally block comes into play. The finally block is another block that is right after the catch block that executes code regardless if an error occurs or not. Any code in the finally block will always execute if an exception is thrown or not in the try block.

This eliminates the need to assume the code will work correctly. This is a perfect place to put code that you want to execute no matter what. In our case, I want to ensure that database connection is closed.

This means our code will now turn to this:

try
{
    ## Create the connection object
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = 'SQLSERVER'; Database = 'MYDB'; Integrated Security = True"
 
    ## Build the SQL command
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = 'SELET * FROM SomeTable'
    $SqlCmd.Connection = $SqlConnection

    ## Execute the query
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd

    ## Return the dataset
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $DataSet.Tables[0]
} 
catch 
{
    Write-Error $_.Exception.Message
}
finally 
{
    ## Close the connection
    $SqlConnection.Close()
}

If you test this, you will see that the connection will be closed every time. Use this approach for any instance in your code where you need to ensure some piece of code is executed regardless if an exception is thrown or not.

Join the Jar Tippers on Patreon

It takes a lot of time to write detailed blog posts like this one. In a single-income family, this blog is one way I depend on to keep the lights on. I'd be eternally grateful if you could become a Patreon patron today!

Become a Patron!