how to loop through datatable in powershell?

Question

I am getting different errors while fetching data from datatable. Here is my code to fill datatable:

$dt = New-Object System.Data.DataTable
$dt.Columns.Add("ServiceName")
$dt.Columns.Add("HostName")

$SQLConnection.Open()
$Reader = $SqlCmd.ExecuteReader()
while ($Reader.Read()) {
    $dt.Rows.Add($Reader.GetValue(0), $Reader.GetValue(1))
}
$SqlConnection.Close()
return $dt

Above code is in a function which returns datatable and when I access this returned datatable in calling script I don't get the data.

Script to fetch data on each row:

foreach ($Row in $BackupData) {
    Write-Host "$Row[0]"
    Write-Host "$Row[1]"
}

I am getting output like:

ServiceName[0]
HostName[0]
System.Data.DataRow[0]
System.Data.DataRow[0]
System.Data.DataRow[0]
System.Data.DataRow[0]

And when I use "$($Row[0])" I get another error

unable to index an object of type system.Data.DataColumn

How can I get the data of each row and column?

How about just $BackupData, or $BackupData | Format-Table -AutoSize? — Ansgar Wiechers
– Ansgar Wiechers, Commented Sep 20, 2016 at 10:10
foreach ($row in $dt) { write-host $row[0] write-host $row[1] } this works if do this in the function where the data is being filled but does not work with the same datatable returned from that function. — nectar
– nectar, Commented Sep 20, 2016 at 10:14
Which version of SQL are you using? There will be aPSSnapin or Module that can make this much easier. — G42
– G42, Commented Sep 20, 2016 at 11:07

Peter M. · Accepted Answer · 2016-12-01 16:45:36Z

1

It's much easier to fill a DataTable with the help of a SqlDataAdapter like so:

$dt = New-Object System.Data.DataTable
$da = New-Object System.Data.SqlClient.SqlDataAdapter -ArgumentList $ConnectionString, $SqlQuery
$da.fill($dt)

So you don't have to take care of opening and closing a connection and using a SqlDataReader either.

All the rows are now part of the DataTable object $dt and can be accessed either with the row index and a specific field name

$dt[0].FieldName

where "FieldName" is the name of a field of that table. Or without having to know the name of a specific field

$dt.Row[0}[0]

The resason for the error is that you have to use a sub expression with Write-Host like so:

Write-Host "value of first field: $($Row[0])"

answered Dec 1, 2016 at 16:45

Peter M.

9481 gold badge8 silver badges19 bronze badges

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

2 Comments

Caltor Over a year ago

$dt.Row[0}[0] That can't be right - can it? Should it be $dt.Row[0][0] ?

Peter M. Over a year ago

Yes, $dt.Row[0][0], of course - first index for the row and the second for the field (thanks for pointing that out)

Collectives™ on Stack Overflow

how to loop through datatable in powershell?

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