Learn more
 

Ready to get started?

Download a free trial of the Sugar Data Provider to get started:

 Download Now

Learn more:

Sugar Icon Sugar ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with Sugar account data including Leads, Contacts, Opportunities, Accounts, and more!

In this article

Related articles

Automate Sugar CRM Integration Tasks from PowerShell


Are you in search of a quick and easy way to access Sugar CRM data from PowerShell? This article demonstrates how to utilize the Sugar CRM Cmdlets for tasks like connecting to Sugar CRM data, automating operations, downloading data, 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.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Sugar CRM, 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. To access Sugar CRM data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Sugar.

Once you have acquired the necessary connection properties, accessing Sugar CRM data in PowerShell can be enabled in three 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\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-Object 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-Object 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-Object System.Data.CData.SugarCRM.SugarCRMParameter("@myId","001d000000YBRseAAH"))) $cmd.ExecuteNonQuery()