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.
Essentials
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 http://en.wikipedia.org/wiki/Atom_(standard).)
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.
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.
Note
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.
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.
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).
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
http://server:port/AW08Service.svc/ProductReview
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:
http://server:port/AW08Service.svc/ProductReview(1)
The result is illustrated in Figure 5.
The URI syntax for your requests may be broadly defined as
http[s]://server:port/ServiceName.svc/[EntitySetName[(KeyValue)]
[/EntitySetName[(KeyValue)]]...[?QueryString]
The power of this addressing syntax is that it enables you to write URIs that traverse an entity model in depth. For example:
http://server:port/AW08Service.svc/Product(1)/ProductReview(5)/Comments
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:
http://server:port/AW08Service.svc/Product(937)/ProductReview?$top=2&$filter=
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
| Option | Purpose |
|---|
| $top | Like T-SQL’s TOP operator; limits results |
| $skip | Use with $top when paging; skips n number of rows |
| $orderby | Like T-SQL’s ORDER BY clause; includes support for ascending (asc) and descending (desc) sorting |
| $filter | Like T-SQL’s WHERE clause; has its own set of operators, listed in Table 45.2 |
| $expand | Instead 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
| Operator(s) | Usage |
|---|
| eq, gt, lt, ge, le | =, >, <, >=, <= |
| and, or, not | Logical AND, logical OR, logical NOT |
| ( ) | Grouping precedence |
| add, sub, mul, div, mod, round, floor, ceiling, and so on | Math functions |
| length, indexof, startswith, endswith, substring, trim, concat, insert, remove, replace, toupper, tolower, and so on | String functions |
| day, hour, minute, second, day, month, year, and so on | Date functions |
| IsOf, Cast | Type 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.
Note
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 http://www.fiddler2.com. 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": "john@fourthcoffee.com",
"Rating": 5,
"Comments": "I can\'t believe I\'m singing...",
"ModifiedDate": "\/Date(1066608000000)\/",
"Product": {
"__deferred": {
"uri":
"http://localhost:2061/AW08Service.svc/ProductReview(1)/Product"
}
}
}
}
|
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 Verb | Operation |
|---|
| GET | Select |
| MERGE | Update the specified properties |
| PUT | Update all properties (replace-style update) |
| POST | Insert |
| DELETE | Delete |
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.)
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 =
NewContext.ProductReview.Where
(
p => p.ProductReviewID == ProductReviewId
).FirstOrDefault();
if (P != null)
{
string OldComments = P.Comments;
P.Comments = NewComments;
NewContext.UpdateObject(P);
NewContext.SaveChanges();
Debug.WriteLine(string.Format("Success! Updated comments to {0}"
, P.Comments));
//put old comments back:
P.Comments = OldComments;
NewContext.UpdateObject(P);
NewContext.SaveChanges();
}
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
).FirstOrDefault();
ProductReview PR = null;
if (P != null)
{
PR = new ProductReview()
{
Comments = Comments,
EmailAddress = "alex@unifieddigital.com",
Rating = 4,
ReviewDate = DateTime.Now,
ModifiedDate = DateTime.Now,
ReviewerName = "Alex T. Silverstein"
};
NewContext.AddToProductReview(PR);
PR.Product = P;
NewContext.SetLink(PR, "Product", P);
NewContext.SaveChanges();
Debug.WriteLine(string.Format("Success! Added new review {0}",
PR.ProductReviewID));
}
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
).FirstOrDefault();
if (PR != null)
{
NewContext.DeleteObject(PR);
NewContext.SaveChanges();
}
}
|
