[PowerShell] Using .Contains with System.Data.DataRow

I realised I hadn’t done a blog post this week and didn’t think I had anything planned, so here is a random PowerShell/SQL Server encounter on Twitter. Hope you enjoy


Recently a question came up on the #sqlhelp hashtag on Twitter asking about a problem that a user was having with using .Contains with an array.

Normally when I see a question regarding PowerShell, Arrays, and Contains I keep an eye on them, not to answer them but to read the responses and learn from them.

However, this one caught my eye for two reasons; it had an image with Invoke-Sqlcmd in it , and it was on the #sqlhelp hashtag. So I said let’s see if I can help out here.

The Question…

… was if you have a table like below…

TableCapture
a…b…c…d…e…f…NULL

and you are running the following PowerShell command to check if the results contain a value…


$String = "abc"
$Array = @(Invoke-Sqlcmd -ServerInstance "SQLServer" -Database "Database" -Query "SELECT code FROM dbo.users")
$Array.Contains($string)

 

InitialQuery
liar!!!

It will return FALSE.

Now we know that the FALSE is false because we know that the string is in there!
This code is proven to work with arrays as stated here by the “Hey, Scripting Guy!”s so this was getting filed under “WTF PowerShell”

The mistake they made…

… and I’ve done the same so I can’t blame them, was they failed to use Get-Member; they made assumptions (bad idea)

If they had run $array | gm, they would have seen that the $array is not an array but a System.Data.DataRow, and we’ve seen them before.

The mistake I made…

…was running $array | gm and seeing that there was no method called .Contains.

ArrayGM
Does not contain Contains

So I was going down the route of using a mix of foreach and -eq.

This wouldn’t have been great though as we would have to iterate over every single row and seeing if the value existed. I assumed that the reason the questioner wanted to use .Contains was to get around iterating over every single row, so this wasn’t going to work either.

What we both should have done…

…was use Get-Member.

The Questioner should have used $Array | gm and I, knowing the next step, should have used $Array.code | gm

ArrayCodeGM
2 TYPES!!!

It’s nice to see the way that NULLs are treated differently in PowerShell. 🙂

If we drop down from the DataRow into the property of the DataRow, it becomes a string! Perfect because the string contains the method .Contain.

And we have our answer…


$Array.code.Contains($String);

BeautifulOneLiner

…and we have our True.

PowerShell…beautiful!

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

One thought on “[PowerShell] Using .Contains with System.Data.DataRow”

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s