Ready to get started?

Learn more about the CData ADO.NET Provider for SendGrid or download a free trial:

Download Now

Automate SendGrid Integration Tasks from PowerShell

Are you looking for a quick and easy way to access SendGrid data from PowerShell? We show how to use the Cmdlets for SendGrid and the CData ADO.NET Provider for SendGrid to connect to SendGrid data and synchronize, automate, download, and more.

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.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the SendGrid API, 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.

After obtaining the needed connection properties, accessing SendGrid data in PowerShell consists of three basic 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 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 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 System.Data.CData.SendGrid.SendGridParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()