John Cleese on Creativity

When I talk about my job and software development in general I find there's a common misperception, even among fellow engineers & architects. People think being a software engineer, designing and writing good software is a methodical, clinical endeavor practiced by mathematicians working in perhaps tense environment with little humor.

In fact software development is mostly about creativity. The best engineers & architects are extremely creative people. The most successful software teams are teams that understand how to harness the creativity of the group.

Success is only mostly about creativity because once you have arrived at that elegant solution/design you must implement it. This means transitioning from that pondering state of mind to a purposeful, focused somewhat closed state required to turn that vision into functioning software.

John Cleese is fantastic in this absolutely brilliant talk on creativity. The video is very entertaining and provides great insight and tips on how to be creative and improve the creativity of organizations. Everyone in R&D should see it:

SData JSON Payloads & MVC .NET Web API

In a previous post I discussed handling SData URLs in MVC .NET Web API. It’s easy to support most of the SData query language in a Web API project if you use my SDataHandler library. The SDataHandler class library is a POC I’ve developed to illustrate how to add SData support to MVC .NET Web API projects.

The SDataHandler class provides/returns JSON payloads including some of the metadata specified by SData 2.0 specification. I should note the SData 2.0 specification has not been ratified yet so some of this might change a bit in the final SData 2.0 release.

If you’re not familiar with JSON or JSON in SData here’s a quick overview of how we represent data in SData with JSON:

A simple Customer object with following structure and values:

customername -> American Business Futures

addressline1 -> 123 Main Street

city -> Milwaukee

state -> WI

zipcode -> 90210

telephoneno -> (414) 555-4787

openorderamt -> 0

A JSON representation of this Customer would look something like:

“Customer”:

{

           "customername": "American Business Futures",

           "addressline1": "2131 N. 14th Street",

           "city": "Milwaukee",

           "state": "WI",

           "zipcode": "53205-1204",

           "telephoneno": "(414) 555-4787",

           "openorderamt": 0

}

A collection of Customers would be returned as a JSON array, so if a client requests a list of all Customers the payload returned would look like this:

{

  "$resources":

   [

       {

           "customername": "American Business Futures",

           "addressline1": "2131 N. 14th Street",

           "city": "Milwaukee",

           "state": "WI",

           "zipcode": "53205-1204",

           "telephoneno": "(414) 555-4787",

           "openorderamt": 0,

       },

       {

           "customername": "Dell Computers",

           "addressline1": "1st street",

           "city": "Irvine",

           "state": "CA",

           "zipcode": "92614",

           "telephoneno": "(414) 555-1234",

           "openorderamt": 0,

       },

       {

           "customername": "Jaretts Hardware",

           "addressline1": "2nd street",

           "city": "Irvine",

           "state": "MI",

           "zipcode": "92614",

           "telephoneno": "(414) 555-5678",

           "openorderamt": 0,

       }

]}

Notice the “$resources” array in the payload, SData keywords within the payload always start with a “$”. SData allows services to provide additional metadata like paging information, etc., along with the payload too. If you’re not familiar with paging in SData see the paging section of the SData web site 

An SData collection payload with paging metadata like counts, and links would look like this:

{

   "$totalResults": 1,

   "$startIndex": 1,

   "$itemsPerPage": 26,

   "$next": "http://localhost:1698/sdata/Customers",

   "$previous": "",

   "$first": "http://localhost:1698/sdata/Customers",

   "$last": "http://localhost:1698/sdata/Customers",

   "$resources":

   [

       {

           "customername": "American Business Futures",

           "addressline1": "2131 N. 14th Street",

           "city": "Milwaukee",

           "state": "WI",

           "zipcode": "53205-1204",

           "telephoneno": "(414) 555-4787",

           "openorderamt": 0,

       },

       {

           "customername": "Dell Computers",

           "addressline1": "1st street",

           "city": "Irvine",

           "state": "CA",

           "zipcode": "92614",

           "telephoneno": "(414) 555-1234",

           "openorderamt": 0,

       },

       {

           "customername": "Jaretts Hardware",

           "addressline1": "2nd street",

           "city": "Irvine",

           "state": "MI",

           "zipcode": "92614",

           "telephoneno": "(414) 555-5678",

           "openorderamt": 0,

       }

]}

Typically SData providers will also return information like the URL for this collection/resource and an entry might also indicate a key. E.G:

{

   "$baseurl": "http://localhost:1698/sdata/Customers",

   "$totalResults": 1,

   "$startIndex": 1,

   "$itemsPerPage": 26,

   "$next": "http://localhost:1698/sdata/Customers",

   "$previous": "",

   "$first": "http://localhost:1698/sdata/Customers",

   "$last": "http://localhost:1698/sdata/Customers",

   "$resources":

   [

       {

           "customername": "American Business Futures",

           "addressline1": "2131 N. 14th Street",

           "city": "Milwaukee",

           "state": "WI",

           "zipcode": "53205-1204",

           "telephoneno": "(414) 555-4787",

           "openorderamt": 0,

           "$url": "Customer(303130303030303035)",

           "$key": "303130303030303035"

       },

       {

           "customername": "Dell Computers",

           "addressline1": "1st street",

           "city": "Irvine",

           "state": "CA",

           "zipcode": "92614",

           "telephoneno": "(414) 555-1234",

           "openorderamt": 0,

           "$url": "Customer(303130303030303033)",

           "$key": "303130303030303033"

       },

       {

           "customername": "Jaretts Hardware",

           "addressline1": "2nd street",

           "city": "Irvine",

           "state": "MI",

           "zipcode": "92614",

           "telephoneno": "(414) 555-5678",

           "openorderamt": 0,

           "$url": "Customer(303130303030303036)",

           "$key": "303130303030303036"

       },

The SDataHandler class will decorate your model and add this metadata to payloads return by your Web API project. As indicated in my post on SData URLs in MVC .NET Web API you can download and add my SDataHandler class to a Web API project and it will add support for SData queries and JSON payloads.

The source code and a build of Sage.SDataCommon is available on GitHub. Download the Sage.SDataCommon.dll from GitHub

Then just add a reference to the Sage.SDataCommon in your Web API project, and add the following code to your project's Global.asax.cs:

using Sage.SData.CommonComponents;

In the "Application_Start()" method somewhere after the Registering the routes add the following one line of code:

GlobalConfiguration.Configuration.MessageHandlers.Add(new SDataTransformHandler());

The "Application_Start()" method should look like:

protected void Application_Start()
{
 ....

        RegisterRoutes(RouteTable.Routes);

        GlobalConfiguration.Configuration.MessageHandlers.Add
new SDataHandler());

 ...
}

Now run your Web API service, open it in a web browser and request a resource. The SDataHandler class will return SData 2.0 JSON.

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

• The tools is discussed on the Showcase section of the SData website
• Download the SData Validation Tool 
• Download the documentation 

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:

http://localhost:49068/api/values/1

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#:

http://visualstudiogallery.msdn.microsoft.com/da740968-02f9-42a9-9ee4-1a9a06d896a2

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:

http://localhost:1489/sdata/contract/customer(ALFKI)

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