Ready to get started?

Learn more about the CData ADO.NET Provider for Exact Online or download a free trial:

Download Now

Automate Exact Online Integration Tasks from PowerShell

Are you looking for a quick and easy way to access Exact Online data from PowerShell? We show how to use the Cmdlets for Exact Online and the CData ADO.NET Provider for Exact Online to connect to Exact Online data and synchronize, automate, download, 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.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the Exact Online API, 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.

After obtaining the needed connection properties, accessing Exact Online data in PowerShell consists of three basic 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"
  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 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 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 System.Data.CData.ExactOnline.ExactOnlineParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()