Automate Sage 50 UK Integration Tasks from PowerShell

The CData Cmdlets for Sage 50 UK 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 Sage 50 UK.

PowerShell Cmdlets or ADO.NET Provider?

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

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

Note: Only Sage 50 UK 2012 and above are supported.

The User and Password properties, under the Connection section, must be set to valid Sage 50 UK user credentials. These values will be the same used to log in to the Sage 50 UK software.

Additionally, the URL property, under the Connection section, will need to be set to the address of the company dataset desired. To obtain the address, do the following:

1. If you have not already done so, open the Sage 50 UK software.
2. Click Tools -> Internet Options.
3. Select the SData Settings tab.
4. Click the Details button next to Sage 50 Accounts. A window is displayed containing a list of company names along with the address to their corresponding datasets.
5. Set the URL property to the value in the address field next to the company desired.

PowerShell

1. Install the module:

   Install-Module Sage50UKCmdlets

2. Connect:

   $sage50uk = Connect-Sage50UK -URL "$URL" -User "$User"

3. Search for and retrieve data:

   $tradingaccountuuid = "c2ef66a5-a545-413b-9312-79a53caadbc4" $tradingaccounts = Select-Sage50UK -Connection $sage50uk -Table "TradingAccounts" -Where "TradingAccountUUID = `'$TradingAccountUUID`'" $tradingaccounts

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

   $tradingaccounts = Invoke-Sage50UK -Connection $sage50uk -Query 'SELECT * FROM TradingAccounts WHERE TradingAccountUUID = @TradingAccountUUID' -Params @{'@TradingAccountUUID'='c2ef66a5-a545-413b-9312-79a53caadbc4'}

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Sage 50 UK:

   $conn= New-Object System.Data.CData.Sage50UK.Sage50UKConnection("URL=http://your-server:5493/sdata/accounts50/GCRM/your-address;User=Manager;") $conn.Open()

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

   $sql="SELECT Name, FinanceBalance from TradingAccounts" $da= New-Object System.Data.CData.Sage50UK.Sage50UKDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.name $_.financebalance }

Update Sage 50 UK Data

PowerShell

ADO.NET

Insert Sage 50 UK Data

PowerShell

ADO.NET

Delete Sage 50 UK Data

PowerShell

ADO.NET