How to Access Sage 50 UK Data Using Entity Framework

Microsoft Entity Framework serves as an object-relational mapping framework for working with data represented as objects. Although Visual Studio offers the ADO.NET Entity Data Model wizard to automatically generate the Entity Model, this model-first approach may present challenges when your data source undergoes changes or when you require greater control over entity operations. In this article, we will delve into the code-first approach for accessing Sage 50 UK data through the CData ADO.NET Provider, providing you with more flexibility and control.

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;