Automate Email Integration Tasks from PowerShell

The CData ADO.NET Provider for Email is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Email.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Email; this tutorial shows how to use the Provider to create, retrieve, update, and delete Email data.

Once you have acquired the necessary connection properties, accessing Email data in PowerShell can be enabled in three steps.

The User and Password properties, under the Authentication section, must be set to valid credentials. The Server must be specified to retrieve emails and the SMTPServer must be specified to send emails.

1. Load the provider's assembly:

   
   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Email\lib\System.Data.CData.Email.dll")
       

2. Connect to Email:

    
   $conn= New-Object System.Data.CData.Email.EmailConnection("[email protected];Password=password;Server=imap.gmail.com;Port=993;SMTP Server=smtp.gmail.com;SMTP Port=465;SSL Mode=EXPLICIT;Protocol=IMAP;Mailbox=Inbox;")
   $conn.Open()
   

3. Instantiate the EmailDataAdapter, execute an SQL query, and output the results:

   
   $sql="SELECT Mailbox, RecentMessagesCount from Mailboxes"
   
   $da= New-Object System.Data.CData.Email.EmailDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.mailbox $_.recentmessagescount
   }
     

Update Email Data

$cmd =  New-Object System.Data.CData.Email.EmailCommand("UPDATE Mailboxes SET Mailbox='Spam' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Email.EmailParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Email Data

$cmd =  New-Object System.Data.CData.Email.EmailCommand("INSERT INTO Mailboxes (Mailbox) VALUES (@myMailbox)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Email.EmailParameter("@myMailbox","Spam")))
$cmd.ExecuteNonQuery()

Delete Email Data

$cmd =  New-Object System.Data.CData.Email.EmailCommand("DELETE FROM Mailboxes WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Email.EmailParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()