Adding parameters to SQLCommand and adding value later

I've seen the different discussions of using Parameters.Add vs Parameters.AddWithValue (one is explicit, the other is not), but that isn't my issue.

I'm defining a command, which will be used to do multiple inserts into the database. I figured I'd do something like the following:

[define command]
[add parameters (including a batchid not known at this time)]

[enter loop]
   [create new batchid]
   [set parameter value]
   [execute command]
[next]

This would mean my parameters are neatly defined in advance, and I set their values within the loop. The code for that looks like this:

$SqlCmdAdd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdAdd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmdAdd.CommandText = "AddBatch"
$SqlCmdAdd.Parameters.Add("@RunId", [Data.SQLDBType]::UniqueIdentifier)
$SqlCmdAdd.Parameters.Add("@RunType", [Data.SQLDBType]::NVarChar, 50)
$SqlCmdAdd.Connection = $SqlConnection

Within the loop I figured I could do something like this:

# Generate GUID to be used as a BatchID
$batchID = [guid]::NewGuid()

# I tried both using the parameter name, as well as their numeric order. Neither worked
$SqlCmdAdd.Parameters["@RunId"].Value = $batchID | Out-Null
$SqlCmdAdd.Parameters["@RunType"].Value = "HDD" | Out-Null
# $SqlCmdAdd.Parameters[0].SqlValue = $batchID | Out-Null
# $SqlCmdAdd.Parameters[1].SqlValue = "HDD" | Out-Null

However, if I read out the values of $SqlCmdAdd.Parameters[0] and [1], I see the proper names and data types, but their values are null. Thus, I'll get an error that no values were specified for the parameters.

What did work was:

$SqlCmdAdd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmdAdd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmdAdd.CommandText = "AddBatch"
$SqlCmdAdd.Connection = $SqlConnection

Within the loop doing this:

# Add parameters
$SqlCmdAdd.Parameters.AddWithValue("@RunId", $batchID) | Out-Null
$SqlCmdAdd.Parameters.AddWithValue("@RunType", "HDD") | Out-Null
$SqlCmdAdd.ExecuteNonQuery()
$SqlConnection.Close()

# Clear parameter collection for next run
$SqlCmdAdd.Parameters.Clear()

What irritates me about this is the need to clear the parameter collection for the next run, and then recreating them. While that runs my code, I'm interested in finding out why attempting to set the value of the earlier created parameters in the first code block doesn't have any effect.

I tried googling and read countless articles.

This happens, as piping the variable to Out-Null happens before assignment. Thus,

$SqlCmdAdd.Parameters["@RunId"].Value = $batchID | Out-Null

Will first pass $batchID to Out-Null, and the result - $null - is assigned into .Value.

As for a fix, just remove Out-Null like so,

$SqlCmdAdd.Parameters["@RunId"].Value = $batchID