Read multiple tables from dataset in Powershell
- by Lucas
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
