Automate Bullhorn CRM Integration Tasks from PowerShell

The CData Cmdlets for Bullhorn CRM 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 Bullhorn CRM.

PowerShell Cmdlets or ADO.NET Provider?

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

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

Begin by providing your Bullhorn CRM account credentials in the following:

• DataCenterCode: Set this to the data center code which responds to your data center. Refer to the list of data-center-specific Bullhorn API URLs: https://bullhorn.github.io/Data-Center-URLs/

If you are uncertain about your data center code, codes like CLS2, CLS21, etc. are cluster IDs that are contained in a user's browser URL (address bar) once they are logged in.

Example: https://cls21.bullhornstaffing.com/BullhornSTAFFING/MainFrame.jsp?#no-ba... indicates that the logged in user is on CLS21.

Authenticating with OAuth

Bullhorn CRM uses the OAuth 2.0 authentication standard. To authenticate using OAuth, create and configure a custom OAuth app. See the Help documentation for more information.

PowerShell

1. Install the module:

   Install-Module BullhornCRMCmdlets

2. Connect:

   $bullhorncrm = Connect-BullhornCRM -DataCenterCode "$DataCenterCode" -OAuthClientId "$OAuthClientId" -OAuthClientSecret "$OAuthClientSecret" -InitiateOAuth "$InitiateOAuth"
   

3. Search for and retrieve data:

   $candidatename = "Jane Doe"
   $candidate = Select-BullhornCRM -Connection $bullhorncrm -Table "Candidate" -Where "CandidateName = `'$CandidateName`'"
   $candidate
   

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

   $candidate = Invoke-BullhornCRM -Connection $bullhorncrm -Query 'SELECT * FROM Candidate WHERE CandidateName = @CandidateName' -Params @{'@CandidateName'='Jane Doe'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Bullhorn CRM:

    
   $conn= New-Object System.Data.CData.BullhornCRM.BullhornCRMConnection("DataCenterCode=CLS33;OAuthClientId=myoauthclientid;OAuthClientSecret=myoauthclientsecret;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

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

   $sql="SELECT Id, CandidateName from Candidate"
   
   $da= New-Object System.Data.CData.BullhornCRM.BullhornCRMDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.candidatename
   }
    

Update Bullhorn CRM Data

PowerShell

Update-BullhornCRM -Connection $BullhornCRM -Columns @('Id','CandidateName') -Values @('MyId', 'MyCandidateName') -Table Candidate -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.BullhornCRM.BullhornCRMCommand("UPDATE Candidate SET CandidateName='Jane Doe' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.BullhornCRM.BullhornCRMParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Bullhorn CRM Data

PowerShell

Add-BullhornCRM -Connection $BullhornCRM -Table Candidate -Columns @("Id", "CandidateName") -Values @("MyId", "MyCandidateName") 

ADO.NET

$cmd = New-Object System.Data.CData.BullhornCRM.BullhornCRMCommand("INSERT INTO Candidate (CandidateName) VALUES (@myCandidateName)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.BullhornCRM.BullhornCRMParameter("@myCandidateName","Jane Doe")))
$cmd.ExecuteNonQuery()

Delete Bullhorn CRM Data

PowerShell

Remove-BullhornCRM -Connection $BullhornCRM -Table "Candidate" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.BullhornCRM.BullhornCRMCommand("DELETE FROM Candidate WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.BullhornCRM.BullhornCRMParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject