Ready to get started?

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

 Download Now

Learn more:

Sage 50 UK Icon Sage UK ADO.NET Provider

Complete read-write access to Sage 50 UK enables developers to search (Customers, Transactions, Invoices, Sales Receipts, etc.), update items, edit customers, and more, from any .NET application.

Automate Sage 50 UK Integration Tasks from PowerShell



Are you in search of a quick and easy way to access Sage 50 UK data from PowerShell? This article demonstrates how to utilize the Sage 50 UK Cmdlets and the CData ADO.NET Provider for Sage 50 UK for tasks like connecting to Sage 50 UK data, automating operations, downloading data, and more.

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 Driver?

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

Update-Sage50UK -Connection $Sage50UK -Columns @('Name','FinanceBalance') -Values @('MyName', 'MyFinanceBalance') -Table TradingAccounts -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Sage50UK.Sage50UKCommand("UPDATE TradingAccounts SET TradingAccountUUID='c2ef66a5-a545-413b-9312-79a53caadbc4' WHERE Id = @myId", $conn) $cmd.Parameters.Add(new System.Data.CData.Sage50UK.Sage50UKParameter("@myId","10456255-0015501366")) $cmd.ExecuteNonQuery()

Insert Sage 50 UK Data

PowerShell

Add-Sage50UK -Connection $Sage50UK -Table TradingAccounts -Columns @("Name", "FinanceBalance") -Values @("MyName", "MyFinanceBalance")

ADO.NET

$cmd = New-Object System.Data.CData.Sage50UK.Sage50UKCommand("INSERT INTO TradingAccounts (TradingAccountUUID) VALUES (@myTradingAccountUUID)", $conn) $cmd.Parameters.Add(new System.Data.CData.Sage50UK.Sage50UKParameter("@myTradingAccountUUID","c2ef66a5-a545-413b-9312-79a53caadbc4")) $cmd.ExecuteNonQuery()

Delete Sage 50 UK Data

PowerShell

Remove-Sage50UK -Connection $Sage50UK -Table "TradingAccounts" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Sage50UK.Sage50UKCommand("DELETE FROM TradingAccounts WHERE Id=@myId", $conn) $cmd.Parameters.Add(new System.Data.CData.Sage50UK.Sage50UKParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()