OData: Using WCF Data Services to Access SQL Server

Microsoft has never been shy about creating new data-access technologies, starting with some of the earliest versions of Visual Basic and SQL Server to the current legion of options available on just about every client and server platform and network protocol. Some of these data-access technologies serve dedicated desktop applications, and others serve distributed, Internet-connected components, with every kind of mix in between.

Some of the most interesting recent data-access options have been of the web services variety, designed to communicate over HTTP to allow both inter-server CRUD (create, retrieve, update, and delete) operations and to perform data access from just about any client application device or application to a server. Even in this relatively narrow data-access niche, the technologies have flourished, including Simple Object Access Protocol (SOAP), WS-*, POX (plain old XML), and others, many or most of which Microsoft has had a hand in developing or promoting. All these technologies have served developers well, albeit each technology has some serious flaws that have been widely discussed and I won't rehash here. To make matters even more interesting, a variety of data formats have been used in these technologies, including various dreaded proprietary formats, the ever-popular XML, and the current favorite of web developers, JavaScript Object Notation (JSON).

This panoply of web-based data-access technologies and formats, along with the deep experience that wide usage has allowed, have set the stage for Microsoft's latest web-based data-access technology: Open Data Protocol (OData). Here we'll take a look at OData and explore how to use it via Microsoft's WCF Data Services to access SQL Server data in a simple project.

What Is OData?

OData is an open standard that was founded by Microsoft and a few other large enterprise software providers to develop and promote as a standard data-access technology. They released the current standard under Microsoft's Open Specification Promise, which allows anyone to create software that interoperates with OData implementations without fear that Microsoft will make a future claim against them. Taking their support for OData a step further, in mid-summer 2012 Microsoft and its fellow OData standard bearers joined the OASIS standards group and formed an OData Technical Committee for the purpose of standardizing OData.

The core goal of OData is to make the vast amounts of data locked in applications and formats on the web available to a much wider variety of applications. According to the OData website, OData is "a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today." As is necessary today to enable wide acceptance of new Internet standards, OData is based on existing web standards and technologies, specifically HTTP, Atom Publishing Protocol (AtomPub), and JSON. AtomPub is a variation of the Atom XML language used for web news feeds that provides additional capabilities to create and update web resources, such as data stores. AtomPub and JSON provide alternative data formats, both of which are supported in just about all web application technologies.

OData is designed to access and manage data in a variety of data stores, including relational databases and just about any other data store accessible from HTTP-based applications. It uses the Representational State Transfer (REST) architecture that's already widely used as a web service design model, which provides a simpler style than SOAP and Web Services Description Language (WSDL) for the same purposes. The bottom line is that OData is built on existing standards and design methodologies that will be familiar to developers and web service architects.

WCF Data Services

Under the standard, developers and vendors are free to implement OData as they choose. Microsoft's implementation is WCF Data Services, introduced in Visual Studio 2008 SP1. The API lets you expose data models over HTTP in a RESTful way, providing features that support querying, data paging, updates, and everything else you'd expect in a data-access technology.

Support for creating OData data sources on a server is built into both .NET Framework and Visual Studio. Microsoft provides client libraries for .NET, Silverlight, Ajax, PHP, and Java applications, which together provide broad support for consuming OData data in client applications. Microsoft includes support for OData through WCF Data Services in SQL Server 2008 R2 and later in limited ways, as well as in Windows Azure, PowerPivot for Excel, and SharePoint 2010, and plans to incorporate OData support in other products over time. Clearly, OData is Microsoft's current choice for data access on the web.

The way that WCF Data Services works with HTTP is pretty slick. Clients can use standard HTTP verbs such as POST, GET, PUT/PATCH, or DELETE to perform the equivalent CRUD actions or to navigate relationships between entities. The resources exposed by the data service are addressable with uniform resource identifiers (URIs), which are essentially the URL for the service with query string parameters that define the actions. The combination of the HTTP verb and the parameters fully defines the desired action and exemplifies the beauty and elegance of WCF Data Services.

In OData and WCF Data Services demo presentations, you'll often see the presenter write URIs in a browser address bar to perform various actions. Although this method is convenient for presentations, it is not the typical way in which you'd access a data service. Instead, you'll likely use a client library that automatically generates the URIs for various actions and shreds the data it receives back, although you are certainly welcome to write custom code that handles the data returned. As usual, a good library can hide much of the messiness of working with a low-level protocol such as OData. (Some of the articles listed in the Related Articles list provide examples of using libraries to work with OData.)

WCF Data Services Providers

Back in the glory days of service-oriented architecture (SOA), SQL Server 2005 introduced a feature called Native XML Web Services, which offered a way to create an HTTP endpoint for SOAP services implemented in SQL Server that didn't require any other server software to handle data-service requests. All you had to do was create the endpoint in SQL Server and start allowing client applications to directly access data via the HTTP endpoint. That feature is now gone from SQL Server 2012—despite Microsoft's typical policy of deprecating features for three version cycles before removing them. Why the rush to remove Native XML Web Services? Microsoft's Sean Kelly explained the reason on the MSDN SQL Server 2012 forum:

"The primary motivation for removing SOAP is that adoption for this feature dropped off significantly after WCF and it did not provide access to many of the best practices needed to build a robust, scalable and secure web service. Instead, our guidance is to leverage technologies such as .NET/WCF that provide a much more robust way to build services. This is especially the case for best practices and features related to scalability and security."

If you have any existing HTTP endpoints in a SQL Server 2008 instance, you'll need to convert them to another type of web service before you can upgrade to SQL Server 2012. (By the way, CREATE ENDPOINT is still supported in SQL Server 2012 to allow for database mirroring. Only the support for SOAP was removed.) You'll find support for OData here and there in SQL Server 2012, such as in PowerPivot. But what's really strange is that there is no native OData data source or destination in Integration Services. You could build your own, of course, but this seems to be an odd omission for a data-access technology that Microsoft is heavily developing and promoting.

Whatever the reason for the rapid discontinuation of HTTP endpoints for SOAP, a .NET application is now the preferred way to expose a WCF Data Service that accesses SQL Server. So you'll need to fire up Visual Studio 2008 SP1 or later and create an ASP.NET application to host the data service. You'll also need a data model to manage the data-access layer to the database. You can either use POCOs (plain old CLR objects) or something like Entity Framework (or LINQ to SQL, although that is a deprecated feature in the .NET Framework). By far, the easiest way to implement the model is with Entity Framework, and that's what I'll use here for the sample application.

Building a WCF Data Service Provider

For our example, we'll assume that you have a database with product information that you want to expose through a WCF Data Service. I'll use the AdventureWorksLT2012 sample database, which conveniently has Product and ProductCategory tables. The sample solution contains two projects. One project is for the data service and contains both an Entity Framework model and the data service definition that uses the model. The other project acts as the client that consumes the data service. I'll explore the client application later in this article.

Creating a simple data service is easy when you use the tools in Visual Studio, particularly if you build the service on top of Entity Framework entities. Figure 1 shows the WCF Data Service item template that I used to add the service to an ASP.NET Empty Web Application project. I named the service AWLTDataService.

Figure 1: Using the WCF Data Service Item Template in Visual Studio

The resulting AWLTDataService.svc.cs (or .vb for Visual Basic projects) code file defines an AWLTDataService class that inherits from a generic System.Data.Services.DataService class. You can make the code in this class as complicated as you want, but at a minimum you have to make two changes: Enter the name of the object containing the Product and ProductCategory entities, and set access rules on the object.

Listing 1 shows the modified code in the sample project, with the AWLTDataService class inheriting from DataService and a single access rule set to allow full access to all entities to everyone. (This is not a recommended access rule for production applications, but it will do for the sample. You can create very granular rules for different permissions on the various entities that your data service exposes.)

namespace AdventureWorks{    public class AWLTDataService : DataService    {    // This method is called only once to initialize service-wide policies.    public static void InitializeService(DataServiceConfiguration config)    {        // Set rules to indicate which entity sets and service operations are visible, updatable, etc.        // Following grants any one full access. Fine for a demo, but not recommended for production!!!        config.SetEntitySetAccessRule("*", EntitySetRights.All);        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;    }    }}

At this point, the AWLTDataService project is a fully functional WCF Data Service built on the OData standard. If you right-click the AWLTDataService.svc file in Solution Explorer and select View in Browser from the pop-up menu, you can explore the features of the data service. This opens the URL localhost:1486/AWLTDataService.svc/ in your default browser (your port number will likely be different) and displays the service information as XML, as shown in Figure 2 in Internet Explorer (IE).

Figure 2: AWLTDataService Information Displayed in Internet Explorer

Now you can begin to explore the OData features of the data service. For example, you could retrieve a list of all products in the catalog using the URL localhost:1486/AWLTDataService.svc/Products.

IE by default recognizes that the data is an Atom feed and applies a style to the data that isn't very useful for seeing what is going on. But you can change the view to enable you to examine the full set of Atom XML returned by the services, as shown in Figure 3. To do so, open IE's Internet Options dialog box, select the Content tab, and in the Feeds and Web Slices section click Settings and select the Turn on feed reading view check box. When you look at the AtomPub feed XML, you'll find one entry element for each project (the first few of which are collapsed in Figure 3) along with all the data and schema information.

Figure 3: Raw XML of the AtomPub Feed with All Product Information from AdventureWorksLT2012

OData defines a number of query options, the most useful of which is probably the $filter option. You could use the URL localhost:1486/AWLTDataService.svc/Products?$filter=Color eq 'Multi'to retrieve only the multicolored products in the database. The result is merely eight items returned rather than the 295 items returned by the previous query. You can use Fiddler or a similar tool to explore the web traffic going between the client and data service, as shown in Figure 4.

Figure 4: Using Fiddler to Explore WCF Data Service Traffic Between Client and Data Service Applications

OData supports a number of other query options, such as $orderby to sort the data, $select to retrieve a subset of available fields, and $top to retrieve the first n rows. It would be well worth your time to explore these options so that you understand what OData can do. But with a good client library that supports OData, such as the one built into the .NET Framework, you don't need to worry about manually forming these URLs in order to perform CRUD operations on the data.

Consuming a WCF Data Service

The nice thing about using an OData client library is that you'll be able to write OData queries and other data operations using the native data-access language of the client development tool. In .NET, that data-access language will be LINQ statements or expressions, which are built into both C# and Visual Basic.

The sample solution provided with this article includes a simple console application that demonstrates how to consume data from a WCF Data Service. The Consumer project includes a reference to the AWLTDataService data service in the AWDataService project, created using the Add Service Reference dialog box shown in Figure 5. To discover the features of the service, enter the URL and click the Go button. A benefit of using Entity Framework for the data model is that it provides everything needed for CRUD operations—otherwise you'd need to define service operations as methods and decorate them as data services. As you can see in Figure 5, the client now knows about the Products and ProductCategories entity sets available through the Entity Framework model.

Figure 5: Adding a Service Reference to the Client Consumer Application

Once you have the service reference set, you can simply instantiate an AWLTServiceReference.AWLTEntities object. The object name consists of the name of the data service reference I defined and the name of the Entity Framework entities object defined in the AWDataService project. Instantiating the object gives you access to the entities in the Entity Framework model and the underlying data, and you can write LINQ queries to your heart's content, as well as insert, update, and delete data.

Listing 2 shows the code in the Main method of the console consumer application that retrieves the multicolored products and displays the product name, category name, and list price for each of the eight products.

namespace Consumer{    class Program    {    static void Main(string[] args)    {        AWLTServiceReference.AWLTEntities db =        new AWLTServiceReference.AWLTEntities(new Uri("http://localhost:1486/AWLTDataService.svc"));             var multiColorProducts = db.Products.Where(p => p.Color == "Multi")        .Select(p => new { p.Name, p.ListPrice, Category = p.ProductCategory.Name });    foreach (var prod in multiColorProducts)        {        Console.WriteLine("{0} ({1}) - {2}", prod.Name, prod.Category, prod.ListPrice.ToString("c"));        }        Console.ReadLine();    }    }}

The application code isn't complex; mostly it consists of just a LINQ query and code to display the results. Figure 6 shows the results in the console window when you run the application.

Figure 6: Console Window Showing List of Multicolored Products Accessed Through a WCF Data Service

As you can see, using WCF Data Services in simple scenarios is very easy. If your needs are more complicated, the server and consumer components of WCF Data Services provide more complex features that you can take advantage of. This article only scratches the surface of what you can do using WCF Data Services.

The Future of OData

Now that the OData specification is in the hands of a standards body, we should soon start to see renewed progress in formalizing the standard: addressing issues people have raised, comments the community has made, and errors found. The OASIS OData Technical Committee is already exploring extensions to the standard in several areas:

The committee is also exploring the creation of a simplified JSON format, sometimes referred to as JSON-lite. The intent is not to replace or change the standard JSON data format but to adapt it for use with OData. It remains to be seen whether the committee can succeed with such a format and have it usable in applications where standard JSON is now so easy to use. The committee's main motivation is to be able to streamline the metadata necessary in the data stream to use a common annotation mechanism that reduces the overall size of the data.

An increasing number of large websites with data to spare have implemented OData services, including Netflix, various large cities around the world, eBay, Stack Overflow, and many others (you can find a much longer list on the OData website). There seems to be a growing momentum behind OData, and I suspect that momentum will increase as OASIS progresses with its work and starts showing results in the form of a refined and expanded specification.

The one competitor to OData seems to be the Google Data Protocol (GData). At a high level, GData is similar to OData, down to using XML feed syndication formats (both Atom and RSS) and using XML or JSON as the data format. In moving the standardization work to OASIS, Microsoft pledged to work with all comers to make OData the Internet data-access standard and openly invited Google to participate. Interest in GData seems to be waning, and as Google releases new web APIs, it seems to be moving away from GData. Additionally, Google has not updated its Google Data APIs page in more than a year.

At any rate, given the wide support for OData in both server and client environments, OData is today a viable data-access technology if you want to use HTTP as the communications protocol and rely on widely used Internet standard technologies. For those of us who have lived through and survived Microsoft's many data-access technologies over the years, OData might seem like just one more in a long series. But for the situations where OData is appropriate, in my view it's a keeper.