Strange occurrence when returning Datatable from a function

I was trying to retrieve some data from a SQL Server database using Powershel using the following function:

function GetLocationData($Name)
{
$SqlConnection = New-Object system.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=ServerName;Database=MyDB;User Id=dbuser;Password=pwd;"

$SqlCommandText = "SELECT Field1, Field2 FROM TableName WHERE [Name]='" + $Name + "'"
$SqlConnection.Open()

$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$Reader = $SqlCmd.ExecuteReader()

$Datatable = New-Object System.Data.DataTable 
$DataTable.Load($Reader)  

# Close the database connection 
$sqlConnection.Close() 

return $Datatable
}

When I would call it from my script

$dt = New-Object System.Data.DataTable
$dt=GetLocationData $item.name 
Write-Host "row count is " +  $dt.Rows.Count

It would not return anything and if I tried accessing a field

Write-Host $dt.Rows[0].Field1

I would get an error "Cannot index into null array"


The issue is that function would be returning a child collection of the table object instead of the table itself. What helped me is using the comma operator to wrap the rows collection in an array so that when the array is unrolled leaving me with the original rows collection.

So all I had to do to fix this issue is put a comma in the return statement right before datatable object:

function GetLocationData($Name)
{
$SqlConnection = New-Object system.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=ServerName;Database=MyDB;User Id=dbuser;Password=pwd;"


$SqlCommandText = "SELECT Field1, Field2 FROM TableName WHERE [Name]='" + $Name + "'"
$SqlConnection.Open()

$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$Reader = $SqlCmd.ExecuteReader()

$Datatable = New-Object System.Data.DataTable 
$DataTable.Load($Reader)  

# Close the database connection 
$sqlConnection.Close() 

return ,$Datatable
}