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?

Boyish

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.

Islas

So why on nearly identical non queries, and queries, am I not seeing this being emitted to the output? – Boyish

Without seeing more, it's hard to say. It may have more to do with the fact that it's the last one in the file. Can you post any more? – Islas

I'll test it out tomorrow at the office by reordering the functions in the file, if it stops for that call, and start for the one now at the bottom, I'll be wondering what's broken past that. – Boyish

The first option ended up solving the problem. It also took 10 minutes off my processing time. – Boyish

So glad this answer was here and thanks for the great explanation of two ways of handling it. – Cystine