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

ADO.NET Provider

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

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

Authentication

Factorial uses OAuth 2.0 for authentication to connect to your HR data or to allow other users to connect to their data.

Using OAuth Authentication

To connect using OAuth, follow these steps:

1. Navigate to your Factorial admin panel and create a new OAuth application.
2. Copy the Client ID and Client Secret from your application configuration.
3. Configure the following connection properties:

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

• AuthScheme: Set this to OAuth.
• OAuthClientId: Set this to your OAuth Client ID.
• OAuthClientSecret: Set this to your OAuth Client Secret.
• Scope: Set this to specify the data access permissions (default: "read write").

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

    
   $conn= New-Object System.Data.CData.API.APIConnection("Profile=C:\profiles\Factorial.apip;AuthScheme=OAuth;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;CallbackUrl=your_callback_url;")
   $conn.Open()
   

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

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