SqlClient.ExecuteNonQuery not returning number of rows

Question

I have been dealing with this for a while now. I have a simple PowerShell function that takes an open connection to an SQL Server and a String containing an SQL command and runs it against the server. The function should return an integer value representing the number of rows that were updated. However, it is outputting information about the method instead.

Code:

function Invoke-MSSQLNonQuery () {
    param (
        [System.Data.SqlClient.SqlConnection]$Connection,
        [string]$Command
    )

    # Create the SQL Command Ojbect
    $SQLCommand = New-Object System.Data.SqlClient.SqlCommand

    # Set the Command Text and Connection for the Command
    $SQLCommand.CommandText=$Command
    $SQLCommand.Connection=$Connection

    # Run command and return the rows affected
    [int]($SQLCommand.ExecuteNonQuery | Out-String)
}

However, upon execution, I get the following error:

Cannot convert value "
OverloadDefinitions
-------------------
int ExecuteNonQuery()
int IDbCommand.ExecuteNonQuery()

" to type "System.Int32". Error: "Input string was not in a correct format."
At C:\_Local\playground\Mason\UpdateFromInventory.ps1:153 char:5
+     [int]($SQLCommand.ExecuteNonQuery | Out-String)
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvalidCastFromStringToInteger

I know that ExecuteNonQuery, when piped to Out-String, is outputting a table with it's overload definitions:

OverloadDefinitions
------------------- 
int ExecuteNonQuery() 
int IDbCommand.ExecuteNonQuery()

I have also tried not piping it to anything, which returned a similar result without the table header.

Edit: Also, I should mention that I am 95% sure that the SQL Commands are executing successfully. I know for certain that this function has worked once before, even returning correct output. However, that has not been the case for a long time.

My Questions are:

1. Why?
2. How can I get it to output the number of rows affected instead?

Answer 1

You're trying to invoke a method without parentheses. When doing that, instead of actually invoking the method the statement will display a list of the overloads (see for instance here), which cannot be cast to an integer, even if you convert it to a string first.

Change

[int]($SQLCommand.ExecuteNonQuery | Out-String)

into

[int]$SQLCommand.ExecuteNonQuery()

and the code should do what you want.