Automate Exact Online Integration Tasks from PowerShell

The CData Cmdlets for Exact Online 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 Exact Online.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Exact Online, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Exact Online data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Exact Online. To access Exact Online data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Exact Online.

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

Exact Online uses the OAuth authentication standard. The InitiateOAuth connection property facilitates the OAuth flow -- by default, this is set to GETANDREFRESH. You can also use the embedded OAuth credentials or you can register an OAuth app with Exact to obtain your own. In addition to the OAuth values, provide the Region. If Division is not set, the default Division is determined.

See the "Getting Started" chapter of the help documentation for more information.

PowerShell

1. Install the module:

   Install-Module ExactOnlineCmdlets

2. Connect:

   $exactonline = Connect-ExactOnline -Region "$Region" -Division "$Division" -InitiateOAuth "$InitiateOAuth"
   

3. Search for and retrieve data:

   $iscompetitor = "False"
   $accounts = Select-ExactOnline -Connection $exactonline -Table "Accounts" -Where "IsCompetitor = `'$IsCompetitor`'"
   $accounts
   

   You can also use the Invoke-ExactOnline cmdlet to execute SQL commands:

   $accounts = Invoke-ExactOnline -Connection $exactonline -Query 'SELECT * FROM Accounts WHERE IsCompetitor = @IsCompetitor' -Params @{'@IsCompetitor'='False'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Exact Online:

    
   $conn= New-Object System.Data.CData.ExactOnline.ExactOnlineConnection("Region='United States';Division=5512;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

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

   $sql="SELECT Name, CreditLinePurchase from Accounts"
   
   $da= New-Object System.Data.CData.ExactOnline.ExactOnlineDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.name $_.creditlinepurchase
   }
    

Update Exact Online Data

PowerShell

Update-ExactOnline -Connection $ExactOnline -Columns @('Name','CreditLinePurchase') -Values @('MyName', 'MyCreditLinePurchase') -Table Accounts -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.ExactOnline.ExactOnlineCommand("UPDATE Accounts SET IsCompetitor='False' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.ExactOnline.ExactOnlineParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Exact Online Data

PowerShell

Add-ExactOnline -Connection $ExactOnline -Table Accounts -Columns @("Name", "CreditLinePurchase") -Values @("MyName", "MyCreditLinePurchase") 

ADO.NET

$cmd = New-Object System.Data.CData.ExactOnline.ExactOnlineCommand("INSERT INTO Accounts (IsCompetitor) VALUES (@myIsCompetitor)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.ExactOnline.ExactOnlineParameter("@myIsCompetitor","False")))
$cmd.ExecuteNonQuery()

Delete Exact Online Data

PowerShell

Remove-ExactOnline -Connection $ExactOnline -Table "Accounts" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.ExactOnline.ExactOnlineCommand("DELETE FROM Accounts WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.ExactOnline.ExactOnlineParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()