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
}
