SQL Server 2008 : Using ADO.NET Data Services

4/23/2011 3:44:25 PM
ADO.NET Data Services (ADODS) is a platform for providing SQL Server data to websites, RIAs (such as Silverlight and Flash applications), and other Internet clients over standard HTTP using modern web development conventions.

Getting Set Up

To start using ADODS, you first need to download and install Visual Studio 2008 Service Pack 1, as well as the .NET Framework 3.5 Service Pack 1. These updates include support for the Microsoft Entity Framework (EF), ADODS core, and ADODS .NET client library. A second configuration step, required for viewing ADODS XML data in the examples that follow, is an Internet Explorer 8 settings change: using the Tools menu, click Internet Options. Click the Content tab and then click the Settings button in the Feeds and Web Slices group. On the Feeds and Web Slices dialog, uncheck the Turn on Feed Reading View check box. Click OK twice.


To work with data provided by ADODS services, you make HTTP requests, each of which must include three key parts:

  • A uniform resource identifier (URI), which addresses the data in question

  • An HTTP verb (either GET, POST, MERGE, PUT, or DELETE), which indicates the type of CRUD operation to be performed

  • An HTTP Accept header, which indicates the format of the data being sent or received

As of this writing, ADODS services provide data to clients in one of two formats:

  • Atom Publishing Protocol (AtomPub)— An XML format that acts as an application-level protocol for working with web resources. (This is the default ADODS response format. For more information on Atom, visit

  • JavaScript Object Notation (JSON)— A text-based format for representing serialized JavaScript objects. Many popular Asynchronous JavaScript and XML (AJAX) client libraries (including JQuery, Prototype, and YUI) include core support for working with JSON.

ADODS services rely on EF to provide an abstract mapping layer between a physical data model and CLR object model. EF is a general-purpose ORM (similar to a LINQ to SQL class) that works with a number of data providers, including SQL Server, Oracle, DB2, and MySQL.

The first step in working with ADODS is to create an EF Entity Data Model (EDM) that includes the objects you want to expose from the AdventureWorks2008 database to the Web.

Let’s get started. First, create a new web application project or website using Visual Studio 2008. Next, right-click that project and select Add New Item. On the ensuing Add New Item dialog, click on the Data node under the Categories heading on the left. Under Templates, click ADO.NET Entity Data Model and name your model file AdventureWorks2008.edmx. This is illustrated in Figure 1.

Figure 1. Creating a new Entity Data Model in Visual Studio 2008.

On the ensuing Entity Data Model Wizard dialog, be sure the Generate from Database option is selected and click Next. On the Choose Your Data Connection screen, select or create a connection to AdventureWorks2008 and then click Next. The result of this step is a new connection string written to your Web.Config file.


If you examine this connection string, you will notice that it utilizes the new System.Data.EntityClient provider, rather than the traditional System.Data.SqlClient. It also uses a new metadata parameter in its connection string itself, which points to Conceptual Schema Definition Language (CSDL) and Storage Metadata Schema (SSDL) XML content found in the AdventureWorks2008.edmx EDM file.

CSDL provides markup for describing content models such as EDMs. SSDL provides markup for describing the underlying data stores used by EDMs. For more information, consult the MSDN articles “Conceptual Schema (CSDL)” and “Storage Metadata Schema (SSDL).”

On the Choose Your Database Objects screen that follows, expand the Tables node and then select the same four tables used previously in the LINQ to SQL examples (Production.Product, Production.ProductModel, Production.ProductInventory, and Production.ProductReview). Take note of the name of your model (AdventureWorks2008Model) and then click Finish. The EDM Designer surface opens to reveal your new EDM with your four selected entities, as illustrated in Figure 2.

Figure 2. Visualizing the Entity Data Model with Visual Studio 2008’s EDM Designer.

Notice how the new Model Browser tool window appears on the right side of Visual Studio. This new window provides access to all the properties of everything in the EDM, including its types, associations, properties, and the underlying data store. Using the Model Browser, you could, for example, click on the name of a CLR entity and then change it using the Visual Studio Properties window.

The new Mapping Details window also appears in Visual Studio. This provides a means of manipulating the mappings from your SQL Server tables and columns to your EDM entities and properties.

Using the Mapping Details window, you could, for example, using the drop-down lists found in its Value/Property grid column, delete the mapping of a given property by selecting the <Delete> option in that mapping’s drop-down list. Take a few moments to familiarize yourself with these new tool windows and the EDM Designer surface.

Building Your Data Service

Now that you have your EDM, you can move forward and create your ADODS service. To accomplish this, right-click your web project again in Visual Studio and select Add New Item. Next, click on the Web node under the Categories heading on the left. Under templates, click ADO.NET Data Service and name your service file AW08Service.svc. This is illustrated in Figure 3.

Figure 3. Creating a new ADO.NET data service in Visual Studio 2008.

The code editor opens in Visual Studio with your new service’s code. This code will not compile as is because you need to specify a type argument for the generic base class from which the service derives. ADODS is built on Windows Communication Foundation (WCF), and all ADODS services are specializations of the WCF class System.Data.Services.DataService<T>. The T in the type argument expects a derivative of System.Data.Objects.ObjectContext, which the EDM class AdventureWorks2008Entities just happens to be.

Delete the comment in the type argument of the class declaration and then type AdventureWorks2008Entities. Believe it or not, your data service is almost ready to start serving data; there is only one more change to make: in the InitializeService method, add the following line of code:

config.SetEntitySetAccessRule("*", EntitySetRights.All);

This SetEntitySetAccessRule method on the context System.Data.Services.IDataServiceConfiguration controls which entities may be accessed and with which rights. This example enables access to all entities with full access (that is, every entity in the EDM may be selected, updated, or deleted). Here’s a second example:

config.SetEntitySetAccessRule("Product", EntitySetRights.ReadMultiple);

This call enables access only to any set of Product entities as provided by the EDM. (Leave this first example intact and remove the second.) The code in your class should now look like the following:

public class AW08Service : DataService<AdventureWorks2008Entities>
public static void InitializeService(IDataServiceConfiguration config)
config.SetEntitySetAccessRule("*", EntitySetRights.All);

Now it’s time to test your service. In Solution Explorer, right-click the AW08Service.svc file and select Set as Start Page. Right-click your web project and select Set as StartUp Project. Press F5 and view the result in Internet Explorer (illustrated in Figure 4).

Figure 4. Atom XML showing the entities exposed by your ADO.NET data service.

The XML displayed (known as the default service document) is in Atom format. Notice how each of the entities is represented by a collection node. Each collectionhref attribute, which indicates the relative navigational path to take to access that entity collection. Let’s navigate to the ProductReview collection. To do this, type the following URI in Internet Explorer’s address box: node has an


The XML response to the HTTP GET request is quite long; it contains every record in the ProductReview table. The reason is that the request is translated by ADODS to the following T-SQL query:

SELECT * FROM AdventureWorks2008.Production.ProductReview

Most of the time, your applications won’t (or shouldn’t) be doing queries like this. To narrow the query to a ProductReview row having a specific key value, supply that key value in parentheses after the entity name, like this:


The result is illustrated in Figure 5.

Figure 5. Atom XML document showing a single ProductReview entity.

The URI syntax for your requests may be broadly defined as


The power of this addressing syntax is that it enables you to write URIs that traverse an entity model in depth. For example:


The preceding request asks your service for value of the Comments property of ProductReview #5 by way of Product entity #1.

Next, let’s ask for the top two ProductReview entities for Product #937, in descending order, where the ProductReviewID is greater than or equal to 2:

ProductReviewID ge
2&$orderby=ProductReviewID desc

The query string in this example makes use of three different query options (denoted with $). Table 1 lists them all.

Table 1. Query Options for ADO.NET Data Service URI Query Strings
$topLike T-SQL’s TOP operator; limits results
$skipUse with $top when paging; skips n number of rows
$orderbyLike T-SQL’s ORDER BY clause; includes support for ascending (asc) and descending (desc) sorting
$filterLike T-SQL’s WHERE clause; has its own set of operators, listed in Table 45.2
$expandInstead of pointing to related objects via a URI (see Atom’s link element), includes them inline in the response

As mentioned in Table 1, the $filter option has its own set of operators for filtering values. Table 2 contains an abbreviated list of these operators.

Table 2. Operators for the ADO.NET Data Service URI $filter Query Option
eq, gt, lt, ge, le=, >, <, >=, <=
and, or, notLogical AND, logical OR, logical NOT
( )Grouping precedence
add, sub, mul, div, mod, round, floor, ceiling, and so onMath functions
length, indexof, startswith, endswith, substring, trim, concat, insert, remove, replace, toupper, tolower, and so onString functions
day, hour, minute, second, day, month, year, and so onDate functions
IsOf, CastType functions

Now that you know how to work with ADODS URIs, let’s examine the salient parts of the Atom XML response.

First, notice its root entry node. This contains the base address for the service (in its xml:base attribute value), as well as three namespace declarations:

Moving down the response document shown in Figure 45.10, the id node indicates the URI of the entity you’ve just requested, the updated node indicates the date and time of your request, the author node is blank (it’s required by Atom), followed by the link elements.

Each link element includes a title attribute, indicating the name of the current or a related entity that you can address in future requests, and an href attribute, indicating the relative URI of that entity (also known as a navigational property). The first link element also contains a rel attribute with the value edit, indicating that the entity in question may be updated by a subsequent request (using a PUT or MERGE request, covered in the next section). The second and final link element tells you, via its href, which URI to use to traverse the EDM to find related ProductReview entities.

Below the link elements is the content element, which contains the result of your request: the entity ProductReview(1) and all its properties.


To change the response format from Atom to JSON, you must change the value of your request’s HTTP Accept header to application/json. To accomplish this in a test environment, we recommend using the freeware HTTP testing application known as Fiddler, available at Use Fiddler’s Request Builder feature to modify a prior request and then examine the response with its Session Inspector.

Note that requests sent with an Accept header value of either */*, application/xml, text/xml, text/*, or application/atom+xml all result in an Atom-based response.

Listing 1 contains the JSON-formatted version of the response generated by this request. The outer object is simply called d. It has a child object called __metadata that contains properties describing the uri and entity type. The properties that follow are those of the selected entity, including a navigational property object leading to related Product entities (__deferred). JSON responses like this one should integrate seamlessly with your AJAX-enabled Web applications.

Listing 1. JSON-Formatted Response from the ADODS Service
"d": {
"__metadata": {
"uri": "http://localhost:2061/AW08Service.svc/ProductReview(1)",
"type": "AdventureWorks2008Model.ProductReview"
"ProductReviewID": 1,
"ReviewerName": "John Smith",
"ReviewDate": "\/Date(1066608000000)\/",
"EmailAddress": "",
"Rating": 5,
"Comments": "I can\'t believe I\'m singing...",
"ModifiedDate": "\/Date(1066608000000)\/",
"Product": {
"__deferred": {

CRUD Operations

In keeping with the principles of Representational State Transfer (REST), ADODS provides a mechanism for selecting, inserting, updating, and deleting data via HTTP requests to particular URIs. To put this into practice, a client application must be able to formulate its requests using specific HTTP verbs, as shown in Table 3.

Table 3. HTTP Verbs Corresponding to CRUD Operations for ADO.NET Data Services
HTTP VerbOperation
MERGEUpdate the specified properties
PUTUpdate all properties (replace-style update)

To simplify the proper creation of these potentially complex HTTP requests, rather than hand-crafting them, you can use the new .NET client library, System.Data.Services.Client, which comes with ADODS.

Create a new website or web application project using Visual Studio 2008. Add a reference to System.Data.Services.Client by right-clicking your new project and then clicking Add Reference, as you have done previously. Next, right-click the project again and select Add Service Reference. Type the URI to the service in the Address box; then, at the bottom of the dialog, type AW08Service in the Namespace box (as illustrated in Figure 6). (Note: You can also use the new command-line tool dataserviceutil.exe, located in c:\Windows\Microsoft.NET\Framework\3.5, to generate the same service reference classes as necessary.)

Figure 6. Adding a service reference to the data service with Visual Studio 2008.

Let’s write some quick code to exemplify each of the CRUD operations. First, add a new class called ServiceClient.cs to your sample project. Completely empty the file and then add all the code in Listing 2 (substitute ProjectName, located in the fifth using statement, with the name of your web project).

Listing 2. .NET Class Leveraging the ADO.NET Data Services Client Library
using System;
using System.Data.Services.Client;
using System.Diagnostics;
using System.Linq;
using ProjectName.AW08Service;

public class ServiceClient
Uri _svcUri = new Uri("http://localhost:2061/AW08Service.svc/");
DataServiceContext _ctx = null;

public ServiceClient ()
_ctx = new DataServiceContext(_svcUri);

public Product GetProduct(int ProductId)
string Query = string.Format("Product({0})", ProductId);
Uri RelativeUri = new Uri(Query, UriKind.Relative);
Product P = _ctx.Execute<Product>(RelativeUri).FirstOrDefault();

if (P != null)
Debug.WriteLine(string.Format("Success! Found Product {0}", P.Name));

return P;

The class shown in Listing 2 requests data from an ADODS service. Its variable declarations introduce the DataServiceContext class (_ctx), which facilitates communication between client applications and ADODS services. Notice its URI-based initialization in the constructor. Further down, examine the GetProduct method. (As with LINQ to SQL, its object selection code is built on IEnumerable<T> and its extension methods.) To call the service, you construct a relative URI (RelativeUri) containing the path and query options to the entity or entities you want to retrieve. You then pass this relative URI to the Execute<T> method of the context, supplying Product for its type parameter. Finally, you call FirstOrDefault against the query result, which in turn returns either the first selected entity returned or null. Because this is merely a retrieval, an HTTP GET request is issued to retrieve the Product.

Let’s add an update query to the client class, as shown in Listing 3. Under the hood, this query uses the HTTP MERGE verb to update a particular ProductReview entity.

Listing 3. An Update Query Using the ADO.NET Data Services Client Library
public ProductReview UpdateProductReview(int ProductReviewId, string NewComments)
AdventureWorks2008Entities NewContext =
new AdventureWorks2008Entities(_svcUri);

ProductReview P =
p => p.ProductReviewID == ProductReviewId

if (P != null)
string OldComments = P.Comments;
P.Comments = NewComments;

Debug.WriteLine(string.Format("Success! Updated comments to {0}"
, P.Comments));

//put old comments back:
P.Comments = OldComments;

return P;

Notice the use of the NewContext object. It is of type AdventureWorks2008Entities, a DataServiceContext derivative that was generated when you added your service reference. You must use this class, rather than the original _ctx object, to perform any CRUD query other than a retrieval.

Walking through the code in Listing 4, you first select your desired ProductReview entity and then update its Comments property. Then you call UpdateObject and SaveChanges to commit your changes to the database through your service.

Next up, Listing 4 illustrates how to perform an insert of a new ProductReview (related to an existing Product) using ADODS. Under the hood, it uses the HTTP POST verb to accomplish its goal. In this example, you first fetch your Product (to which you associate your new ProductReview), then set the properties of your ProductReview, add that new object (PR) to the current list (via NewContext.AddToProductReview), set the Product property on PR, and then call SetLink to tell your context that you have made this association. Finally, you call SaveChanges to commit your work.

Listing 4. An Insert Query Using the ADO.NET Data Services Client Library
public ProductReview AddProductReview(int ToProductId, string Comments)
AdventureWorks2008Entities NewContext =
new AdventureWorks2008Entities(_svcUri);

Product P =
from p in NewContext.Product
where p.ProductID == ToProductId
select p

ProductReview PR = null;

if (P != null)
PR = new ProductReview()
Comments = Comments,
EmailAddress = "",
Rating = 4,
ReviewDate = DateTime.Now,
ModifiedDate = DateTime.Now,
ReviewerName = "Alex T. Silverstein"

PR.Product = P;
NewContext.SetLink(PR, "Product", P);

Debug.WriteLine(string.Format("Success! Added new review {0}",

return PR;

To round things out, in this final example, Listing 5 illustrates how to perform a delete using ADODS. Its syntax is straightforward and simple. All that is needed is to fetch the object to be deleted and then call DeleteObject on your context and then SaveChanges. Note that in this final example, the URI for your deletion is http://server:port/AW08Service.svc/ProductReview(11), no payload is sent to the server, and your HTTP verb is DELETE.

Listing 5. A Delete Query Using the ADO.NET Data Services Client Library
public void DeleteProductReview(int ProductReviewId)
AdventureWorks2008Entities NewContext =
new AdventureWorks2008Entities(_svcUri);

ProductReview PR =
from p in NewContext.ProductReview
where p.ProductReviewID == ProductReviewId
select p

if (PR != null)

  •  SQL Server 2008 : Developing with LINQ to SQL (part 2) - Uncovering LINQ to SQL with Linqpad
  •  SQL Server 2008 : Developing with LINQ to SQL (part 1)
  •  Getting Comfortable with ADO.NET 3.5 and SQL Server 2008
  •  SQL Server System and Database Administration : System Views
  •  SQL Server System and Database Administration : System Tables & System Stored Procedures
  •  SQL Server System and Database Administration : System Databases
  •  SQL Server 2008 : Monitoring Your Server - Monitoring Your CPU
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 3) - XML DML & Converting a Column to XML
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 2) - SQL Server 2005 XQuery in Action
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 1) - XQuery Defined
  •  SQL Server 2008 : Monitoring Your Server - Familiarizing Yourself with the Performance Monitor
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 3) - OPENXML Enhancements in SQL Server 2005
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 2) - FOR XML EXPLICIT
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 1) - FOR XML RAW & FOR XML AUTO
  •  SQL Server 2008 : Audit-Related Startup and Shutdown Problems
  •  SQL Server 2008 : Creating SQL Server Audits Using the GUI
  •  SQL Server 2008 : Creating Database Audit Specifications
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 3) - XML Indexes
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 2) - XML Schemas
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 1) - Working with the XML Data Type as a Variable & Working with XML in Tables
    Top 10
    Creating and Managing Views in SQL Server 2008 : Partitioned Views
    Creating and Managing Views in SQL Server 2008 : Managing Views, Data Modifications and Views
    Introducing Windows Presentation Foundation and XAML : Investigating the WPF Assemblies
    Introducing Windows Presentation Foundation and XAML : The Motivation Behind WPF & The Various Flavors of WPF
    SharePoint 2010 : Business Intelligence - Excel Services (part 2) - Accessing Excel Services Over SOAP
    SharePoint 2010 : Business Intelligence - Excel Services (part 1) - Accessing Excel Services Over REST
    SharePoint 2010 : Business Intelligence - Visio Services
    Windows Server : Branch Office Deployment - Branch Office Services (part 2)
    Windows Server : Branch Office Deployment - Branch Office Services (part 1)
    Windows Server : Planning Application Virtualization
    Most View
    Automating Blind SQL Injection Exploitation
    Management Tools in SQL Server 2008
    Microsoft ASP.NET 4 : Configuring ASP.NET from IIS
    Exploring the T-SQL Enhancements in SQL Server 2005 : The PIVOT and UNPIVOT Operators
    What to Back Up on Exchange Servers 2010
    ASP.NET AJAX : Partial Refreshes (part 2) - Handling Errors
    SQL Server 2008 : Configuration Options
    Remote Administration of Exchange Server 2010 Servers : Using the Remote Exchange Management Shell
    SQL Server 2008 : Demystifying Data Types - Computed Columns
    Using Text in XAML
    ASP.NET AJAX : Timed Refreshes
    Hacking - Advanced Camouflage
    Windows Server 2008 : Install the Hyper-V Role
    Security Policy Explained in .NET
    Exchange Server 2007 : Work with Remote Domains
    Windows Server 2008 : Utilize System Center VMM
    SharePoint 2010 : Understanding Windows PowerShell Concepts (part 2)
    iPhone 3D Programming : Textures and Image Capture - Generating and Transforming OpenGL Textures with Quartz
    Windows 7 : Indexing Your Computer for Faster Searches (part 3) - Optimizing File Properties for Indexing
    Windows Server 2008 : Domain Name System and IPv6 - Understanding DNS Queries