Check if System.Object[] contains a value (Updated)

This isn’t the most interesting of blog posts admittedly. But I was using the SQL Server Management Objects (SSMO) in my PowerShell script to return a result set from a stored procedure in SQL server. The data set returned from the Invoke-sqlcmd command was a System.Object[] type, and this blog explains how to check if System.Object[] contains a value.

Check if System.Object[] contains a value

I wanted to see if a column (the Department column) in the result set contained a specific value. So firstly I obtained the result set like so:

# Load the SQL Server SMO library
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 
Import-Module "sqlps" -DisableNameChecking
#SQL Server
$SQLServer = "ALKANESQLSERVER"
#Database
$SQLDatabase = "ALKANESQLDATABASE"
#Return results from stored procedure
$sql_departments = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query 'exec [dbo].[ALKANESTOREDPROCEDURE]'

In PowerShell 3 it was a trivial exercise:

if ($sql_departments."Department" -contains "AlkaneDepartment")
{
}

however in PowerShell 2 it required slightly more legwork, and was a little slower to run:

if (($sql_departments | Select -ExpandProperty Department) -contains "AlkaneDepartment")
{
}

To provide another example, consider we’re iterating through some local profile folders and we want to see if it contains a folder contains a certain name. We can run:

$localprofiles = get-childitem c:\Users

and check which properties it contains by running:

$localprofiles | Select *

From this, we can see that the property we want to check is called name, so we simply check (using PowerShell 3+) by writing:

if ($localprofiles.Name -contains "profilename") {
#do something
}

Easy!