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:
- The function constructs an object in JSON notation.
- The JSON object is posted via an AJAX request to a WCF endpoint.
- The specified OperationContract method maps the JSON object to a pre-defined DataContract object.
- An Entity object is instantiated.
- A function associated with the Entity is invoked using parameters represented by the DataContract’s DataMember elements.
- A stored procedure mapped to the Entity object function is called and passed the appropriate parameters.
- The procedure executes and returns a result set (if data is the specified output, otherwise no data or a status message is returned).
- The result set is mapped to a collection of complex entity types.
- The entity collection is transformed into a JSON object.
- The OperationContract returns the object as a string.
- The data is extracted from the JSON string.
- The UI is updated to display the final result.
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).
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.
Create a Web Application
Begin by creating a web application to host the app and web service (these can be de-coupled if necessary but be aware of cross-domain issues when doing so).
Open Visual Studio 2012, create a new project and select Cloud > Windows Azure Cloud Service.
Visual Studio will prompt you to select a role type. Any web role will work – the ASP.NET role is used here but an MVC role will work just as well if you prefer that development model. Select the desired role, click the right > arrow, and edit the solution name:
Click OK and VS will generate a solution with projects for the web role and Azure deployment.
Add an Entity Model
Add an ADO.NET Entity Data Model to the web role project. This model will provide for the automatic generation of data objects from SQL and saves a tremendous amount of time and effort compared to manual creation of data access classes.
If you wish to import an entire data structure and let the wizard wire up all the necessary objects, you can select "Generate from database" at this stage. Otherwise, start with an empty model and manually add the objects later.
You may be prompted with a few security warnings at this point. This occurs when VS tries to open the model design surface for the first time. You can safely ignore these warnings.
Connect to SQL and Import Objects
Right-click on the Entity Model design surface and select Update Model from Database. At this point you will be prompted to create a new data connection.
Click New Connection then select Microsoft SQL Server and click Continue.
In the server name, either select the local SQL server instance or, if you are working with a SQL Azure database, input the fully-qualified URL of the database (you can find your connection information in the Azure portal by going to the SQL Databases tab, selecting the database from the list, and clicking Show connection strings on the right). Enter the login credentials and click Test Connection (remember that Azure uses SQL Server authentication and the user name will be in the format [UserName]@[ DatabaseName] – with DatabaseName being the unqualified string name of the SQL database instance as shown in the Azure portal).
If you haven’t already added your machine to the Azure firewall list, you will likely receive the following error:
This can be corrected by selecting the database and clicking Manage on the Actions tab at the bottom of the Azure dashboard. It will prompt you to add your current IP address to the firewall rules.
Once the connection is successful, clicking OK will populate the data connection fields in the wizard. You can select to either embed the connection password in the web.config settings or set it programmatically then click Next.
If all the settings are correct and you don’t run into the Pre-Login handshake SSL error, the wizard will then display a list of objects from the database:
Expand the Tables node and select a table to import. Click Finish and the table object will be imported and added to the model design surface:
Add Function Imports
Mapping entity objects to stored procedures is accomplished via Function Imports in the Entity Framework. To wire up a procedure, select the Model Browser tab at the bottom of the Solution Explorer window. This will display the model hierarchy.
Begin the procedure mapping by first importing a stored procedure from the database. Right-click in the model designer and select Update Model from Database. Expand the Stored Procedures node, locate the desired procedure, and click Finish.
The procedure will be imported and added to the list of function imports. By default, this will create a function with the same name as the procedure and a new complex type based on the fields returned by the procedure. If a direct mapping to an entity object is desired, rather than a separate complex type, edit the properties of the function, select Entities, and then choose the appropriate object (NOTE: the object must have the same properties and data types as those returned by the procedure for the mapping to succeed).
Repeat this process for each table and procedure in the database; alternatively, you can do a mass import of tables and procedures in the Update step then clean up any individual objects that require different return types.
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).