PowerShell reading SQL - read multiple rows

Question

I have successfully read a single returned value but can't quite get multiples.

My query is something like

select value 
from table 
where a in ('here', 'there').

In SQL Server, I see two rows being returned:

'first'
'second'

My script does the following:

$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()
$rc = $result.Read()

I can't seem to find the right way to see that there are two rows (I assume there are rows rather than columns because this is how it shows in SQL) or what the second value is; I can see the first by executing:

$fnum = $result.hasrows

if ($fnum -eq "True") {
    $fname =  $result.GetValue(0)
    #write-host $fname
} else {
    write-host "parm not found"
    exit 5
}

So $fname does have a value (first). $result.count shows '1 1' like there are two values. I tried doing a $result.getvalue(1) and tried doing another read and then getvalue. Neither works.

How do I get the second value and how do I see that there are two?

I didn't format the sql return correctly. 'first' and 'second' show as two ROWS not columns. — dmataz
– dmataz, Commented Aug 29, 2024 at 15:08
What are you trying to do with these rows, exactly? Just output the dataset to the user? — Thom A
– Thom A ♦, Commented Aug 29, 2024 at 15:12
If you want to read the data from the Reader, you need to loop through that reader. One way would be to use while($result.Read()){...}. You don't make clear what you want to do here. — Thom A
– Thom A ♦, Commented Aug 29, 2024 at 15:33
On a side note, consider using DbaTools module and the Invoke-DbaQuery cmdlet, which does most of this for you. — Charlieface
– Charlieface, Commented Aug 29, 2024 at 15:38
HasRows returns a boolean, meaning the comparison against as string (-eq "True") is questionable, see also: stackoverflow.com/a/78869703/1701026 — iRon
– iRon, Commented Aug 29, 2024 at 15:52

Joel Coehoorn · Accepted Answer · 2024-08-30 13:33:36Z

When you use a DataReader, the .Read() function gives you a boolean result telling you if the read was successful. You keep calling .Read() again in a loop as long the result is true:

$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()
while ($result.Read())
{
    $fname =  $result.GetValue(0)
    write-host $fname
}

I can't seem to find the right way to see that there are two rows
... how do I see that there are two?

For DataReaders, there is no final count of rows yet, because a reader is designed to allow you to process many many rows... possibly billions... as soon as the first row is available, without waiting for the last row and hence possibly before the final row count is even known for sure. In fact, it's even possible to implement your own DataReader type (inhering from DbDataReader) that never ends, and would loop forever. So instead of how many rows, the .Count property on the reader is telling you how many columns are in the current row.

Getting a final count or rows for a database query would require the server to have the last row available, which can have very bad performance implications for large result sets, effectively requiring the server to hold the entire large result set in memory instead of just buffering a few rows as it streams results and requiring your application to wait for all of these rows to be available before processing any of them. It can also make the database hold onto locks for longer, impacting other queries as well.

If you need the count up front, and can't wait until you can process the rows and take your own count, you have to load the results into a DataSet or DataTable. And this is fine for small to medium result sets.

Additionally, for PowerShell, we can take advantage of the Pipeline to set reader values into a variable:

$command = $connection.CreateCommand()
$command.CommandText = $query

$rdr= $command.ExecuteReader()
$result = while ($rdr.Read())
{
    $result.GetValue(0) # put it on the pipeline    
}

write-host $result

Collectives™ on Stack Overflow

PowerShell reading SQL - read multiple rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related