CODEWITHSUNDEEP
sqlpowershelldatatable
nectar
nectar

Reputation: 9679

how to loop through datatable in powershell?

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?

Upvotes: 2

Views: 27269

Answers (1)

Peter M.
Peter M.

Reputation: 856

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])"

Upvotes: 1

Related Questions