Read multiple tables from dataset in Powershell

Question

I am using a function that collects data from a SQL server:

function Invoke-SQLCommand {
param(
    [string] $dataSource = "myserver",
 [string] $dbName = "mydatabase",
 [string] $sqlCommand = $(throw "Please specify a query.")
)


$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$dataSource;Database=$dbName;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

}

It works great but returns only one table. I am passing several Select statements, so the dataset contains multiple tables.

I replaced

$DataSet.Tables[0]

with

for ($i=0;$i -lt $DataSet.tables.count;$i++){
    $Dataset.Tables[$i]
    }

but the console only shows the content of the first table and blank lines for each records of what should be the second table. The only way to see the result is to change the code to

$Dataset.Tables[$i] | out-string

but I do not want strings, I want to have table objects to work with.

When I assign what is returned by the Invoke-SQLCommand to a variable, I can see that I have an array of datarow objects but only from the first table. What happened to the second table?

Any help would be greatly appreciated.

Thanks

Answer 1

I tried your function (that returns $DataSet.Tables) and it worked pretty well for me. This command returned rows from both tables:

$t = Invoke-sqlcommand '.\sql2005' 'AdventureWorksDW' "select * from DimOrganization; select * from DimSalesReason"
$t[0]  #returns rows from first table
$t[1]  #returns rows from second table

---

Anyway, what I would recommend:

First I would discard output from Fill:

$SqlAdapter.Fill($DataSet) > $null

It is returned as well, but that's not probably desired.

As in your case Invoke-SqlCommand doesn't work, I would try to return 1 dim array like this:

function Invoke-SQLCommand {
   ...
   ,$DataSet.Tables
}

Consider that PowerShell treats DataTable specially and when trying to format it, it unravels Rows collection (credits to x0n). That's why just executing $t from my example displays all the rows returned from the command.

Answer 2

Thank you for your answer.

Well, I can't explain why it works for you and not for me.

If I run the exact same command as you (except for the data source, mysqlserver\sqlexpress in my case), $t[0] only returns the first row of the first table and $t[1] the second row.

What seems to be happening in my case is that the rows from all tables are merged so I end up with one big set of datarows, not the individual tables expected.

I ended replacing:

for ($i=0;$i -lt $DataSet.tables.count;$i++){
    $Dataset.Tables[$i]
    }

with just

$Dataset

I can then reference the individual tables from my script by using $t.Tables[0] and $t.Tables[1].

Thanks again