Check row existence in SQL Server with PowerShell

Question

Actually I am dealing for my first time with PowerShell scripting.

By looking for a good solution in order to perform a SELECT on SQL Server, I found the code as follows:

Function IdentityProviderExistsDatabase {
    param(
        [string] $IdPIdentifier
    )

    $SQLQuery = $("SELECT [*] FROM [dbo].[IdPs] WHERE [IdPIdentifier] = '$IdPIdentifier'")

    $SQLConnect = new-object system.data.sqlclient.sqlconnection  
    $SQLConnect.connectionstring = $SQLConnectionString

    $SQLConnect.Open()  

    $command = New-object system.data.sqlclient.SqlCommand   
    $command.connection = $SQLConnect  
    $command.CommandText = $SQLQuery
    $Reader = $command.ExecuteReader()
    while ($Reader.Read()) {
        $Reader.GetValue($1)
    }
}

The solution above works fine, but really I don't need to read any value; for my purposes, I'd just know if the query returns something, thus just if the record exists or not according to the WHERE clause.

Is there a way to perform this operation in a more elegant way than by checking the returned values inside the loop?

Answer 1

Use ExecuteScalar() method. As per documentation,

Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database.

Sample usage:

$SQLQuery = "SELECT count(*) FROM..."
$SQLConnect = new-object system.data.sqlclient.sqlconnection(...)
$SQLConnect.Open()  
$command = New-object system.data.sqlclient.SqlCommand   
$command.connection = $SQLConnect  
$command.CommandText = $SQLQuery
$scalar = $command.ExecuteScalar() # The select's result is stored in $scalar
$command.Dispose()
$SQLConnect.Dispose()