Automate SendGrid Integration Tasks from PowerShell

The CData Cmdlets for SendGrid are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to SendGrid.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to SendGrid, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete SendGrid data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for SendGrid. To access SendGrid data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for SendGrid.

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.

PowerShell

1. Install the module:

   Install-Module SendGridCmdlets

2. Connect:

   $sendgrid = Connect-SendGrid -User "$User" -Password "$Password"
   

3. Search for and retrieve data:

   $type = "Device"
   $advancedstats = Select-SendGrid -Connection $sendgrid -Table "AdvancedStats" -Where "Type = `'$Type`'"
   $advancedstats
   

   You can also use the Invoke-SendGrid cmdlet to execute SQL commands:

   $advancedstats = Invoke-SendGrid -Connection $sendgrid -Query 'SELECT * FROM AdvancedStats WHERE Type = @Type' -Params @{'@Type'='Device'}
   

ADO.NET

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

PowerShell

Update-SendGrid -Connection $SendGrid -Columns @('Name','Clicks') -Values @('MyName', 'MyClicks') -Table AdvancedStats -Id "MyId"

ADO.NET

$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

PowerShell

Add-SendGrid -Connection $SendGrid -Table AdvancedStats -Columns @("Name", "Clicks") -Values @("MyName", "MyClicks") 

ADO.NET

$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

PowerShell

Remove-SendGrid -Connection $SendGrid -Table "AdvancedStats" -Id "MyId"

ADO.NET

$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()