Automate SendGrid Integration Tasks from PowerShell

The CData ADO.NET Provider for SendGrid 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 SendGrid.

ADO.NET Provider

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

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

To make use of all the available features, provide the User and Password connection properties.

To connect with limited features, you can set the APIKey connection property instead. See the "Getting Started" chapter of the help documentation for a guide to obtaining the API key.

1. Load the provider's assembly:

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

2. Connect to SendGrid:

    
   $conn= New-Object System.Data.CData.SendGrid.SendGridConnection("User=admin;Password=abc123;")
   $conn.Open()
   

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

   
   $sql="SELECT Name, Clicks from AdvancedStats"
   
   $da= New-Object System.Data.CData.SendGrid.SendGridDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.name $_.clicks
   }
     

Update SendGrid Data

$cmd =  New-Object System.Data.CData.SendGrid.SendGridCommand("UPDATE AdvancedStats SET Type='Device' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SendGrid.SendGridParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert SendGrid Data

$cmd =  New-Object System.Data.CData.SendGrid.SendGridCommand("INSERT INTO AdvancedStats (Type) VALUES (@myType)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SendGrid.SendGridParameter("@myType","Device")))
$cmd.ExecuteNonQuery()

Delete SendGrid Data

$cmd =  New-Object System.Data.CData.SendGrid.SendGridCommand("DELETE FROM AdvancedStats WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SendGrid.SendGridParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()