We are proud to share our inclusion in the 2024 Gartner Magic Quadrant for Data Integration Tools. We believe this recognition reflects the differentiated business outcomes CData delivers to our customers.
Get the Report →Automate Exact Online Integration Tasks from PowerShell
Are you in search of a quick and easy way to access Exact Online data from PowerShell? This article demonstrates how to utilize the Exact Online Cmdlets for tasks like connecting to Exact Online data, automating operations, downloading data, and more.
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
-
Install the module:
Install-Module ExactOnlineCmdlets
-
Connect:
$exactonline = Connect-ExactOnline -Region "$Region" -Division "$Division"
-
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
-
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")
-
Connect to Exact Online:
$conn= New-Object System.Data.CData.ExactOnline.ExactOnlineConnection("Region='United States';Division=5512;InitiateOAuth=GETANDREFRESH") $conn.Open()
-
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()
CodeProject