Ending Up with Extra Null Values in ArrayList from SQL Reader

Question

In short I am pulling data from a SQl stored procedure into an ArrayList of a custom class and I am somehow ending up with extra null items. One for each item pulled back, so if there are 15 items returned from the SQL stored procedure, I end up with an array of 30. Any help figuring out why I am getting extra items, or how to remove them would be greatly appreciated.

Function Execute-Procedure
{
    Param
    (
        [Parameter(Mandatory=$true)][string]$parameter,
        [Parameter(Mandatory=$true)] 
        [System.Data.SqlClient.SqlConnection]$sqlconnection
    )
Process
{
 $items = [System.Collections.ArrayList]@()
 $cmd = New-Object System.Data.SqlClient.SqlCommand
 $cmd.Connection = $sqlconnection
 $cmd.CommandTimeout = 0
 $cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
 $cmd.CommandText = "My_Stored_Procedure"
 $cmd.Parameters.AddWithValue("@PARAMETER",$parameter) | Out-Null
 $reader = $cmd.ExecuteReader()
 while($reader.Read())
 {
      $currentItem = [CustomClass]::new()
      $currentItem.property1 = $reader.GetValue(0)
      $currentItem.property2 = $reader.GetValue(3)
      $items.Add($currentItem)
 }
 $cmd.Dispose() | Out-Null
 $reader.Dispose()
 return $items
}
}

...

$items = Execute-Procedure $parameter $sqlconnection
$itemCount = $items.Count
"Item count: $itemCount"
foreach($item in $items)
{
     $p1 = $item.property1
     $p2 = $item.property2
     "Property1: $p1 - Property2: $p2"
}

Some sample output:

Item count: 30
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1:  - property2: 
property1: 1111 - property2: XXXX                                           
property1: 1111 - property2: XXXX                                  
property1: 1111 - property2: XXXX                                                
property1: 1111 - property2: XXXX                                 
property1: 1111 - property2: XXXX                                            
property1: 1111 - property2: XXXX                                        
property1: 1111 - property2: XXXX                                                   
property1: 1111 - property2: XXXX                                               
property1: 1111 - property2: XXXX                                                        
property1: 1111 - property2: XXXX                                      
property1: 1111 - property2: XXXX                                      
property1: 1111 - property2: XXXX                
property1: 1111 - property2: XXXX                           
property1: 1111 - property2: XXXX                      
property1: 1111 - property2: XXXX

You keep updating the same instance of [CustomClass] over and over again. You'll want to create a new instance every time inside the while loop — Mathias R. Jessen
– Mathias R. Jessen, Commented Jul 29, 2020 at 13:57
Welcome to stackoverflow - you should always include a capture of the output to help people. — Mirv - Matt
– Mirv - Matt, Commented Jul 29, 2020 at 14:01
@MathiasR.Jessen I tried that, and I'm still getting the same results. — Chris Alston
– Chris Alston, Commented Jul 29, 2020 at 14:07
Just to get it right - $items.Count is 30 even though you only expect 15 items based on the query - and 15 of the items in the array list are $null? Every other? The last 15? The first 15? — Mathias R. Jessen
– Mathias R. Jessen, Commented Jul 29, 2020 at 14:11

Mathias R. Jessen · Accepted Answer · 2020-07-29 14:32:18Z

1

You need to suppress the output from `ArrayList.Add()`

When you call Add() on an [ArrayList], it returns the index at which the object was inserted.

Since PowerShell gladly surfaces any output to the caller, your function output ends up consisting of the numbers 0 through 14 and then the 15 objects - and dereferencing "property1" on an integer just results in $null

Solving it is as simple as:

$null = $items.Add($currentItem)
# or
[void]$items.Add($currentItem)
# or 
$items.Add($currentItem) |Out-Null

... inside the while loop

answered Jul 29, 2020 at 14:32

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Ending Up with Extra Null Values in ArrayList from SQL Reader

1 Answer 1

You need to suppress the output from `ArrayList.Add()`

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

You need to suppress the output from ArrayList.Add()

Comments

Your Answer

Sign up or log in

Post as a guest

Related

You need to suppress the output from `ArrayList.Add()`