At SharePoint Conference 2013 I demonstrated an Azure-hosted help desk application for Office365 and SharePoint 2013 using the new app model framework. The core data for the application was stored in a SQL Azure database with some SharePoint data integration via REST. The entire UI was coded in JavaScript and HTML. This presented some interesting challenges during the development process and led to the creation of a repeatable pattern that I have used in several subsequent projects. I’ve received a number of requests to share the code but I felt that it deserved a more complete explanation and walkthrough than a sample Visual Studio solution can provide on its own. This series of posts lays out the thought process behind the pattern along with sample code and implementation details so developers can determine if it is suitable for their purposes and how best to modify it in each unique situation.

Overview

Any development pattern is only as good as the situation(s) it was originally designed for. My scenario for the initial application was very specific – a SharePoint app that could run both in the cloud and on-premise, with the provider-hosted web and database both in Azure, featuring a modern UI that only used server-side code where absolutely necessary (such as authorization and the Client Object Model). It should be noted, however, that the same pattern has been applied to non-SharePoint projects outside of Azure with very little modification. Essentially, the only requirement is an HTML + JavaScript web site built with Visual Studio that needs to exchange data with a SQL server database; for purposes of the pattern, SharePoint and Azure are simply extensions.

Conceptually, the pattern seeks to achieve three primary objectives: 1) Provide a consistent methodology for CRUD operations from client-side script to/from a remote data repository, 2) Define a middle-tier service layer with uniform object and method structures, and 3) Minimize the amount of manual coding required to create and manage core data access classes. In practice, the pattern is primarily intended to ease the process of client and server side code integration; secondarily, it aims to make each operation as simple as copy => paste => tweak => deploy. From this perspective, it is not an architectural pattern in the traditional sense of MVC or SOA but rather a simplistic design pattern – an approach to dealing with specific implementation challenges in a consistent and repeatable manner.

The scenarios to which the pattern applies are somewhat narrowly focused in that they deal almost entirely with components within the overall Microsoft .NET development stack – AJAX, WCF, C#, SQL, and, of course, Visual Studio – but within that stack these are widely used and nearly universal in all modern web development projects designed to run on Windows servers. Bringing SharePoint back into the equation, this collection of components, although not required for the 2013 app model, will almost certainly comprise the vast majority of all apps both in the cloud and on-premise, making the pattern especially useful in that context. For applications such as these to which the pattern is applicable, the basic execution process resembles the following:

With a corresponding return path:

To better understand the process, consider a use case in which a user must click a button to display a set of tabular data in a grid. The following activities would then take place behind the scenes in order to achieve the desired result:

1. A JavaScript function attached to the HTML button element fires in response to the button click event.
2. The function constructs an object in JSON notation.
3. The JSON object is posted via an AJAX request to a WCF endpoint.
4. The specified OperationContract method maps the JSON object to a pre-defined DataContract object.
5. An Entity object is instantiated.
6. A function associated with the Entity is invoked using parameters represented by the DataContract’s DataMember elements.
7. A stored procedure mapped to the Entity object function is called and passed the appropriate parameters.
8. The procedure executes and returns a result set (if data is the specified output, otherwise no data or a status message is returned).
9. The result set is mapped to a collection of complex entity types.
10. The entity collection is transformed into a JSON object.
11. The OperationContract returns the object as a string.
12. An asychronous success or failure handler in JavaScript receives the response.
13. The data is extracted from the JSON string.
14. JQuery (or direct JavaScript) is used to manipulate objects in the DOM or display informational dialogs.
15. The UI is updated to display the final result.

The use case and execution process can be distilled down into an architecture that consists of three tiers – a backend data tier based on the ADO.NET Entity Framework, a middle tier using WCF web services, and a front-end tier using JavaScript, JQuery and AJAX. Naturally, many developers will disagree with various aspects of the pattern and the chosen implementation methods. This is to be expected – everyone designs applications in their own unique fashion. The pattern is simply a guideline designed to make repetitive coding tasks easier and less cumbersome. Any part can be switched out for an alternate method that satisfies the same requirements. For example, some .NET developers like the Entity Framework and others don’t, preferring to write their own data access classes. A solid argument can be made that EF is too rigid and the generated code overly verbose. These are fair criticisms; however, many developers find it to be much more efficient than manual code generation and it was selected for the pattern solely on this basis. If you prefer to roll your own data methods or use some other framework, skip Part One and simply wire up the WCF methods in Part Two to something else more to your liking.

On a similar note, WCF may not be the most desirable middle-tier component. In this respect, the pattern is a bit more rigid – WCF is the glue that ties the front end to the back end and, until something else comes along, is the preferred SOA solution for .NET apps. That being said, developers who prefer the legacy ASMX approach can continue to use it as part of the pattern but the provided code samples will all be based upon WCF.

The front-end is the most flexible part of the pattern. Any application that can call a remote web service and parse JSON data can serve as the client. This includes platform-specific mobile apps (IOS, Android, Windows Phone), rich applications (Silverlight, WPF, Windows 8) and any modern web browser. The pattern was designed specifically to eliminate any client-side platform dependencies (other than JSON but even that could be swapped for XML with some minor changes to the middle tier).

Implementation

In the context of building SharePoint apps, the most logical implementation of the pattern is an IIS web server running in the cloud or on-premise that hosts the web application and WCF service. A SharePoint 2013 or Office365 site provides access to the app, secured via OAuth or S2S, which exposes a responsive UI with little or no code behind (or at least the minimal amount of code required for authorization and context management). All data access is facilitated by the WCF service via JavaScript AJAX calls using JSON. Access to SharePoint is handled via REST and the client object model (which is not part of the pattern but could easily be integrated). Regardless of the actual functionality provided by the app, this should be a fairly consistent theme, with minor variations (such as the front-end hosting platform), for most apps that end up either in the app store or corporate catalog.

The first implementation concern is the data layer, which involves the creation of Entity Framework objects and functions. First, an entity class is created, then a connection to a database established, followed by the import of data objects (such as tables and stored procedures). Behind the scenes, the wizard will generate code for each set of objects which can then be accessed from the service layer. Although the sample steps shown in this series involve an ASP.NET web site running in an Azure web role, the data model can exist independently or in combination with the service layer. The latter implementation scenario is mostly applicable to rich clients and mobile devices. The second phase, covered in Part Two, will focus on the WCF service, data contracts, operation contracts and configuration parameters. Accessing the data asynchronously and wiring up the final UI elements will be covered in Part Three.

The above process creates the basic data structure for the app. Whenever new tables or procedures are added during the development process, the Update Model from Database step is repeated to create the necessary objects and functions. If any changes are made in the underlying data structure (i.e. columns are added to a table or the data type of a field returned by a procedure changes), use the Refresh tab in the update wizard to modify the generated code. Be aware that any manual changes made to the underlying code files will be overwritten during a refresh operation. To make minor changes to a function, edit the function in the Model Browser and update the generated complex type using the Update button or simply create a new one (mappings to Entity objects are necessarily static so use a complex type if there is no direct column mapping or if you desire the ability to change the result set without changing core entities).

In Part Two of this series a WCF service will be created that utilizes the entity container and objects to serve as a middle-tier data broker between the UI and the database. In Part Three the middle and backend tiers will be exposed to the front-end JavaScript using AJAX calls and JQuery, allowing for a responsive UI with no code-behind or server dependencies. The full Visual Studio solution will be available for download at the conclusion of Part Three.

Additional References

Getting Started with Windows Azure SQL Databases

Windows Azure and SQL Database Tutorials

How to Create and Deploy a Windows Azure Cloud Service

ADO.NET Entity Framework

Get Started with the Entity Framework