How to stop PowerShell SqlCommand from echoing parameters

Question

I have a few functions that use the SqlCommand object to to data inserts, and queries. But one function (the last one in the file) seems to echo (most of) the attributes into the output. The function in question:

function Add-DataStudentChangeEvent($person,
        $key,
        $currentValue,
        $newValue,
        $eventType){

    $cmdEvent=New-Object System.Data.SqlClient.SqlCommand
    $cmdEvent.Connection = $conn
    $cmdEvent.CommandTimeout = 600000

    $cmdEvent.CommandText = "INSERT INTO ChangeEvent
            (AttributeKey
            ,CurrentAttributeValue
            ,NewAttributeValue
            ,EventType
            ,EventDate
            ,CompletedStatus
            ,Person_Id)
        VALUES
            (@AttributeKey,
            @CurrentAttributeValue,
            @NewAttributeValue,
            @EventType,
            GETDATE(),
            0,
            @PersonId);" -F

    $cmdEvent.Parameters.AddWithValue("@AttributeKey", $key); 
    $cmdEvent.Parameters.AddWithValue("@CurrentAttributeValue", $current);
    $cmdEvent.Parameters.AddWithValue("@NewAttributeValue", $updateTo);
    $cmdEvent.Parameters.AddWithValue("@EventType", $eventType);
    $cmdEvent.Parameters.AddWithValue("@PersonId", $person);

    $cmdEvent.ExecuteNonQuery()
}

In another similar function using parameterized queries I found that adding the -F at the end, stops the echo. And I did say most of the parameters. Only 4 of the 5 actually display. This is the output I'm getting:

1
CompareInfo                     : None
XmlSchemaCollectionDatabase     : 
XmlSchemaCollectionOwningSchema : 
XmlSchemaCollectionName         : 
ForceColumnEncryption           : False
DbType                          : String
LocaleId                        : 0
ParameterName                   : @CurrentAttributeValue
Precision                       : 0
Scale                           : 0
SqlDbType                       : NVarChar
SqlValue                        : Null
UdtTypeName                     : 
TypeName                        : 
Value                           : 
Direction                       : Input
IsNullable                      : False
Offset                          : 0
Size                            : 0
SourceColumn                    : 
SourceColumnNullMapping         : False
SourceVersion                   : Current

CompareInfo                     : None
XmlSchemaCollectionDatabase     : 
XmlSchemaCollectionOwningSchema : 
XmlSchemaCollectionName         : 
ForceColumnEncryption           : False
DbType                          : String
LocaleId                        : 0
ParameterName                   : @NewAttributeValue
Precision                       : 0
Scale                           : 0
SqlDbType                       : NVarChar
SqlValue                        : 2007-11-15
UdtTypeName                     : 
TypeName                        : 
Value                           : 2007-11-15
Direction                       : Input
IsNullable                      : False
Offset                          : 0
Size                            : 10
SourceColumn                    : 
SourceColumnNullMapping         : False
SourceVersion                   : Current

CompareInfo                     : None
XmlSchemaCollectionDatabase     : 
XmlSchemaCollectionOwningSchema : 
XmlSchemaCollectionName         : 
ForceColumnEncryption           : False
DbType                          : Int32
LocaleId                        : 0
ParameterName                   : @EventType
Precision                       : 0
Scale                           : 0
SqlDbType                       : Int
SqlValue                        : 1
UdtTypeName                     : 
TypeName                        : 
Value                           : 1
Direction                       : Input
IsNullable                      : False
Offset                          : 0
Size                            : 0
SourceColumn                    : 
SourceColumnNullMapping         : False
SourceVersion                   : Current

CompareInfo                     : None
XmlSchemaCollectionDatabase     : 
XmlSchemaCollectionOwningSchema : 
XmlSchemaCollectionName         : 
ForceColumnEncryption           : False
DbType                          : Int32
LocaleId                        : 0
ParameterName                   : @PersonId
Precision                       : 0
Scale                           : 0
SqlDbType                       : Int
SqlValue                        : 72
UdtTypeName                     : 
TypeName                        : 
Value                           : 72
Direction                       : Input
IsNullable                      : False
Offset                          : 0
Size                            : 0
SourceColumn                    : 
SourceColumnNullMapping         : False
SourceVersion                   : Current

Any ideas on how I can stop the echo?

Answer 1

What you are seeing are return values from functions. For example, SqlParameterCollection.AddWithValue() returns a SqlParameter, and SqlCommand.ExecuteNonQuery() returns an int. Unlike C#, any values that you don't explicitly deal with, will be written to the output stream.

There are two ways to deal with it. The first way is to assign the return values to a variable:

$param = $cmdEvent.Parameters.AddWithValue("@AttributeKey", $key)
$param = $cmdEvent.Parameters.AddWithValue("@CurrentAttributeValue", $current)
$param = $cmdEvent.Parameters.AddWithValue("@NewAttributeValue", $updateTo)
$param = $cmdEvent.Parameters.AddWithValue("@EventType", $eventType)
$param = $cmdEvent.Parameters.AddWithValue("@PersonId", $person)

$numRows = $cmdEvent.ExecuteNonQuery()

The second way is to pipe the output to the null stream:

$cmdEvent.Parameters.AddWithValue("@AttributeKey", $key) | Out-Null 
$cmdEvent.Parameters.AddWithValue("@CurrentAttributeValue", $current) | Out-Null
$cmdEvent.Parameters.AddWithValue("@NewAttributeValue", $updateTo) | Out-Null
$cmdEvent.Parameters.AddWithValue("@EventType", $eventType) | Out-Null
$cmdEvent.Parameters.AddWithValue("@PersonId", $person) | Out-Null

$cmdEvent.ExecuteNonQuery() | Out-Null

I prefer the first method, because you may run into a case where you want to make use of the return value. The second method, however, more clearly shows your intent that you are not interested in the return value.