Automate Acumatica Integration Tasks from PowerShell

The CData ADO.NET Provider for Acumatica is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Acumatica.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Acumatica; this tutorial shows how to use the Provider to create, retrieve, update, and delete Acumatica data.

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.

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

$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

$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

$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()