Automate Pipedrive Integration Tasks from PowerShell

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

ADO.NET Provider

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

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

1. Load the provider's assembly:

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

2. Connect to Pipedrive:

    
   $conn= New-Object System.Data.CData.Pipedrive.PipedriveConnection("AuthScheme=Basic;CompanyDomain=MyCompanyDomain;APIToken=MyAPIToken;")
   $conn.Open()
   

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

   
   $sql="SELECT PersonName, UserEmail from Deals"
   
   $da= New-Object System.Data.CData.Pipedrive.PipedriveDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.personname $_.useremail
   }
     

Update Pipedrive Data

$cmd =  New-Object System.Data.CData.Pipedrive.PipedriveCommand("UPDATE Deals SET Value='50000' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Pipedrive.PipedriveParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Pipedrive Data

$cmd =  New-Object System.Data.CData.Pipedrive.PipedriveCommand("INSERT INTO Deals (Value) VALUES (@myValue)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Pipedrive.PipedriveParameter("@myValue","50000")))
$cmd.ExecuteNonQuery()

Delete Pipedrive Data

$cmd =  New-Object System.Data.CData.Pipedrive.PipedriveCommand("DELETE FROM Deals WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Pipedrive.PipedriveParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()