Automate Epicor Kinetic Integration Tasks from PowerShell

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

PowerShell Cmdlets or ADO.NET Provider?

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

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.

PowerShell

1. Install the module:

   Install-Module EpicorERPCmdlets

2. Connect:

   $epicorerp = Connect-EpicorERP -Service "$Service" -ERPInstance "$ERPInstance" -URL "$URL" -User "$User" -Password "$Password" -InitiateOAuth "$InitiateOAuth"
   

3. Search for and retrieve data:

   $companyname = "CompanyName"
   $customers = Select-EpicorERP -Connection $epicorerp -Table "Customers" -Where "CompanyName = `'$CompanyName`'"
   $customers
   

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

   $customers = Invoke-EpicorERP -Connection $epicorerp -Query 'SELECT * FROM Customers WHERE CompanyName = @CompanyName' -Params @{'@CompanyName'='CompanyName'}
   

ADO.NET

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

PowerShell

Update-EpicorERP -Connection $EpicorERP -Columns @('CustNum','Company') -Values @('MyCustNum', 'MyCompany') -Table Customers -Id "MyId"

ADO.NET

$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

PowerShell

Add-EpicorERP -Connection $EpicorERP -Table Customers -Columns @("CustNum", "Company") -Values @("MyCustNum", "MyCompany") 

ADO.NET

$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

PowerShell

Remove-EpicorERP -Connection $EpicorERP -Table "Customers" -Id "MyId"

ADO.NET

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

CodeProject