VSM Cover Story

Manipulate Data in the Cloud with ADO.NET

ADO.NET Data Services (formerly code-named "Project Astoria") delivers data from relational tables and Windows Live services to Web mashups and Visual Studio 2008 projects, including ASP.NET AJAX and Silverlight 2.0 rich Internet applications, as Representational State Transfer (REST) resources over HTTP in response to URI-based requests or LINQ to REST queries.

Technology Toolbox: VB.NET, C#, SQL Server 2005, ASP.NET, ADO.NET, XML, VB6, Other: See Additional Resources

Cloud computing became the buzzword du jour in 2007, as Google Inc. promoted its Google data (GData) APIs for interacting with an increasing list of free Web-based services and as Amazon.com rolled out Simple Storage Service (S3), Elastic Compute Cloud (EC2), and SimpleDB as low-cost, highly scalable, online (Web-hosted) data storage and computing services. Simple but versatile Web-based services that implement Representational State Transfer (REST) architecture began gaining ground steadily against their heavyweight Simple Object Access Protocol (SOAP) (WS-*) counterparts.

Unfortunately, SOAP Web services are burdened by a set of specifications that rival the thickness (and entertainment quotient) of the white pages for a large city. REST identifies data items (resources) by a unique Uniform Resource Identifier (URI) and accesses them by HTTP without requiring an additional messaging layer, such as SOAP. SQL Server Data Services (SSDS), Microsoft's new Web-facing relational database in the cloud that Ray Ozzie introduced at Microsoft's MIX08 conference, offers both REST and SOAP APIs. Browser-based Web apps with sophisticated UIs and data gleaned from the Web through RESTful services are known as rich Internet applications (RIAs). RIAs range from generic mashups created with Microsoft Popfly or Yahoo! Pipes to highly interactive Asynchronous JavaScript and XML (AJAX), Microsoft Silverlight, or Adobe Flash/AIR applications.

ADO.NET Data Services Framework is the new name for an expanded version of the Microsoft Data Programmability group's "Project Astoria," which debuted as an "incubator project" at the MIX07 conference in early May 2007. ADO.NET Data Services (without the Framework suffix), which almost everyone calls Astoria, promised a new approach to deploying relational data over on-premises intranets or the Internet "cloud." Astoria's DataService (formerly WebDataService) objects respond to HTTP POST, GET, PUT, and DELETE verbs by performing SQL-like CREATE, RETRIEVE, UPDATE, and DELETE operations on URI-addressable REST resource items or collections.

I'll describe Astoria's data service and client-side components, provide details on data formats, and walk you through creating a data service. Then I'll explain how to access data from a browser by composing URI queries, and I'll show you how to write Astoria clients for Windows or Web Forms. I'll also demonstrate Language Integrated Query (LINQ) to REST query syntax and data updates with POST, PUT, and DELETE methods. Finally, I'll outline a potential "Astoria Offline" feature and explain how Astoria will relate to SSDS when Microsoft releases the service for general use in 2009's first half. You can download a sample NwindDataService.sln Web app and NwinDSClient.sln WinForm client that demonstrate Astoria's basic feature set.

REST applications use client/server architecture and are stateless by definition; they transport requests and return resources over HTTP (see Additional Resources). The original Project Astoria supported the ADO.NET Entity Framework as the server's data source and Plain Old XML (POX) as the data format. An early read-only demo, accessible from the ADO.NET Data Services page on Microsoft Live Labs, returns POX-formatted data from online Northwind, AdventureWorks, Encarta, and TagSpace data sources (see Additional Resources). The December 2007 community technology preview (CTP) introduced Atom 1.0 Syndication (Atom) and Publication (AtomPub) formats, as well as JavaScript Object Notation (JSON) that Windows Communication Foundation (WCF) 3.5 added (see Additional Resources). The CTP dropped POX, but the Astoria team promises its support for the final release. Atom, AtomPub, JSON, and POX are widely accepted as truly "open" data formats; SSDS supports POX, and Atom is slated for the release version. JSON is the preferred data format for AJAX-aware ASP.NET and Silverlight applications.

Astoria's Atom Data Format
Today's Web mashups primarily consume and combine feed data in RSS, Atom, or both formats that's independent of the data's origin. The Astoria team chose Atom 1.0 over RSS as the data format because the Atom specification is more easily extensible than RSS 2.0 and the related AtomPub format supports updates to <feed> and <entry> elements (see Figure 1). The popular Google Data GData APIs for Google Base, Google Apps, Blogger, Picasa, YouTube, and other services also use Atom and AtomPub as default data formats; RSS is optional. GData for Google Base primarily uses query strings of IDs or attribute values to return public "snippets" or private <entry> collections, which Google calls "items," but it also supports query composition with arcane semantics. Google restricts the number and names of Base attributes to support a fixed set of 16 item types ranging from Course Schedules to Wanted Ads, whereas Astoria has no limitation to attributes you specify. The implementations and extensions Microsoft and Google make to Atom differ, but their combined support for Atom and AtomPub assures increasing popularity of REST-based Web services that use these formats.

The Astoria Client library enables Windows or Web forms clients to incorporate an autogenerated object model for specific types of Atom resources. The client's DataServiceContext.DataServiceQuery() method executes LINQ to REST queries that return strongly typed IQueryable<T> sequences. LINQ to REST, which supports most LINQ Standard Query Operators (SQOs), translates LINQ query expressions or method call syntax into the appropriate Astoria-style URI request, which I'll describe shortly. The library also simplifies data binding and update semantics by treating resources with associations (relationships) as members of an object graph. The new ASP.NET DataServiceDataSource server control makes binding to an Astoria data source as easy as binding the LinqDataSource to a LINQ to SQL DataContext. The team released the first Astoria Silverlight 1.1 Client API in August 2007. The Microsoft ASP.NET 3.5 Extensions Preview (Dec 2007) – ADO.NET Data Services Silverlight Add-On download for the Silverlight 1.1 Alpha September Refresh followed in January 2008. The updated Add-On for the Silverlight 2.0 beta became available a few weeks after MIX08 (see Additional Resources).

The Astoria Client library now supports any data provider that implements IQueryable<T> and, for optional data updating, IUpdatable<T> interfaces. David Treadwell, corporate vice president for Live Platform Services, announced in late February 2008 that Atom and AtomPub protocols will be the "future direction" for Windows Live services. Windows Live Photo API and a new Application-Based Storage service will be the first hosted services to fully integrate with the Astoria Client library. Treadwell calls Application-Based Storage "an experimental API which allows application developers to store a small amount of state/configuration data in the Windows Live data centers on behalf of a user." Pablo Castro, Astoria's software architect, presented a session titled "Accessing Windows Live Services via AtomPub" at MIX08 that described "how all Windows Live services can be accessed using AtomPub." The session included a live demo of Atom URIs accessing photos from a Windows Live Spaces account (see Additional Resources).

Create and Test an Astoria Web Service
Creating an Astoria Web service from an existing Entity Data Model (EDM) is a snap with Visual Studio (VS) 2008. First, download and install the latest ADO.NET Entity Framework beta release, Entity Framework Tools CTP, and ADO.NET Data Services Framework CTP to add ADO.NET Entity Data Model and ADO.NET Data Service templates to your Add New Item dialog (see Additional Resources). Change each entity name to the singular version of the source table name if you want this article's URI examples to execute without modification.

Create a new file-system Web site or Web Application and then follow the instructions in my "Model Domain Objects with the Entity Framework" article from the March 2008 issue (you can access it online by using Locator+ code: VS0803RJ_T) to create an Entity Data Model (EDM) of the Northwind sample database (see Additional Resources). Press F5 to generate the required Northwind.ssdl, Northwind.msl, and Northwind.csdl schema files in your ...\Debug\bin folder and the Northwind.cs or .vb partial class file in the project folder.

Open the Add New Object dialog, select ADO.NET Data Service, change its name to Northwind.svc, click Add to add Northwind.svc and Northwind.svc.cs or .vb to your project, and then open the file. Add the EDM using Northwind.Model or Imports Northwind.Model directives, and change the class declaration line to this:

public class Northwind :   

ADO.NET Data Services are completely locked down by default, so uncomment the config.SetEntitySetAccessRule (formerly config.SetResourceContainerAccessRule) line and change it as shown here to temporarily enable read/write operations on all entities:


That's all you need to do to create and test a basic ADO.NET Data Service. Press F5 to build and run the service automatically and display the top-level service metadata -- a list of EntitySets (collections) and their aliases in Internet Explorer or Firefox 2+:

<?xml version="1.0" encoding="utf-8" 
      standalone="yes" ?> 
<service xml:base=
      <collection href="Categories">
      <collection href="Customers">
      <collection href="Employees">
      <collection href="Order_Details">
      <collection href="Orders">
      <collection href="Products">
      <collection href="Shippers">
      <collection href="Suppliers">


One of the benefits of RESTful Astoria services is that you can test them easily by typing URIs in a browser's address textbox. However, you must disable IE7+'s Feeds feature to view Atom content with the XML stylesheet. To disable Feeds, open the Internet Options dialog, click Settings on the Contents tab, clear the Turn On Feed Reading View checkbox, click OK twice, and re-open IE. I haven't found an equally simple means to disable Firefox 2.0+'s Feeds feature. You can select the Web server's Specific Port option in the project's Web properties page and accept the 52660 default or supply a different number.

Here's the generalized URI query syntax for returning EntitySets as <feed> groups and individual entity instances as <entry> groups:

http://server_name[:tcp_port]/service_name. _
   svc/entityset_name[(entity_key)[ _

For example, this syntax returns the Supplier entry for the Product specified by the ProductID value of the first Order_Details item for the Order entity with an OrderID of 10262 (see Figure 2):

http://localhost:52660/Northwind.svc/Orders(10262) _

This example's FK_Order_Order_Details association is one-to-many, and the FK_Order_Detail_Product and FK_Product_Supplier associations are many-to-one. If you omit the EntityKey argument, one-to-many associations will return a <feed> group that contains multiple <entry> groups.

DataService objects support a sophisticated URI-based query language that consists of a set of query options (see Table 1); logical, arithmetic, and grouping operators (see Table 2); and functions for date, math, and string data types, as well as object types (see Table 3). The $filter=property_name logical_operator value query option applies the equivalent of a WHERE clause constraint; the $expands=association_name query option delivers a nested hierarchical expansion of one-to-many associations. This statement returns an EntitySet that contains a <feed> group that has an <entry> group for each Order entity shipped to France:

http://localhost:52660/Northwind.svc/Orders _
   ?$filter=ShipCountry%20eq%20'France' _
The Order's <content> group, has <link> elements, that point to associated entities (see Listing 1). Linking eliminates problems that occur with XML serialization of

object graphs that contain cyclic references such as Order_Detail.Order (a many-to-one association or EntityReference), which creates a cycle with Order.Order_Details (a one-to-many association or EntitySet).

QueryInterceptor() methods enable enforcing data access policies for entities based on user identity or other dynamic properties by altering the incoming query or substituting a new query. ChangeInterceptor() methods can apply similar restrictions on entity updates (see Listing 2). You can substitute WCF Service Operation methods for composable URI queries if you want to restrict clients to a specific entity or EntitySet.

Build Object Graphs
You don't need to become a URI query linguist to use Astoria.

The Astoria Client library (System.Data.WebClient.dll), which setup installs to the Global Assembly Cache (GAC), provides DataContext and DataQuery objects that mirror the EDM's ObjectContext and ObjectQuery types. Installing the ADO.NET Data

Services CTP adds the DataSvcUtil.exe (formerly WebDataGen.exe) command-line tool to the installation folder. DataSvcUtil.exe's output file substitutes for a Service Reference to a SOAP Web service's WSDL file. Running DataSvcUtil.exe with these parameters generates a Northwind.cs partial class file:

DataSvcUtil.exe /mode:ClientClassGeneration 

Substituting vb for cs creates the corresponding VB classes. The file includes classes with CreateEntityType() methods for each EntityType in the top-level EntityContainer. The ModelName Entities partial class, NorthwindEntities for this example, includes a set of prebuilt constructors and CreateQuery<TEntity>("EntitySet") methods for each EntityType, which act as the data source for LINQ and REST queries against the EntitySet.

To create a client, open a new WinForm or Web Application project, add a reference to System.Data.Services.Client, build and run your data service, run DataSvcUtil.exe to create the partial class file, and add the file to your project to create types for the data source. Then add these directives to the code-behind file:

using System.Data.Services.Client;
using NorthwindModel;

URI queries require invoking the DataContext.DataQuery<T>() method and supplying the service URI's resource identification suffix. For example, run this code to create a simple URI query:

string url = "http://localhost:52660/Northwind.svc"
DataContext context = 
   new DataContext(url);
DataQuery<Category> query = 

Execute the resulting IEnumerable<Category> query by iterating it in the usual foreach loop or applying the ToList() method.

LINQ to REST queries require invoking the ModelNameEntities.DataQuery<T>() method with the same URL as that for URI queries:

NorthwindEntities entities = 
   new NorthwindEntities(url);
var query = from c in entities.CreateQuery<Category>
   ("/Categories?$expand=Products ") select c;

The NwindDSClient.sln client project executes three sample URI and four LINQ queries against NwindDataService.sln service, and it updates a Customer entity (see Figure 3).

Persist Updates
Caching a new entity and updating or deleting an entity in the cache and then saving the changes follows the same pattern as these operations in the Entity Framework or LINQ to SQL. These instructions insert a new Customer object:

DataContext context = 
   new DataContext(url);
context.MergeOption = MergeOption.AppendOnly;
Customer cust = new Customer();
cust.CustomerID = "BOGUS";
cust.CompanyName = "Bogus Software, Inc.";
context.AddObject("Customers", cust);

The DataContext.MergeOption enumeration is the same as the Entity Framework's ObjectContext.MergeOption: NoTracking disables entity tracking; AppendOnly (the default) inserts new entities only; OverwriteChanges enforces "last entry wins" concurrency; and PreserveChanges enables optimistic concurrency management with an ETag response header that contains a timestamp. To update an object, query to retrieve it, set changed property values, and invoke the DataContext.UpdateObject method:

WebDataContext context = 
   new WebDataContext(url);
context.MergeOption = 
DataQuery<Customer> query =
   ("/Customers?$filter=CustomerID eq 'BOGUS'");
List<Customer> custs = query.ToList();
if (custs.Count == 1)
   Customer cust = custs[0];
   cust.ContactName = "Joe Bogus";
   cust.ContactTitle = "President";

Deleting an object requires retrieving it and invoking the DataContext.DeleteObject(entity) and DataContext.SaveChanges() methods.

The Astoria client invokes HTTP POST, PUT, or DELETE methods instead of generating a Canonical Query Tree to hand off to an Entity SQL-aware data provider or a T-SQL batch command. The POST request for an INSERT operation goes to the appropriate EntitySet container -- http://localhost:52660/Northwind.svc/Customers for this example. The Atom payload must include the required properties to insert a new row in the data store:

<entry xmlns:ads=
   <content type="application/xml">
         Bogus Software, Inc.
      </ads: CompanyName > 

Update PUT operations go to the entity instance's URI http://localhost:52660/North-wind.svc/Customers('BOGUS'), for this example -- with <content> property subelements for modified values only. DELETE operations go to the same URI with no payload.

Controlling access to data services has two components: user authentication and group or user authorization. The Astoria client library relies on the hosting platform's infrastructure for user authentication. For example, you can use ASP.NET Forms, Basic HTTP, Live ID, or a custom authentication service. As mentioned earlier, all EntitySets are locked down by default. It's a recommended practice to enable access by all users to each EntitySet with the appropriate SetEntitySetAccessRule's EntitySetRights enumeration member. You then implement QueryInterceptors that invoke appropriate Roles.IsUserInRole("RoleName") methods for each EntitySet and allow or block the query.

Look into ADO.NET Data Services Framework's Future
Astoria Offline is the nickname of a candidate for a post-release add-on to ADO.NET Data Services Framework version 1 that enables saving changes while offline in a SQL Server Compact Edition (SSCE) 3.5 SP2+ instance. Upon reconnection, the user synchronizes with the online database using Microsoft Synchronization Framework CTP 2+, which became available for download on the second day of MIX08. The Sync Framework supports multiple sync providers, the first of which was ADO.NET's Sync Services. The Framework now handles NTFS/FAT file sync and FeedSync (formerly Simple Sharing Extensions or SSE) protocols. FeedSync is a specification for extensions to Atom 1.0 and RSS 2.0 that can synchronize any type of data that's serializable in a syndication format. This capability makes FeedSync ideal for synchronization with client/server hub-and-spoke or the peer-to-peer mesh architecture that Ray Ozzie eulogized in his MIX08 keynote. What makes Astoria Offline unique is its ability to autogenerate the SSCE database from scratch using metadata in the partial class file that DataSvcUtil.exe creates. Mike Flasko delivered a brief demonstration of Astoria Offline at MIX08 (see Additional Resources).

SSDS is a highly scalable implementation of SQL Server hosted in Microsoft data centers on thousands of interconnected, low-cost, commodity servers. The servers are configured as a self-managed, high-availability cluster of data nodes that enable subscribers to store and query a virtually unlimited amount of data. The servers also accommodate spikes in demand without excessive latency. For simplicity, SSDS employs a schemaless data model for entities of various kinds (types) implemented as property bags of name/value pairs. Property data types are limited initially to string, numeric, date/time, boolean, and binary. The REST and SOAP protocols use a LINQ-like query language, and the unit of update is a complete entity. Sync Framework and Astoria will play increasingly important roles as SSDS matures. According to Francois Ajenstadt, director of project management for SQL Server, the ADO.NET Data Services and SSDS APIs will become more closely aligned as SSDS's release date approaches. Soumitra Sengupta, a SQL Server architect, confirmed that SSDS "will support the full EDM/EF/ADO.NET v3 model in due time." (See his full blog post here.) The SSDS team has scheduled a public beta in July and production operations for first half of 2009; for more information, be sure to check out VSM's upcoming July 2008 issue.

The ADO.NET Data Services team promised at MIX08 that beta 1 would be available "in a few weeks," so it should be ready to download by the time you read this article, which was based on the interim build distributed on the MIX08 CD. If beta 1 is cooked, the ZIP file you download will unzip into an \AstoriaBeta folder. If not, code that's compatible with the December 2007 CTP will unzip into an \AstoriaCTP folder and be replaced by beta 1–compatible code shortly after the runtime bits become available.

The use of REST protocols and Atom formats by the ADO.NET Data Services Framework and SSDS, as well as Atom by FeedSync, represent significant steps by Microsoft toward adoption of Web standards for its Data Platform as a whole. SSDS's entity view of data and need for offline synchronization bodes well for adoption of the Entity Framework and future versions of ADO.NET Data Services as the preferred data access APIs for "data from the cloud."

comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.