Ready to get started?

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

Download Now

Access Sage 50 UK Data with Entity Framework 6

This article shows how to access Sage 50 UK data using an Entity Framework code-first approach. Entity Framework 6 is available in .NET 4.5 and above.

Entity Framework is an object-relational mapping framework that can be used to work with data as objects. While you can run the ADO.NET Entity Data Model wizard in Visual Studio to handle generating the Entity Model, this approach, the model-first approach, can put you at a disadvantage if there are changes in your data source or if you want more control over how the entities operate. In this article you will complete the code-first approach to accessing Sage 50 UK data using the CData ADO.NET Provider.

  1. Open Visual Studio and create a new Windows Form Application. This article uses a C# project with .NET 4.5.
  2. Run the command 'Install-Package EntityFramework' in the Package Manger Console in Visual Studio to install the latest release of Entity Framework.
  3. Modify the App.config file in the project to add a reference to the Sage 50 UK Entity Framework 6 assembly and the connection string.

    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.
    <configuration> ... <connectionStrings> <add name="Sage50UKContext" connectionString="Offline=False;URL=http://your-server:5493/sdata/accounts50/GCRM/your-address;User=Manager;" providerName="System.Data.CData.Sage50UK" /> </connectionStrings> <entityFramework> <providers> ... <provider invariantName="System.Data.CData.Sage50UK" type="System.Data.CData.Sage50UK.Sage50UKProviderServices, System.Data.CData.Sage50UK.Entities.EF6" /> </providers> <entityFramework> </configuration> </code>
  4. Add a reference to System.Data.CData.Sage50UK.Entities.EF6.dll, located in the lib -> 4.0 subfolder in the installation directory.
  5. Build the project at this point to ensure everything is working correctly. Once that's done, you can start coding using Entity Framework.
  6. Add a new .cs file to the project and add a class to it. This will be your database context, and it will extend the DbContext class. In the example, this class is named Sage50UKContext. The following code example overrides the OnModelCreating method to make the following changes:
    • Remove PluralizingTableNameConvention from the ModelBuilder Conventions.
    • Remove requests to the MigrationHistory table.
    using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Data.Entity.ModelConfiguration.Conventions; class Sage50UKContext : DbContext { public Sage50UKContext() { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { // To remove the requests to the Migration History table Database.SetInitializer<Sage50UKContext>(null); // To remove the plural names modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); } }
  7. Create another .cs file and name it after the Sage 50 UK entity you are retrieving, for example, TradingAccounts. In this file, define both the Entity and the Entity Configuration, which will resemble the example below: using System.Data.Entity.ModelConfiguration; using System.ComponentModel.DataAnnotations.Schema; [System.ComponentModel.DataAnnotations.Schema.Table("TradingAccounts")] public class TradingAccounts { [System.ComponentModel.DataAnnotations.Key] public System.String Name { get; set; } public System.String FinanceBalance { get; set; } }
  8. Now that you have created an entity, add the entity to your context class: public DbSet<TradingAccounts> TradingAccounts { set; get; }
  9. With the context and entity finished, you are now ready to query the data in a separate class. For example: Sage50UKContext context = new Sage50UKContext(); context.Configuration.UseDatabaseNullSemantics = true; var query = from line in context.TradingAccounts select line;