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.
… was if you have a table like below…
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")
It will return
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…
$array | gm and seeing that there was no method called
So I was going down the route of using a mix of
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…
The Questioner should have used
$Array | gm and I, knowing the next step, should have used
$Array.code | gm
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
And we have our answer…
…and we have our