Automate Onfleet Integration Tasks from PowerShell

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

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Onfleet; this tutorial shows how to use the Provider to retrieve Onfleet data.

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

Onfleet uses HTTP ApiKey Authentication. Your Onfleet API Key is used to authenticate requests. You can generate or view your API Key in the Onfleet dashboard under Settings > API.

Using ApiKey Authentication

After setting the following connection properties, you are ready to connect:

• AuthScheme: Set this to APIKey.
• APIKey: Set this to your Onfleet API Key.

Example connection string:

Profile=C:\profiles\Onfleet.apip;AuthScheme=APIKey;ProfileSettings="ApiKey=your_api_key";

1. Load the provider's assembly:

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

2. Connect to Onfleet:

    
   $conn= New-Object System.Data.CData.API.APIConnection("Profile=C:\profiles\Onfleet.apip;AuthScheme=APIKey;ProfileSettings="ApiKey=your_api_key";")
   $conn.Open()
   

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

   $sql="SELECT , from Administrators"
   
   $da= New-Object System.Data.CData.API.APIDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_. $_.
   }