Inconsistent DataTable Row Count for Single-Row Query Results

Question

I'm experiencing an issue with a PowerShell function that retrieves data from an database. The function works correctly in PowerShell v7.4 but behaves inconsistently in PowerShell v5.1 when the query returns a single row.

Here is the function:

function Get-Data ($connectstring, $sql) {
   # Create a new ODBC Connection
   $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($connectstring)
   $OLEDBConn.open()
   
   # Create a new ODBC Command
   $readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)
   $readcmd.CommandTimeout = '300'
   
   # Create a DataAdapter and fill the DataTable
   $da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
   $dt = New-Object system.Data.datatable

   [void]$da.fill($dt)

   $OLEDBConn.close()

   return $dt
}

Issue:

When executing QUERY 1:

$qry= "SELECT * FROM backups" (which returns 3 rows)
$dtres = Get-Data $connString $qry 
$dtres.Count

Both PowerShell v5.1 (ISE) and v7.4 (Visual Code) correctly return a count of 3.

When executing QUERY 2:

$qry= "SELECT 'test'" (which should return 1 row)
$dtres = Get-Data $connString $qry 
$dtres.Count

PowerShell v5.1 (ISE) returns $null PowerShell v7.4 (Visual Code) returns 1

Then, I searched for other properties of the datatable '$dt' and I have found the property '$dt.Rows.Count' (inside the Get-Data function) and it returned the correct number of rows (1) regardless of the PS version.

However, when i tried to get the number of rows using '$dtres.Rows.Count':

$qry= "SELECT 'test'" (which should return 1 row)
$dtres = Get-Data $connString $qry 
$dtres.Rows.Count

On both versions the value returned was 0.

Is there a workaround to correctly return the number of rows in PowerShell v5.1 when the query result is a single row?

Although it doesn't explain the behavior of $dt.Count in 5.1, it's worth noting that $dtres is not a DataTable in either case - PowerShell enumerates the rows of data tables the same way it does arrays or lists. Change return $dt to return Write-Output $dt -NoEnumerate to fix that (at which point the count issue won't occur anymore) — Mathias R. Jessen
– Mathias R. Jessen, Commented Apr 17 at 19:10

mklement0 · Accepted Answer · 2025-04-19 21:50:08Z

tl;dr

Due to PowerShell's auto-enumeration behavior (see next section), what your Get-Data function outputs is not a [System.Data.DataTable], but a stream of [System.Data.DataRow] instances that results from the enumeration of the former's .Rows property.
A bug in Windows PowerShell (the legacy, ships-with-Windows, Windows-only edition of PowerShell whose latest and last version is 5.1) causes .Count to mistakenly evaluate to $null if only one [System.Data.DataRow] happens to be output.
$dtres.Rows.Count predictably and correctly yields 0, because $dtRes has no .Rows property (because it isn't a data table), so your expression is equivalent to $null.Count, which is always 0 (due to use of the intrinsic .Count property - see next section).
To get the desired behavior in both PowerShell editions:
- Make sure that Get-Data outputs the [System.Data.DataTable] itself, by replacing
  return $dt with return , $dt (or return Write-Output -NoEnumerate $dt)
- Then, on the captured result, which is then a [System.Data.DataTable] instance, use .Rows.Count to query the number of rows ($dtRes.Rows.Count), not $dtRes.Count.
  - $dtres.Count, assuming that $dtRes is a [System.Data.DataTable] instance, arguably should be the same as .Rows.Count or should yield 1, if you conceive of a table as a single object, but does not, due to another bug that affects both PowerShell editions, i.e. also PowerShell (Core) 7, as of v7.5.0:
    - In Windows PowerShell, it unexpectedly yields $null (irrespective of the number of rows in the table).
    - In PowerShell (Core) 7, it unexpectedly performs member-access enumeration on the rows (the elements of the .Rows collection), (uselessly) yielding one or more 1 values, one for each row - see GitHub issue #6466.

Background information

Outputting ("returning") from a function an instance of a type that PowerShell considers enumerable^[1] causes auto-enumeration: that is, it isn't the instance itself that is output, but the elements that result from its enumeration, which are streamed to the pipeline one by one.
- PowerShell has custom logic that considers instances of System.Data.DataTable enumerable (even though said type does not implement the IEnumerable interface) and auto-enumerates the elements of its .Rows collection, which are instances of type System.Data.DataRow
- Therefore, your Get-Data function can situationally output 0, 1, or multiple [System.Data.DataRow]s.
- If you want to prevent auto-enumeration, i.e. if you want to output the [System.Data.DataTable] itself, replace return $dt with return , $dt or, more verbosely, but conceptually more clearly, return Write-Output -NoEnumerate $dt.
  - For more information, including an explanation of the concise form with the unary form of the , operator, see this answer.
  - Also note that use the return keyword is optional - it is never needed to produce output, and only necessary for unconditionally exiting a scope - see this answer.
Applying .Count to command output uses three different mechanisms, depending on the number of output objects and what properties / interfaces they implement:
- 0 or 1 instances:
  - The intrinsic .Count property is used, which is a virtual property provided by PowerShell designed to harmonize the processing of collections (enumerables) and scalars (single objects): it is designed to reflect 0 for no output, and 1 for a scalar (single) object, i.e. to also make "nothing" and "one object" countable like collections.
    - 0 output objects, i.e. no output, are represented in one of two ways in PowerShell and it follows from the above that .Count reports 0 in both cases:
      - $null in the context of expressions (e.g., $noSuchVariable)
        
        That is, $null.Count yields 0
      - The enumerable null ([System.Management.Automation.Internal.AutomationNull]::Value) in the context of command output; this special value behaves like $null in expression contexts, and like an empty array in the pipeline (i.e., it sends no objects through it).
        
        That is, (& {}).Count yields 0
        (& {}, i.e. execution of an empty script block ({ ... }), is the most concise way to output an enumerable null).
    - 1 output object, assuming that it is a scalar, i.e. represents a single thing rather than a collection / enumeration of other things, sensibly causes .Count to report 1.
      - That is, something like (42).Count yields 1 and, given that none of the exceptions below apply, applying .Count on a single instance of the [System.Data.Row] type should yield 1 too, but doesn't, due to a bug in Windows PowerShell that has since been fixed in PowerShell (Core) 7
      - By contrast, if a given object itself has a .Count property (which typically means that the object is enumerable), it is used.
      - Otherwise, if a given object is enumerable^[1] yet has no .Count property (which is not very common), member-access enumeration is applied, which means that the .Count property access is applied to each enumerated element, with the resulting counts collected in the same manner as multi-object output from a command (see next point).
- 2 or more instances:
  - When output from a command is collected, such as by assignment to a variable or via (...), the grouping operator, PowerShell of necessity creates a collection-like data structure if there are two or more objects, specifically, an array of type [object[]].
  - Since arrays do have a type-native .Count property - via their implementation of the System.Collections.ICollection interface, which PowerShell implicitly surfaces - the .Count property value is the count of array elements (irrespective of the type of the individual elements).

^{[1] To summarize the logic as to which types PowerShell considers enumerable: Specifically, types that implement the IEnumerable interface or its generic counterpart are automatically enumerated, but there are a few exceptions: strings, dictionaries (types implementing IDictionary or its generic counterpart), and System.Xml.XmlNode instances. Additionally, as discussed, System.Data.DataTable is enumerated too, despite not implementing IEnumerable itself, via its .Rows property.
See this answer for details.}

Collectives™ on Stack Overflow

Inconsistent DataTable Row Count for Single-Row Query Results

1 Answer 1

Background information

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Background information

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related