Automate Zoho Inventory Integration Tasks from PowerShell

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

PowerShell Cmdlets or ADO.NET Provider?

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

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

In order to connect to Zoho Inventory, set the following connection properties:

• OrganizationId: set this to the ID associated with your specific Zoho Inventory organization
• InitiateOAuth: set the to "GETANDREFRESH"
• AccountsServer (Optional): set this full Account Server URL (only when manually refreshing the OAuth token)

The connectors use OAuth to authenticate with Zoho Inventory. For more information, refer to the Getting Started section of the Help documentation.

PowerShell

1. Install the module:

   Install-Module ZohoInventoryCmdlets

2. Connect:

   $zohoinventory = Connect-ZohoInventory -OrganizationId "$OrganizationId" -AccountsServer "$AccountsServer" -InitiateOAuth "$InitiateOAuth"
   

3. Search for and retrieve data:

   $firstname = "Katherine"
   $contacts = Select-ZohoInventory -Connection $zohoinventory -Table "Contacts" -Where "FirstName = `'$FirstName`'"
   $contacts
   

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

   $contacts = Invoke-ZohoInventory -Connection $zohoinventory -Query 'SELECT * FROM Contacts WHERE FirstName = @FirstName' -Params @{'@FirstName'='Katherine'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Zoho Inventory:

    
   $conn= New-Object System.Data.CData.ZohoInventory.ZohoInventoryConnection("OrganizationId=YourOrganizationId;AccountsServer=YourAccountServerURL;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

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

   $sql="SELECT Id, CustomerName from Contacts"
   
   $da= New-Object System.Data.CData.ZohoInventory.ZohoInventoryDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.customername
   }
    

Update Zoho Inventory Data

PowerShell

Update-ZohoInventory -Connection $ZohoInventory -Columns @('Id','CustomerName') -Values @('MyId', 'MyCustomerName') -Table Contacts -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.ZohoInventory.ZohoInventoryCommand("UPDATE Contacts SET FirstName='Katherine' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.ZohoInventory.ZohoInventoryParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Zoho Inventory Data

PowerShell

Add-ZohoInventory -Connection $ZohoInventory -Table Contacts -Columns @("Id", "CustomerName") -Values @("MyId", "MyCustomerName") 

ADO.NET

$cmd = New-Object System.Data.CData.ZohoInventory.ZohoInventoryCommand("INSERT INTO Contacts (FirstName) VALUES (@myFirstName)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.ZohoInventory.ZohoInventoryParameter("@myFirstName","Katherine")))
$cmd.ExecuteNonQuery()

Delete Zoho Inventory Data

PowerShell

Remove-ZohoInventory -Connection $ZohoInventory -Table "Contacts" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.ZohoInventory.ZohoInventoryCommand("DELETE FROM Contacts WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.ZohoInventory.ZohoInventoryParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()