Automate Zoho Projects Integration Tasks from PowerShell

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

PowerShell Cmdlets or ADO.NET Provider?

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

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

The connector uses OAuth to authenticate with Zoho Projects. CData Software has already registered an OAuth application with Zoho Project which is embedded and used to authenticate.

If you use the embedded credentials, set the InitiateOAuth connection property to "GETANDREFRESH".

If you would prefer to use your own custom OAuth app, see the Help documentation.

PowerShell

1. Install the module:

   Install-Module ZohoProjectsCmdlets

2. Connect:

   $zohoprojects = Connect-ZohoProjects -InitiateOAuth "$InitiateOAuth"
   

3. Search for and retrieve data:

   $crmpartner = "true"
   $portals = Select-ZohoProjects -Connection $zohoprojects -Table "Portals" -Where "CrmPartner = `'$CrmPartner`'"
   $portals
   

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

   $portals = Invoke-ZohoProjects -Connection $zohoprojects -Query 'SELECT * FROM Portals WHERE CrmPartner = @CrmPartner' -Params @{'@CrmPartner'='true'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Zoho Projects:

    
   $conn= New-Object System.Data.CData.ZohoProjects.ZohoProjectsConnection("InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

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

   $sql="SELECT Id, Name from Portals"
   
   $da= New-Object System.Data.CData.ZohoProjects.ZohoProjectsDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.name
   }
    

Update Zoho Projects Data

PowerShell

Update-ZohoProjects -Connection $ZohoProjects -Columns @('Id','Name') -Values @('MyId', 'MyName') -Table Portals -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.ZohoProjects.ZohoProjectsCommand("UPDATE Portals SET CrmPartner='true' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.ZohoProjects.ZohoProjectsParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Zoho Projects Data

PowerShell

Add-ZohoProjects -Connection $ZohoProjects -Table Portals -Columns @("Id", "Name") -Values @("MyId", "MyName") 

ADO.NET

$cmd = New-Object System.Data.CData.ZohoProjects.ZohoProjectsCommand("INSERT INTO Portals (CrmPartner) VALUES (@myCrmPartner)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.ZohoProjects.ZohoProjectsParameter("@myCrmPartner","true")))
$cmd.ExecuteNonQuery()

Delete Zoho Projects Data

PowerShell

Remove-ZohoProjects -Connection $ZohoProjects -Table "Portals" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.ZohoProjects.ZohoProjectsCommand("DELETE FROM Portals WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.ZohoProjects.ZohoProjectsParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()