Learn about the OData protocol and how you can combine SQL Azure databases with WCF Data Services to create OData endpoints that utilize a cloud-based database.
OData is an interesting new protocol that allows you to expose relational database data over the web using a REST-based interface. In addition to just publishing data over an XML presentation format, OData allows querying database data using filters. These filters allow you to work with portions of the database data at a time, even if the underlying dataset is large.
Although these features – web-based querying and filtering – alone would be very useful for many applications, there is more to OData. In fact, OData allows you to also modify, delete and insert data, if you wish to allow this to your users. Security is an integral part of OData solutions.
In ASP.NET applications OData support is implemented technically using WCF Data Services technology. This technology takes for instance an ADO.NET Entity Framework data model, and exposes it to the web with settings you specify. Then, either using an interactive web browser or a suitable client application, you can access the database data over the HTTP protocol.
Although traditional on-premises ASP.NET and WCF applications continue to be work horses for many years, cloud based applications are galloping into the view. When working with Microsoft technologies, cloud computing means the Azure platform. The good news is that you can also expose data sources using OData from within your Azure applications.
This article explores the basics of OData, and more importantly, how you can expose OData endpoints from within your applications. Code shown is written in C#. To develop the applications walked through in this article, you will need Visual Studio 2010 and .NET 4.0, an Azure account, and the latest Azure SDK Kit, currently in version 1.2 (implicitly, this requires Windows Vista or Windows 7). See the Links section for download details.
Introduction to OData
OData (“Open Data”) is, as its name implies, an open standard for sharing database data over the Internet. Technically speaking, it uses the HTTP protocol to allow users and applications to access the data published through the OData endpoint. And like you learned earlier, OData allows intelligent querying of data directly with filters specified in the URL addresses.
In addition to reading, the OData protocol also allows modifying data on the server, according to specified user rights. Applications like Excel 2010 (with its PowerPivot feature) and most web browsers can already access OData directly, and so can libraries in Silverlight 4, PHP and Windows Phone 7, for instance
All this sounds good, but how do OData based systems tick? Take a look at Figure 1, where a WCF Data Service application written with .NET 4.0 shows an OData endpoint in a browser.
Figure 1: An OData endpoint exposed by a WCF Data Service in a web browser
The application exposes the Northwind sample database along with its multiple tables. For instance, to retrieve all customer details available in the Customers table, you could use the following URL:
http://myserver/NorthwindDataService.svc/Customers
This is a basic query that fetches all records from the table. The data is published in XML based ATOM format, which is commonly used for RSS feeds for example. Here is a shortened example to show the bare essentials:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml_base="http://myserver/NorthwindDataService.svc/"
xmlns_d="http://schemas.microsoft.com/ado/2007/08/dataservices"
xmlns_m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
>
<title type="text">Customers</title>
<id>http://myserver/NorthwindDataService.svc/Customers</id>
<updated>2010-09-18T04:12:14Z</updated>
...
<content type="application/xml">
<m:properties>
<d:CustomerID>ALFKI</d:CustomerID>
<d:CompanyName>Alfreds Futterkiste</d:CompanyName>
<d:ContactName>Maria Anders</d:ContactName>
<d:ContactTitle>Sales Representative</d:ContactTitle>
<d:Address>Obere Str. 57</d:Address>
<d:City>Berlin</d:City>
<d:Region m_null="true" />
<d:PostalCode>12209</d:PostalCode>
<d:Country>Germany</d:Country>
<d:Phone>030-0074321</d:Phone>
<d:Fax>030-0076545</d:Fax>
</m:properties>
</content>
...
Using an XML based format means that the data downloaded is quite verbose, using a lot more bandwidth than for example SQL Server’s own TDS (Tabular Data Stream) protocol. However, interoperability is the key with OData.
If the Customer table would contain hundreds or thousands of records, then retrieving all records with a single query becomes impractical. To filter the data and for example return all customers starting with the letter A, use the following URL:
http://myserver/NorthwindDataService.svc/Customers?$filter=startswith(CompanyName, 'A') eq true
In addition to simple queries like this, you can also select for instance top five or ten records, sort based on a given column (both ascending and descending), and use logical operations such as NOT, AND and OR. This gives you a lot of flexibility in working with data sources exposed through the OData protocol.
Different Ways to Work with OData on the Cloud
When speaking of OData and the cloud, you should be aware that there is no single architecture that alone fits all needs. In fact, you can easily come up with different options, Figure 2 showing you three of them. The two right-most options have one thing in common: they run the actual data source (the SQL Server database) inside the cloud, but the location of the WCF Data Service varies.
Figure 2: Three common architectures for working with OData
That is, the WCF data service can run on your own servers and fetch data from the database on the cloud. This works as long as there is a TCP/IP connection to Microsoft’s data centers from your own location. This is important to keep in mind, as you cannot guarantee connection speeds or latencies when working through the open Internet.
To create a proof-of-concept (POC) application, you could start with the hybrid option shown in the previous figure. In this option, the database sits in the cloud, and your application on your own computers or servers. To develop such an application, you will need to have an active Azure subscription. Using the web management console for SQL Azure (sql.azure.com), create a suitable database to which you want to connect, and then migrate some data to the newly created database (see another recent article “Migrating your database applications to SQL Azure” by Jani Järvinen at DatabaseJournal.com for tips on migration issues).
While you are working with the SQL Azure management portal, take a note of the connection string to your database. You can find the connection string under the Server Administration web management page, selecting the database of your choice and finally clicking the Connection Strings button at the bottom. Usually, this is in the format like the following:
Server=tcp:nksrjisgiw.database.windows.net;Database=Northwind;User ID=username@nksrjisgiw;Password=mysecret;Trusted_Connection=False;Encrypt=True;
Inside Visual Studio, you can use for instance the Server Explorer window to test access to your SQL Azure database. Or, you could use SQL Server Management Studio to do the same (Figure 3). With an interactive connection possibility, it is easy to make sure everything (such as Azure firewall policies) is set up correctly.
Figure 3: SQL Server Management Studio 2008 R2 can be used to connect to SQL Azure
Creating a WCF Data Service Wrapper for an SQL Azure Database
Creating an OData endpoint using WCF technologies is easiest using the latest Visual Studio version 2010. Start or open an ASP.NET web project of your choice, for instance a traditional ASP.NET web application or a newer ASP.NET MVC 2 application. A WCF Data Service, which in turn is able to publish an OData endpoint of the given data source, requires a suitable data model within your application.
Although you can use for instance LINQ to Sql as your data model for a WCF Data Service, an ADO.NET Entity Framework data model is a common choice today. An Entity Framework model works well for the sample application, so you should continue by adding an Entity Framework model to your ASP.NET project. Since you want to connect to your SQL Azure database, simply let Visual Studio use the connection string that you grabbed from the SQL Azure web management portal. After this, you should have a suitable data model inside your project (Figure 4).
Figure 4: A simple ADO.NET Entity Framework data model (entity data model) in Visual Studio
After the entity data model has been successfully created, you can publish it through a WCF Data Service. From Visual Studio’s Project menu, choose the Add New Item command, and from the opening dialog box, navigate to the Web category on the left, and choose the WCF Data Service icon (the one with a person in it). After clicking OK, Visual Studio will add a set of files and references to your project. Visual Studio will also open a skeleton code file for you, which you then must edit.
Basically, you must include your data model to the class definition, and then set various options for things like protocol version, security, access control, and so on. Once edited the finished code file should look like the following:
using System.Data.Services.Common;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;
namespace SqlAzureODataWebApplication
{
public class NorthwindDataService :
DataService
{
public static void InitializeService(
DataServiceConfiguration config)
{
config.SetEntitySetAccessRule("*",
EntitySetRights.AllRead);
config.DataServiceBehavior.MaxProtocolVersion =
DataServiceProtocolVersion.V2;
}
}
}
At this point, your application is ready to be tested. Run your application, and your web browser should automatically launch. In your browser, you should see an XML presentation of your data, coming straight from the SQL Azure cloud database (Figure 5)!
Figure 5: An example OData endpoint showing data coming directly from SQL Azure
Be also sure to test out URL addresses like the following (note that IE cannot display the first URL directly, but view the source of the resulting page to see the details):
http://localhost:1234/NorthwindDataService.svc/Customers('ALFKI')
http://localhost:1234/NorthwindDataService.svc/Customers?$filter=Country eq 'USA'
http://localhost:1234/NorthwindDataService.svc/Customers?$orderby=City&$top=10
Query strings can have lots of power, can’t they?
Conclusion
OData is a new protocol for sharing data over the web. Although still much in its infancy, the protocol has lots of potential, especially when it comes to Microsoft platforms. For developers, OData gives a cross-platform and cross-language way of sharing relational data using web-friendly technologies such as HTTP, JSON and XML/Atom.
For .NET developers, OData is easy to work with. Publishing an OData endpoint is done using WCF Data Service technology, and with Visual Studio 2010, this doesn’t require many clicks. Internally inside your application, you will need to be using some object-oriented database technology, such as LINQ to SQL or ADO.NET Entity Framework.
On the client side, OData-published data can be explored in multiple ways. The good news is that .NET developers have many options: Silverlight 4 contains direct support for WCF Data Services, and in any other .NET application type, you can import an OData endpoint directly as a web service reference. This allows you to use technologies such as LINQ to query the data. Excel 2010 can do the trick for end-users.
Combining OData endpoints with a cloud-based database can give you several benefits. Firstly, excellent scalability allows you to publish large datasets over the OData protocol, meaning that you are not limited to data size of just a gigabyte or two. Secondly, decreased maintenance costs and the possibility to rapidly create databases for testing purposes are big pluses.
In this article, you learned about the OData protocol, and how you can combine SQL Azure databases with WCF Data Services to create OData endpoints that utilize a cloud based database. You also saw that Visual Studio 2010 is a great tool to build this kind of modern applications. It doesn’t require many clicks anymore to be able to use cloud databases in your applications, and to publish that data over the web. Happy hacking!
Links
OData protocol pages: http://www.odata.org/
Azure SDK 1.2 download page: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=21910585-8693-4185-826e-e658535940aa&displaylang=en
List of supported OData client applications: http://www.odata.org/consumers
OData Protocol URI Conventions: http://www.odata.org/developers/protocols/uri-conventions
The Atom Publishing Protocol: http://www.odata.org/media/6655/[mc-apdsu][1].htm
Sample Northwind OData endpoint: http://services.odata.org/Northwind/Northwind.svc/
