Utility to schedule automatic daily alert notification based on data retrieved from MySql database table: PowellShell script for reading data from MySql Database & Sending email

Recently, I have created a small utility to schedule  a daily alert notification to the user based on data retrieved from one of my MySql database table (Alerts table: which contains messages related to my application level violations). In this post, I will explain how that can be achieved in few steps using a simple PowerShell script.

Step 1: PowellShell script (database-alerts.ps1) for reading data from MySql Database & Sending email

Script (database-alerts.ps1): In this script, we are connecting to database (techcartnow), reading today’s messages form alerts table & sending emails if there are any violation messages retrieved from database.

Try
{
  [void][system.reflection.Assembly]::LoadFrom(“C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.5.2\MySQL.Data.dll”)

  #Create a variable to hold the connection:
  $myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection

  #Set the connection string:
  $myconnection.ConnectionString = "Data Source=127.0.0.1; Initial Catalog=techcartnow; uid=root; Password=root; Port=3306"

  #Call the Connection object’s Open() method:
  $myconnection.Open()
  
  $SMTPServer = "smtp.office365.com"
  $SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587)
    
  Try
  {
    $command = $myconnection.CreateCommand()
    
    $command.CommandText = "SELECT Message FROM alerts where DATE(CreatedDateUtc) = DATE(UTC_DATE());";

    $reader = $command.ExecuteReader()
    #The data reader will now contain the results from the database query.

    #Processing the Contents of a Data Reader
    #The contents of a data reader is processes row by row:

    $Body = "";

    while ($reader.Read()) {
      #And then field by field:
      for ($i= 0; $i -lt $reader.FieldCount; $i++) {
      $Body += $reader.GetValue($i).ToString() + [Environment]::NewLine
      }
    }

    if($Body -ne "")
    {
      $EmailFrom = "alert@techcartnow.com"
      $EmailTo = "kapil.khandelwal@techcartnow.com"
      $Subject ="ALERT:: Violation Alert."
      $Body = "Following violation were found: " + [Environment]::NewLine + $Body
      $SMTPClient.EnableSsl = $true
      $SMTPClient.Credentials = New-Object System.Net.NetworkCredential("alert@techcartnow.com", "Password");
      $SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)
    }

    $reader.Close()
    $reader.Dispose()
    write-output "Success"
  }
  Catch
  {
    write-output "Failure:: Innner"
  }
  Finally
  {
    write-output "Cleanup Started"
    $myconnection.Close()
    $myconnection.Dispose()
    $SMTPClient.Dispose()
    write-output "Cleanup Finished"
  }
}
Catch
{
  write-output "Failure:: Outer"
}

Note: Change the Database connection & SMTP settings

Step 2:Use “Windows Task Scheduler” for executing the PowerShell script on daily at a given time.

Here:

Program: powershell

Arguments: -File C:\database-alerts.ps1

Kapil Khandelwal

.NET Professional | Microsoft Certified Professional | DZone’s Most Valuable Blogger | Web Developer | Author | Blogger

Doctorate in Computer Science and Engineering

Microsoft Certified Professional (MCP) with over 12+ years of software industry experience including development, implementation & deployment of applications in the .NET framework

Experienced and skilled Agile Developer with a strong record of excellent teamwork, successful coding & project management. Specialises in problem identification and proposal of alternative solutions. Provided knowledge and individual mentoring to team members as needed

Among top 3% overall in terms of contribution on Stack Overflow (~2.3 million people reached my posts). Part of the top 1% Stack Overflow answerers in ASP.NET technology.

DZone’s Most Valuable Blogger (MVB)

Created and actively maintain the TechCartNow.com tech blog while also editing, writing, and researching topics for publication.

Excellent skills in Application Development using C#/Vb.Net, .NET Framework, ASP.NET, MVC, ADO.NET, WCF, WPF, Web API, SQL Server, jQuery, Angular, React, BackboneJS