Automate Suadeo Integration Tasks from PowerShell

The CData ADO.NET Provider for Suadeo 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 Suadeo.

ADO.NET Provider

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

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

The driver uses the OAuth 2.0 Resource Owner Password Credentials (ROPC) grant to authenticate to Suadeo. Authentication occurs directly using your credentials; there is no browser-based authorization flow or refresh token.

Set the following connection properties:

• URL: The base URL of your Suadeo instance.
• User: Your Suadeo username.
• Password: Your Suadeo password.
• AuthenticationName: The name identifier for the authentication configuration in your Suadeo instance. Different authentication names can be configured for different environments or use cases.

When you connect, the driver sends your credentials to the Suadeo OAuth token endpoint, receives an access token, and uses it for all subsequent requests. A new access token is obtained automatically when needed during the session.

1. Load the provider's assembly:

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

2. Connect to Suadeo:

    
   $conn= New-Object System.Data.CData.Suadeo.SuadeoConnection("URL=https://mysuadeoinstance;User=username;Password=password;AuthenticationName=your_auth_name;")
   $conn.Open()
   

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

   $sql="SELECT Id, Name from Customers"
   
   $da= New-Object System.Data.CData.Suadeo.SuadeoDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.name
   }