Asked by:
How to display two result sets for two queries

Question
-
The below is two queries for SQL Server where each runs and displays fine in Powershell when run as a single query setup in $query, but when I try to run and display both as shown below, only the first result is displayed. The second result set just shows as empty space in the display panel. I thought the last two lines in the script should display both result sets? Thanks for any advice.
$query = @"
select * from t1
select * from t2
"@
$SqlCmd.CommandText = $query
$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]
$DataSet.Tables[1]Thursday, December 7, 2017 3:08 PM
All replies
-
As I posted before,
$dataset.Load($reader)
https://stackoverflow.com/questions/11345761/how-to-fill-dataset-with-multiple-tables
\_(ツ)_/
Thursday, December 7, 2017 4:29 PM -
Any chance you could advise more specifically. I am newbie on DataSets and Powershell.
Thanks
Thursday, December 7, 2017 4:49 PM -
Use the C# code as a template for writing your code. You need to use a reader to "Load" the dataset.
$reader = $cmd.ExecuteReader()
$dataset.Load($reader)\_(ツ)_/
Thursday, December 7, 2017 5:05 PM -
Here is a more complete example:
$query = @' select * from contacts select * from testtext '@ $connStr = 'Data Source=alpha\sqlexpress;Initial Catalog=LocalTest;Integrated Security=SSPI;' $conn = New-Object System.Data.SqlClient.SqlConnection($connStr) $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = $query $ds = New-Object System.Data.DataSet $rdr = $cmd.ExecuteReader() $ds.Load($rdr, [System.Data.LoadOption]::OverwriteChanges, [string[]]@('Contacts', 'testtext')) $conn.Close() $ds.Tables['Contacts'] $ds.Tables['testtext']
\_(ツ)_/
- Edited by jrv Thursday, December 7, 2017 5:59 PM
Thursday, December 7, 2017 5:59 PM