Monday, September 1, 2008

ADO.NET Data Services - Part 1


ADO.NET Data Services is a new feature that Microsoft has introduced as a part of the SP1 upgrade to .NET 3.5 and VS 2008. It was previously previewed as ASP.NET Extensions. But with the SP1 release a lot of things have changed around ADO.NET Data Services. Keep reading to find out more.


ADO.NET Data Services is exactly what it says. It exposes ADO.NET data as a set of Services. As an example if I have a customers table (as we do in Northwind database),  the table will be accessible by the URI http://myserver/data.svc/Customers. I can understand you already having thoughts about Security and Access Priviliges. Hold on, we will get to that in a brief while. But first things first, let us dissect ADO.NET Data Services further and see the “what” and “how”. We will then proceed to expose a sample database through ADO.NET Data Services and build a client to access the same.


ADO.NET Data Services has undergone quite a lot of changes in syntaxes and assemblies for the SP1 release from its preview. I will highlight those throughout this article.


The main purpose of ADO.NET Data Services is to expose data in a couple of new formats. Ie. JSON and ATOM. Most of you will be familiar with both of these formats. You can get more details about these formats at and The important thing to note here is that both ATOM and JSON are language independent formats thus enabling you to expose your data to a variety of clients. Representations of how data will be returned are given below.




<?xml version="1.0" encoding="utf-8" standalone="yes" ?>

<service xml:base="http://server/service.svc/" xmlns:atom="" xmlns:app="" xmlns="">



  <collection href="Categories">



  <collection href="CustomerDemographics">



  <collection href="Customers">



  <collection href="Employees">



   <collection href="Order_Details">



  <collection href="Orders">



  <collection href="Products">



  <collection href="Region">



  <collection href="Shippers">



  <collection href="Suppliers">



  <collection href="Territories">









  "d" : {
     __metadata: {

        uri: "http://server/service.svc/Customers(\'ALFKI\')",

        type: "NorthwindModel.Customers"

     CustomerID: "ALFKI",

     CompanyName: "Alfreds Futterkiste",

     ContactName: "Maria Anders",

     ContactTitle: "Sales Representative",

     Address: "Obere Str. 57",

     City: "Berlin",

     Region: null,

     PostalCode: "12209",

     Country: "Germany",

     Phone: "030-0074321",

     Fax: "030-0076545",

     Orders: {
        __deferred: {
           uri: "http://server/service.svc/Customers(\'ALFKI\')/Orders"

     CustomerDemographics: {
        __deferred: {
           uri: “http://server/service.svc/Customers(\'ALFKI\')/CustomerDemographics"




The importance of exposing data in the JSON is more for AJAX enabled application. So if you built your site on Silverlight, for example, you can now get data in JSON format from your business services. This makes coding in Silverlight a lot more easier right? Further in this article, we will also do a quick comparison of using traditional methods and JSON formats.


ADO.NET Data Services use a style of software called Representation State Transfer (REST). In very simple terms, it means transferring data using HTTP without any messaging layer or formats. In ADO.NET Data Services, we will use HTTP GET, HTTP PUT HTTP POST and HTTP Delete for CRUD Operations. Yes. You read it right. You can now make updates and deletes over the web directly to your database. ADO. NET Data Services uses a data model called the Entity Data Model to represent data.


Addressing Scheme – GET OPERATIONS


We all know now that we can expose data as services with ADO.NET Data Services. But that still leaves a few questions unanswered. How do I maintain relationships? How do I sort and filter records?


Thankfully, the Data Services team has already thought about these questions. All of the above operations can be done using query strings and URIs.


Let us go back to the Customers example in the Northwind database. We saw that once they are exposed http://myserver/data.svc/Customers will give you access to the Customers table. Now to get hold of a particular record, all you have to do is to specify the entity key within parameters. So, http://myserver/data.svc/Customers(“ALFKI”) will now refer to a single entity. You can access the properties of the entity by using the property name after a “/”. That means, http://myserver/data.svc/Customers(“ALFKI”)/ContactName will give us access to the contact name of the customer. So far so good? Now let us move on. Another important aspect of any relational data is its association with other entities. In this case, a customer can have a list of orders against him. To access these associations, all you have to do is replace the property in the previous example by the name of the entity. http://myserver/data.svc/Customers(“ALFKI”)/Orders, in this case retrieves all the orders that the customer ALFKI is associated with. That is so cool!! But we are not done yet. We still have to see how to do filtering, sorting and paging, so lets get right into it.



Filtering, Sorting and Paging


Let us keep building on the example we already have. If  your requirement was to retrieve only the active orders for the Customer ALFKI you will change your URI to http://myserver/data.svc/Customers(’ALFKI’)/Orders?$filter=Active eq true. The URI uses filter=Active eq true to give you only active records on the Customer. There are other comparison operators you can use as well. 


Just like filtering you can also include query string options to order the resultset. For eg. http://myserver/data.svc/Customers(’ALFKI’)/Orders?$filter=Active eq true&$orderby=OrderDate orders the data by OrderDate.


Paging using Data Services is just a bit more complex than the other operations. Let me introduce to two new keywords “skip” and “top”. These keywords help you achieve paging of records with ADO.NET Data Services. Both skip and top take an integer value. “Skip” skips the number of records mentioned. For example skip = 30 will move you to the 31st record and “top” will restrict the number of records returned by the service. So top = 5 will mean that you get only 5 records. Use these two in conjunction and now you have paging implemented. So if I you need customers 20 to 30, you will add the skip and top options to the query string. The resultant URI now is http://myserver/data.svc/Customers?$skip=20&$top=10. You can use skip and top with Order By. If no order by is used, the resultset is ordered by the primary key in the table.


In general, the addressing scheme for ADO.NET Data Services can be depicted as http://host/vdir/<service>/<EntitySet>[(<Key>)[/<NavigationProperty>[(<Key>)/...]]]

 Where [] implies optional components.


Now that’s a lot for one reading. While you familiarize yourself with these concepts I will be back with the next step in this series that ponders on why anybody would use ADO.NET DATA SERVICES and the benefits of the same. Come back in about a week’s time for more.