Automate Epicor Kinetic Integration Tasks from PowerShell

The CData ADO.NET Provider for Epicor Kinetic 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 Epicor Kinetic.

ADO.NET Provider

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

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

To successfully connect to your ERP instance, you must specify the following connection properties:

• Url:the URL of the server hosting your ERP instance. For example, https://myserver.EpicorSaaS.com
• ERPInstance: the name of your ERP instance.
• User: the username of your account.
• Password: the password of your account.
• Service: the service you want to retrieve data from. For example, BaqSvc.

In addition, you may also set the optional connection properties:

• ApiKey: An optional key that may be required for connection to some services depending on your account configuration.
• ApiVersion: Defaults to v1. May be set to v2 to use the newer Epicor API.
• Company: Required if you set the ApiVersion to v2.

1. Load the provider's assembly:

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

2. Connect to Epicor Kinetic:

    
   $conn= New-Object System.Data.CData.EpicorERP.EpicorERPConnection("Service=Erp.BO.CustomerSvc;ERPInstance=MyInstance;URL=https://myaccount.epicorsaas.com;User=username;Password=password;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

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

   
   $sql="SELECT CustNum, Company from Customers"
   
   $da= New-Object System.Data.CData.EpicorERP.EpicorERPDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.custnum $_.company
   }
     

Update Epicor Kinetic Data

$cmd =  New-Object System.Data.CData.EpicorERP.EpicorERPCommand("UPDATE Customers SET CompanyName='CompanyName' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.EpicorERP.EpicorERPParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Epicor Kinetic Data

$cmd =  New-Object System.Data.CData.EpicorERP.EpicorERPCommand("INSERT INTO Customers (CompanyName) VALUES (@myCompanyName)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.EpicorERP.EpicorERPParameter("@myCompanyName","CompanyName")))
$cmd.ExecuteNonQuery()

Delete Epicor Kinetic Data

$cmd =  New-Object System.Data.CData.EpicorERP.EpicorERPCommand("DELETE FROM Customers WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.EpicorERP.EpicorERPParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()