<#
.SYNOPSIS
    Outputs the result of a SQL command.

.DESCRIPTION
    In order for this runbook to work, the SQL Server must be accessible from the runbook worker
    running this runbook. Make sure the SQL Server allows incoming connections from Azure services
	by selecting 'Allow Windows Azure Services' on the SQL Server configuration page in Azure.

    This runbook also requires an Automation Credential asset be created before the runbook is
    run, which stores the username and password of an account with access to the SQL Server.
    That credential should be referenced for the SqlCredential parameter of this runbook.

.PARAMETER SqlServer
    String name of the SQL Server to connect to.

.PARAMETER SqlServerPort
    Integer port to connect to the SQL Server on.

.PARAMETER Database
    String name of the SQL Server database to connect to.

.PARAMETER sql
    SQL string to execute.

.PARAMETER $SqlCredName
    String name of the credential asset containing a username and password for the SQL Server

.PARAMETER $SmtpCredName
    String name of the credential asset containing a username and password for the SMTP server.
#>

workflow CheckDatabaseAndSendEmail
{
    param(
        [parameter(Mandatory=$True)]
        [string] $SqlServer = "xxxxxxxxxx.database.windows.net",

        [parameter(Mandatory=$False)]
        [int] $SqlServerPort = 1433,

        [parameter(Mandatory=$True)]
        [string] $Database = "MyDatabaseName",

        [parameter(Mandatory=$True)]
        [string] $sql = "EXECUTE [dbo].[BuildEmailBody] 10",

        [parameter(Mandatory=$True)]
        [string] $SqlCredName = "sqldb",

        [parameter(Mandatory=$True)]
        [string] $SmtpCredName = "smtp"
    )

    # Get the username and password from the SQL Credential
    $SqlCredential = Get-AutomationPSCredential -Name $SqlCredName
    $SqlUsername = $SqlCredential.UserName
    $SqlPass = $SqlCredential.GetNetworkCredential().Password

    $SmtpCred = Get-AutomationPSCredential -Name $SmtpCredName

    inlinescript {
        $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
        $Conn.Open()
        $Cmd=new-object System.Data.SqlClient.SqlCommand($using:sql, $Conn)
        $Cmd.CommandTimeout=120

        $Ds=New-Object System.Data.DataSet
        $Da=New-Object System.Data.SqlClient.SqlDataAdapter($Cmd)
        [void]$Da.fill($Ds)
        $html = $Ds.Tables[0].Rows[0][0]
        $Conn.Close()

        if ($html.Length -eq 0) {
            Write-Output "No data was found."
        } else {
            Write-Output $html

            $To = "me@example.com"
            $From = "auto@example.com"
            $Subject = "Email from Azure Automation"

             Send-MailMessage `
                -To $To  `
                -Subject $Subject  `
                -Body $html `
                -UseSsl `
                -Port 587 `
                -SmtpServer 'smtp.sendgrid.net' `
                -From $From `
                -BodyAsHtml `
                -Encoding ([System.Text.Encoding]::UTF8) `
                -Credential $using:SmtpCred

            Write-Output "Email was sent."
        }
    }

}