Learn more
 

Ready to get started?

Download a free trial of the Acumatica Data Provider to get started:

 Download Now

Learn more:

Acumatica Icon Acumatica ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with Acumatica account data including Accounts, Bills, Customers, Leads, and more!

In this article

Related articles

Automate Acumatica Integration Tasks from PowerShell


Are you in search of a quick and easy way to access Acumatica data from PowerShell? This article demonstrates how to utilize the Acumatica Cmdlets for tasks like connecting to Acumatica data, automating operations, downloading data, and more.

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

PowerShell Cmdlets or ADO.NET Provider?

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

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

Set the following connection properties to connect to Acumatica:

  • User: Set this to your username.
  • Password: Set this to your password.
  • Company: Set this to your company.
  • Url: Set this to your Acumatica URL, in the format http://{Acumatica ERP instance URL}/entity/{Endpoint name}/{Endpoint version}/.
    For example: https://acumatica.com/entity/Default/17.200.001/

See the Getting Started guide in the CData driver documentation for more information.

PowerShell

  1. Install the module:

    Install-Module AcumaticaCmdlets

  2. Connect:

    $acumatica = Connect-Acumatica -Url "$Url" -User "$User" -Password "$Password" -Company "$Company"

  3. Search for and retrieve data:

    $id = "1" $events = Select-Acumatica -Connection $acumatica -Table "Events" -Where "Id = `'$Id`'" $events

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

    $events = Invoke-Acumatica -Connection $acumatica -Query 'SELECT * FROM Events WHERE Id = @Id' -Params @{'@Id'='1'}

ADO.NET

  1. Load the provider's assembly:

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

  2. Connect to Acumatica:

    $conn= New-Object System.Data.CData.Acumatica.AcumaticaConnection("Url = https://try.acumatica.com/ISV/entity/Default/17.200.001/;User=user;Password=password;Company=CompanyName;") $conn.Open()

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

    $sql="SELECT Id, location_displayname from Events" $da= New-Object System.Data.CData.Acumatica.AcumaticaDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.id $_.location_displayname }

Update Acumatica Data

PowerShell

Update-Acumatica -Connection $Acumatica -Columns @('Id','location_displayname') -Values @('MyId', 'Mylocation_displayname') -Table Events -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Acumatica.AcumaticaCommand("UPDATE Events SET Id='1' WHERE Id = @myId", $conn) $cmd.Parameters.Add((New-Object System.Data.CData.Acumatica.AcumaticaParameter("@myId","10456255-0015501366"))) $cmd.ExecuteNonQuery()

Insert Acumatica Data

PowerShell

Add-Acumatica -Connection $Acumatica -Table Events -Columns @("Id", "location_displayname") -Values @("MyId", "Mylocation_displayname")

ADO.NET

$cmd = New-Object System.Data.CData.Acumatica.AcumaticaCommand("INSERT INTO Events (Id) VALUES (@myId)", $conn) $cmd.Parameters.Add((New-Object System.Data.CData.Acumatica.AcumaticaParameter("@myId","1"))) $cmd.ExecuteNonQuery()

Delete Acumatica Data

PowerShell

Remove-Acumatica -Connection $Acumatica -Table "Events" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Acumatica.AcumaticaCommand("DELETE FROM Events WHERE Id=@myId", $conn) $cmd.Parameters.Add((New-Object System.Data.CData.Acumatica.AcumaticaParameter("@myId","001d000000YBRseAAH"))) $cmd.ExecuteNonQuery()