CODEWITHSUNDEEP
powershellpowershell-2.0
Ken
Ken

Reputation: 1031

Powershell - If SQL query returns a result, then run function

Within PowerShell, I'm trying to trigger an email to be sent only if there are any results from a SQL query. Email functionality and DB connection is working fine, but the "if then" statement based on SQL results isn't working - I don't think the $result = [bool] is valid.

End result would be the email only being sent if there are any records returned from the SQL statement, and the email would contain the SQL results.

Here's what I have so far:

$result = [bool]("
                SELECT *
                FROM table
                WHERE condition")

If ($result -eq $true) {

function Invoke-SQL ($dataset) {

    $connectionString = "server=servername;uid=valuehere;pwd=passwordhere;database=dbnamehere;"
    $sqlCommand = "
                SELECT *
                FROM table
                WHERE condition"
                    "

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $adapter.Fill($dataSet) #| Out-Null

    $connection.Close()

}


function Invoke-Email ($dataset) {
    foreach ($Row in $dataset.Tables[0].Rows)
    { 
      write-host "value is : $($Row[0])"
    }

    $From = "email"
    $To = "email"
    $Subject = "subject here"
    $Body = echo 'email body here' $dataset.tables[ 0 ] | Out-String
    $SMTPServer = "server here"


    Send-MailMessage -From $From -to $To -Subject $Subject -Body $Body -SmtpServer $SMTPServer
    #-Port $Port

}

$dataset = New-Object System.Data.DataSet

Invoke-SQL $dataset
$dataset.Tables
Invoke-Email $dataset
$result

Upvotes: 1

Views: 5062

Answers (1)

emanresu
emanresu

Reputation: 974

There are at least 2 things that you can do to accomplish this based on your current method of executing the query. For a simple SELECT query executed with the 'fill' method of the dataAdapter the return value should be the number of rows returned by the query. You could do this:

$resultCount = $adapter.fill($dataSet)

Then just check if $resultCount is greater than 0.

if($resultCount -gt 0){
   #invoke your email function here
}

You could also just check the row count of your data table like this:

$dataSet.Tables[your table name or index].Rows.Count

Again simply checking for greater than 0.

if($dataSet.Tables[your table name or index].Rows.Count -gt 0){
    #invoke your email function here
}

I recommend this second approach of counting the number of rows in the data table, because you do not have to worry about differences in the different data adapter objects that are available.

Upvotes: 1

Related Questions