Thursday, May 10, 2012

SData Solutions within Sage


In this post I list of some Sage products currently leveraging SData.

These products are using SData to solve a variety of problems. SData provides:
  • REST Web Services
  • Integrations for Sage to Sage and Sage to 3rd party products
  • Mobility/Browser based access

SData Glossary

  • SData Provider – Server side component that “provides” a web services API. E.G: Web Server
  • SData Consumer – Client side component that interacts with Sage SData Providers. E.G: Web Browser
  • SData Sync Provider – component used to synchronize changes to common data across multiple Sage products
  • Contracts – agreed upon format of data used in SData web service calls. The most commonly used SData Contract is the Global CRM Integration contract which I'll discuss in great detail in a future post.


SData Solutions within Sage:

Product
Consumer
Provider
Sync
Contract
Sage SalesLogix
Yes
Yes
Yes
GCRM
Sage CRM
Yes
No
Yes
GCRM
Sage ACT!
Yes
No
No
GCRM
Sage 50 (UK)
No
Yes
No
GCRM
Sage 200 (UK)
No
Yes
No
Sage 1000 (UK)
Yes
Yes
Yes
GCRM
Sage X3
Yes
Yes
Yes
GCRM
Office Line
Yes
Yes
Yes
GCRM
Logic Class ERP/Murano
Yes
Yes
Yes
GCRM
Sage Mobile
Yes
No
No
Sage 300 ERP
Yes
Yes
Sage 300 Construction and Real Estate
Yes
Yes
No
Native
Billing Boss/Sage 50 (Canadian Edition)
Yes
Yes
Yes
GCRM +
Sage 100 ERP
No
Yes
No
Native




Validating SData Providers


In this post I discuss:
  • Opportunity SData provides
  • Problem/Challenge of building an SData provider that’s compliant with the SData specification
  • Solution is the SData Validation Tool


Opportunity - Broad Common Interface

In a previous post I discussed how SData can provide a broad common interface for all Sage products. The SData specification defines a common language for integrating with Sage applications.

There’s growing commercial interest in SData and many success stories, here’s a list of SData solutions and a Glossary of SData terms.

Sage 100 and Sage 300 teams are building and/or improving their SData services. I’m not sure of the exact release dates for these services but I’ll provide an update as soon as that information is available.

If Sage 100 and Sage 300 both provide valid SData web services then an ISV will have a common method for accessing Sage business logic and data. ISVs can request a representation of a resource and/or delete, update and create resources.

Sage 100 and Sage 300 currently don’t implement a common SData contract so the representation of a resource might be different; for example a Sales Order resource might have different elements, smaller or longer address fields, etc. Nevertheless, there will at least be a common language for CRUD operations on resources.

Having a common language means large parts of integrations between Sage 100 and Sage 300 can be common. An R&D team working in .NET could use our .NET SData client libraries as a common data access layer for both Sage 100 and Sage 300; they don’t need to know or think about the fact that Sage 100 is written in ProvideX and Sage 300’s SData provider is written in Java. This will reduce the cost of developing and maintaining integrations with Sage products.

Problem/Challenge - Compliance with the SData Specification

Unfortunately, the potential of SData is significantly diminished when providers deviate from the SData specification. If Sage 100 and Sage 300 interpret and implement the SData specification differently or there’re bugs in their provider’s that cause deviation from the SData specification then client libraries and data access logic will require special conditions for specific providers.  For example:

Frequently teams will develop an SData provider without an external partner/consumer. An R&D team will build both an SData provider and consumer to solve a business problem. They think they’re also laying the ground work for future integrations. SData enables fantastic accessibility so they know eventually someone else will come along and utilize those feeds; maybe a solution they haven’t considered, like an Excel Plug-in, or Mobile Client that’s driven by SData will utilize their SData feeds.

However, if they have not validated their compliance with the SData specification using the Validation Tool there’s a good chance that SData provider is not 100% SData compliant so the emerging ecosystem of SData consumers is not available to them.

If an R&D team undertakes to build an SData provider they must validate their provider is compliant with the SData specification. If an SData provider is not compliant then that project does not meet requirements even if their SData consumer works and meets the business case requirements.

Solution - SData Validation Tool

Fortunately there’s a great tool for validating SData providers. The SData Validation Tool has been around for several years. It’s free, easy to use and comprehensive. Currently Sage 100 and Sage 300 are validating their SData providers with this tool.

Download the SData Validation Tool from the SData web site and run the installer. The installer asks for a valid email address and then it immediately emails an activation code.

Once installed simply enter the URL of an SData provider and the Validation Tool will run hundreds of tests. The results are presented in a Grid or Tree view that indicated the number of test performed and status for each; each test results in a status of: Error, Warning, Successful or Not Executable.

Where to get the SData Validation Tool



Tuesday, May 1, 2012

SData with Entity Framework (EF) Model & Database First

The SIF is a great framework for building SData providers, see my previous post on the SIF for more about it.

In this post I'm going to provide complete instructions on how to generate a RESTful web service  starting with the Northwind sample database.  The web service will be able to handle SData or OData URLs/Queries.

With Visual Studio (VS) it's easy to build a RESTful web service starting Model & Database and adding support for SData URLs is easy too.

I'm going to do this with Visual Studio 11 Beta, Entity Framework (EF) 5 and EF 5.x DbContext Generator for C#. I'll generate my Model from SQL Server and the Northwind sample database.

Video Tutorial

I provide instructions below the video if you prefer. The Video will show:
  • Creating the MVC .NET 4 Web API project from the Northwind database
  • Routes for handling SData Single and Collection URLs
  • Referencing the SDataTransformHandler which handles complex SData queries



Instructions for generating a RESTful web service from the Northwind Demo database. 


Follow my instruction or this Microsoft tutorial:
EF 4.2 Model & Database First Walkthrough

Create Web API project in VS

1.            File New Project
2.            APS.NET MVC 4Web Application (Wizard)
3.            Web API (Wizard)

Build Solution!

You should now be able to run default wizard generated Web API project and make a REST request like:


To see a JSON content type returned add the ACCEPT header and JSON type: E.G, Add Header:
               Accept  application/json
            

Add database connection

1.            Open Server Explorer View
2.            Right click Data Connections -> Select “Add Connection” wizard
3.            Select Server Name and then select database name
4.            Test Connection

Add model from database

1.            Right Click “Models”, select: Add -> New Item
2.            Select “ADO.NET Entity Data Model from the list
3.            In Entity Data Model Wizard select “Generate  from database”
4.            Select Connection (E.G. onm512295.Northwind.dbo)
5.            Click Next button and then select all tables from the “Entity Data Model Wizard”
6.            Finish
Build Solution!

Generate the code from the model

If using VS 11 then install the EF 5.x DbContext Generator for C#:

1.            Right click on the model_name.edmx diagram/editor and click “Add Code Generation Item…”
2.            Select “Code” from the left hand list
3.            Select “EF 5.x DbContext Generator” and click Add
4.            Click ok if you get a warning

There will now be a node called [some name].tt within Models in Solution Explorer. Within [some name].tt there will be all of the Entities. E.g. Customer, Order, etc.

There should also be a [some name].Context.tt node under the Models node in Solution Explorer. This is required later (see below).

Important warning: If the keyword “virtual” appears in the Entities (E.G. in Order.cs, etc.) then delete it. If the virtual keyword exists on an entities member then the entity will not be serialized; an error like the following  will appear at runtime when you eventually execute a GET:  
Exception Details: System.Xml.XmlException: You must write an attribute 'type'='object' after writing the attribute with local name '__type'.

Build Solution!

Generate Controller for each the Entity

  1. Right Click “Controllers” folder and “Add Controller”
  2. The Add Controller Wizard will popup enter the following: 
  3. Specify Controller name that matches target Entity, E.G: "CustomerController" for the Customer model class
  4. Template: drop box select “API controller with empty read/write actions".
  5. Model class: Select the Model class, e.g.  "Customer"
  6. Data context class: Select the DbContext created in step 3 of Generate the code from the model instructions above. E.G: "NorthwindEntities"

Add logic to the controller class to read data

    public class CustomerController : ApiController
    {
        NorthwindEntities db = new NorthwindEntities();
        // GET /api/customer
        public IQueryable<Customer> Get()
        {
            IQueryable<Customer> retVal = db.Customers.AsQueryable();
            if (retVal == null)
                throw new HttpResponseException(HttpStatusCode.NotFound);

            return retVal;
        }

        // GET /api/customer/5
        public Customer Get(string selector)
        {
            Customer retValue = db.Customers.Single(o => o.CustomerID == selector);

            if (retValue == null)
            {
                throw new HttpResponseException(HttpStatusCode.NotFound);
            }
            return retValue;
        }

    }

Now run the project, open a web browser and request customer resource, e.g:


If you hit the following error:
"Exception Details: System.Xml.XmlException: You must write an attribute 'type'='object' after writing the attribute with local name '__type'."
 It’s because you did not remove the “virtual” keyword from the generated entities classes as indicated above. This is a bug in MS's framework.
At this point you should have a RESTful web service that can handle simple HTTP GETs with SData Single or Collections URLs. To add support for complex SData URLs/Queries follow the instructions I provided in my post:
SData URLs in MVC .NET Web API