PowerShell - ForEach using SQL data input

Question

I am using powershell code to first connect on a database and from the SELECT I am making, this output is then output :

NAME: %SERVERNAME1
NAME: %SERVERNAME2

Now I want to make a "foreach" loop where it will make a "get-childitem" on every %SERVERNAME to find every EXE files and output to out-gridview.

My foreach look like this but obviously it doesn't work :

$Connection.open()
Write-host "Database Connection $databasename = SUCCESSFULL : Searching role $Role in $palier ..." -foregroundcolor green -backgroundcolor black
$SqlCmd.CommandText = $SqlQuery
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$SqlCmd.Connection = $Connection
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($Dataset)
$Connection.Close()
$Result = $Dataset.Tables[0]
$Result


Foreach ($name in $Result) 
{

$name = Get-ChildItem "\\$_\c$\" -recurse -filter *.exe

}

How this foreach could be done ?

P.S. : Didn't want to add to much info since the database connection is working, just want to understand where I am failing on the foreach side.

alroc · Accepted Answer · 2014-04-17 15:42:30Z

1

$Result is a collection of data rows, and foreach can't use $_ (that's only for pipelined objects). You need to loop through the results and grab the Name field from each row.

In your loop, you're overwriting $name on each iteration, so you'll only output the results of the last Get-ChildItem. You need to collect those results for output.

$ExeList = @();
Foreach ($Record in $Result) {
    $ExeList += Get-ChildItem -path \\$($Record.name)\c$\ -recurse -filter *.exe
}
$ExeList|out-gridview;

answered Apr 17, 2014 at 15:42

alroc

28.3k6 gold badges55 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Philippe Massé Over a year ago

It works like a charm ! Could you explain to me theses two parameters you've used : "@()" and "\\$($Record.name) " ? Why uses the dollar sign before the record name ?

alroc Over a year ago

@() creates an empty array to stuff the results of GCI into. $($Results.name) is how you get variable expansion for properties within a quoted string (yes, I realize I didn't quote it here - force of habit).

Collectives™ on Stack Overflow

PowerShell - ForEach using SQL data input

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related