Automate SpaceX 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 SpaceX.

ADO.NET Provider

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

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

Start by setting the Profile connection property to the location of the SpaceX Profile on disk (e.g. C:\profiles\SpaceX.apip). Next, set the ProfileSettings connection property to the connection string for SpaceX (see below).

SpaceX API Profile Settings

No authentication is required to access SpaceX data — the SpaceX API is publicly available. Simply set the Profile connection property to the path of the SpaceX profile file.

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 SpaceX:

    
   $conn= New-Object System.Data.CData.API.APIConnection("Profile=C:\profiles\SpaceX.apip;")
   $conn.Open()
   

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

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