locked
How to display two result sets for two queries RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    Any chance you could advise more specifically.  I am newbie on DataSets and Powershell.  

    Thanks

    Thursday, December 7, 2017 4:49 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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