Ready to get started?

Learn more about the CData ADO.NET Provider for Sugar or download a free trial:

Download Now

Automate Sugar CRM Integration Tasks from PowerShell

Are you looking for a quick and easy way to access Sugar CRM data from PowerShell? We show how to use the Cmdlets for Sugar CRM and the CData ADO.NET Provider for Sugar CRM to connect to Sugar CRM data and synchronize, automate, download, and more.

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

Cmdlets or ADO.NET?

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

After obtaining the needed connection properties, accessing Sugar CRM data in PowerShell consists of three basic steps.

The User and Password properties, under the Authentication section, must be set to valid SugarCRM user credentials. This will use the default OAuth token created to allow client logins. OAuthClientId and OAuthClientSecret are required if you do not wish to use the default OAuth token.

You can generate a new OAuth consumer key and consumer secret in Admin -> OAuth Keys. Set the OAuthClientId to the OAuth consumer key. Set the OAuthClientSecret to the consumer secret.

Additionally, specify the URL to the SugarCRM account.

Note that retrieving SugarCRM metadata can be expensive. It is advised that you store the metadata locally as described in the "Caching Metadata" chapter of the help documentation.

PowerShell

  1. Install the module:

    Install-Module SugarCRMCmdlets
  2. Connect:

    $sugarcrm = Connect-SugarCRM -User "$User" -Password "$Password" -URL "$URL" -CacheMetadata "$CacheMetadata"
  3. Search for and retrieve data:

    $name = "Bob" $accounts = Select-SugarCRM -Connection $sugarcrm -Table "Accounts" -Where "Name = `'$Name`'" $accounts

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

    $accounts = Invoke-SugarCRM -Connection $sugarcrm -Query 'SELECT * FROM Accounts WHERE Name = @Name' -Params @{'@Name'='Bob'}

ADO.NET

  1. Load the provider's assembly:

    [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Sugar CRM\lib\System.Data.CData.SugarCRM.dll")
  2. Connect to Sugar CRM:

    $conn= New-Object System.Data.CData.SugarCRM.SugarCRMConnection("User=MyUser;Password=MyPassword;URL=MySugarCRMAccountURL;CacheMetadata=True;") $conn.Open()
  3. Instantiate the SugarCRMDataAdapter, execute an SQL query, and output the results:

    $sql="SELECT Name, AnnualRevenue from Accounts" $da= New-Object System.Data.CData.SugarCRM.SugarCRMDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.name $_.annualrevenue }

Update Sugar CRM Data

PowerShell

Update-SugarCRM -Connection $SugarCRM -Columns @('Name','AnnualRevenue') -Values @('MyName', 'MyAnnualRevenue') -Table Accounts -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SugarCRM.SugarCRMCommand("UPDATE Accounts SET Name='Bob' WHERE Id = @myId", $conn) $cmd.Parameters.Add(new System.Data.CData.SugarCRM.SugarCRMParameter("@myId","10456255-0015501366")) $cmd.ExecuteNonQuery()

Insert Sugar CRM Data

PowerShell

Add-SugarCRM -Connection $SugarCRM -Table Accounts -Columns @("Name", "AnnualRevenue") -Values @("MyName", "MyAnnualRevenue")

ADO.NET

$cmd = New-Object System.Data.CData.SugarCRM.SugarCRMCommand("INSERT INTO Accounts (Name) VALUES (@myName)", $conn) $cmd.Parameters.Add(new System.Data.CData.SugarCRM.SugarCRMParameter("@myName","Bob")) $cmd.ExecuteNonQuery()

Delete Sugar CRM Data

PowerShell

Remove-SugarCRM -Connection $SugarCRM -Table "Accounts" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SugarCRM.SugarCRMCommand("DELETE FROM Accounts WHERE Id=@myId", $conn) $cmd.Parameters.Add(new System.Data.CData.SugarCRM.SugarCRMParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()