OData with SQL Azure - OData Overview

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
1/19/2011 11:23:06 AM
OData stands for Open Data protocol. It's a REST-based web protocol for querying and updating data completely independent of the platform or source. OData accomplishes this by utilizing and enhancing existing web technologies such as HTTP, JavaScript Object Notation (JSON), and the Atom Publishing Protocol (AtomPub). Through OData, you can gain access to a multitude of different applications and services from a variety of sources including relational databases, file systems, and even content-management systems.

The OData protocol came about from experiences implementing AtomPub clients and servers in an assortment of products over the past few years. OData relies on URIs for resource identification, which provides consistent interoperation with the Web, committing to an HTTP-based and uniform interface for interacting with the different sources. OData is committed to the fundamental web principles; this gives OData its great ability to integrate and interoperate with a plethora of services, clients, tools, and servers.

It doesn't matter if you have a basic set of reference data or are architecting an enterprise-size web application: OData facilitates the exposure of your data and associated logic as OData feeds, thus making the data available to be consumed by any OData-aware consumers such as business intelligence tools and products as well as developer tools and libraries.

1. OData Producers

An OData producer is a service or application that exposes its data using the OData protocol. This article —pertains to SQL Azure and OData, SQL Azure can expose data as OData. But so can SQL Server Reporting Services and SharePoint 2010, among other applications. L

Many public (or live) OData services have been made available, which anyone can consume in an application. For example, Stack Overflow, NerdDinner, and even Netflix have partnered with Microsoft to create an OData API. You can view a complete list of such OData producers, or services, at

In the browser, you see a list of the categories by which you can browse or search for a movie offered by Netflix, as shown in Figure 1. You probably look at Figure 1 and think, "This looks a lot like WCF Data Services." That is correct, because, as stated earlier, OData facilitates the exposure of your data and associated logic as OData feeds, making it much easier via a standardized method to consume data regardless of the source or consuming application.

Thus, in Figure 1 you can see the categories via which you can search Netflix movie catalog. For example, you can see the different endpoints of the API through which to find a movie, such as Titles, People, Languages, and Genres.

You can begin navigating through the vast Netflix catalog by entering your query as a URI. For example, let's look at all the different genres offered by Netflix. The URI is

You're given a list of genres, each of which is in an <entry> element with the name of the genre in the <Name> element in the feed, shown in Figure 2.

Figure 1. Netflix catalog

Figure 2. Netflix genres

Figure 2 shows the Comedy genre. Additional information lets you know what you need to add to the URI to drill down into more detail. For example, look at the <id> element. If you copy the value of that element into your browser, you see the detailed information for that genre.

Continuing the Comedy example, let's return all the titles in the Comedy genre. To do that, you need to append the /Titles filter to the end of the URI:'Comedy')/Titles

The Netflix OData service returns all the information for the movies in the Comedy genre. Figure 3 shows one of the movies returned from the service, displayed in the browser.

Figure 3. Viewing Netflix titles

At this point you're just scratching the surface—you can go much further. Although this article isn't intended to be a complete OData tutorial, here are some basic examples of queries you can execute:

The key to knowing what to add to the URL to apply additional filters is in the information returned by the service. For example, let's modify the previous example as follows:$filter=Name%20eq%20'Brad%20Pitt'

On the resulting page, several <link> elements tell you what additional filters you can apply to your URI:

<link rel=""...
<link rel=""...
<link rel=""...

These links let you know the information by which you can filter the data.

2. OData Consumers

An OData consumer is an application that consumes data exposed via the OData protocol. An application that consumes OData can range from a simple web browser (as you've just seen) to an enterprise custom application. The follow is a list of the consumers that support the OData protocol:

  • Browsers. Most modern browsers allow you to browse OData services and Atom-based feeds.

  • OData Explorer. A Silverlight application that allows you to browse OData services.

  • Excel 2010. Via PowerPivot for Excel 2010. This is a plug-in to Excel that has OData support.

  • LINQPad. A tool for building interactive OData queries.

  • Client libraries. Programming libraries such as .NET, PHP, Java, and Windows Phone 7 that make it easy to consume OData services.

  • Sesame (an OData browser). A browser built by Fabrice Marguerie specifically for browsing OData.

  • OData Helper for webMatrix. Along with ASP.NET, let's you retrieve and update data from any service that exposes its data via the OData protocol.

There are several more supported client libraries. You can find a complete list of consumers at

OK, enough about OData. If you want to learn more, the OData home page is

You should spend some time reading up on OData and start playing with some of the services provided by the listed producers. When people began getting into web services and WCF services, there was an obvious learning curve involved in understanding and implementing these technologies. Not so much with OData—it has the great benefit of using existing technologies to build on, so understanding and implementing OData is much faster and simpler.

  •  SQL Server 2008 : Performing Nonlogged DML Operations
  •  SQL Server 2008 : Using the OUTPUT Clause with the MERGE Statement
  •  SQL Server 2008 : Returning Data from DML Operations Using the OUTPUT Clause
  •  SQL Server 2008: Working with System Databases
  •  SQL Server 2008 : Using @@IDENTITY and NEWID Functions in DML Statements
  •  SQL Server 2008 : Using Advanced Functionality with DML - Introduction
  •  Defensive Database Programming with SQL Server: The Ticket-Tracking System (part 2) - Removing the performance hit of ON UPDATE CASCADE
  •  Defensive Database Programming with SQL Server: The Ticket-Tracking System (part 1) - Enforcing business rules using constraints only
  •  SQL Server 2008 : Working with DML Queries - Using the MERGE Statement
  •  Defensive Database Programming with SQL Server : Client-side Error Handling
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    Video Sports
    programming4us programming4us