Using ADO.NET Data Services

Introduction

ADO.NET Data Services allows you to expose data as entities over a service. This provides an elegant way of using a ‘Linq to SQL’ model or an ‘ADO.NET Entity’ model over a WCF service layer. The applications accessing the WCF Service layer to work with the data can be any application that can send HTTP requests.. This article introduces ADO.NET Data Services and explains how to create service-oriented data access layers that can be accessed over HTTP. This article is based on ADO.NET Data Services that ships with Visual Studio.NET 2008 Service Pack 1.

ADO.NET Data Services provides access to the data model and allows you to perform CRUD operations over REST based services. REST stands for REpresentational State Transfer (REST). This allows any entity from the data model to be accessed by a URI. The type of CRUD operation to perform on an entity will depend on the HTTP verb used to access the entity. For example the HTTP GET operation is used to retrieve an entity, POST to create an entity, PUT to update an entity and DELETE to delete an entity.

ADO.NET Data Services enables you to work with the data model through HTTP and by changing the request content-type allows you to serve the data in multiple formats. At the time of writing ADO.NET Data Services supports ATOM (ATOM is a XML based syndication format for web feeds) and JSON (JavaScript Object Notation) formats.

ADO.NET Data Services opens up possibilities to work with data in new ways and provides many advantages. It works very well as a service application for Silverlight applications and AJAX web applications, and allows you to send requests to retrieve data in the required format without refreshing the web page. It also works with Adobe AIR applications or java client applications or just about any type of applications that can make HTTP requests to the service layer. By using both ADO.NET Entity Framework and ADO.NET Data Services, we can create service oriented data access layers providing data from the database to these different applications. Because of the REST model, the dependencies on the actual technology implementing the service or database specific implementations are shielded from the application accessing the service.

Creating ADO.NET Data Services

Let us create a simple ADO.NET Entity Data Model named Contoso inside an ASP.NET web application. The Contoso ADO.NET Entity Data Model has the following entities generated from the database. I will not be going into ADO.NET Entity Framework and for more details you can check the cribsheet on ADO.NET Entity Framework.

To expose the above data model using ADO.NET Data Services, let us add an ADO.NET Data Service to our project and name it as ContosoDal.svc. We need to alter the code behind for the ContosoDal.svc to work with the Contoso ADO.NET Entity Data Model we created and then specify the entity sets in our model that are accessed through the ADO.NET Data Services. For example to allow access to both our entities in the data model we can alter the code behind as follows

C# Code:

The ContosoModel.ContosoEntities refers to the ObjectContext type for the ADO.NET Entity Data Model that we created. The “*” specifies that we want to include all the entities in our data model, and the EntitySetRights enumeration controls the type of access we are allowing for our entity sets. We can also set the EntitySetRights for each entity set individually and restrict the type of access we want to allow to the entity set. Now if we navigate to the ContosoDal.svc by setting it as the start up page, we should see the list of entity sets from the ADO.NET Data Service retrieved as an ATOM feed

Querying Data

You can query data by executing HTTP GET operations over simple URIs. For example, to list out all the customer entity instances from the Customers entity set we can append the entity set name to the base URI for the ContosoDal.svc

http://localhost:8885/ContosoDal.svc/Customers

Accessing the above URI will list out all the Customer entity instances in the Customers entity set. To just display details about a single Customer entity, I can pass the primary key value in the URI as follows:

http://localhost:8885/ContosoDal.svc/Customers(1)

The above URI based query will list out the details of the Customer entity instance with the primary key column ID set to 1. If I want to display just a property value of an entity, I can append the name of the property to the URI

 

http://localhost:8885/ContosoDal.svc/Customers(1)/Country

The above URI displays just the Country property value for the Customer entity instance with the primary key set to 1. Similarly we can traverse through associations as well. For example the following URI displays the Order entity instances for the Customer entity that has the primary key set to 1

http://localhost:8885/ContosoDal.svc/Customers(1)/Orders

We can also sort, filter and page the results when querying entity sets. For example the following URI return all the Customer entity instances where the Country property is set to UK

http://localhost:8885/ContosoDal.svc/Customers?$filter=Country eq ‘UK’

We can add a $skip, $top and $orderby query parameters to implement paging and sorting

http://localhost:8885/ContosoDal.svc/Customers?$filter=Country eq ‘UK’$orderby=City desc$skip=10$top=10

The above query filters for Customer entity instances from the UK and sorts the result by the descending order of the City property and skips the first 10 records and retrieves the next 10 results

Query Interceptors

Query interceptors are useful when you want to run your own custom methods when queries are executed against your entity sets. For example let us assume you want to restrict the results in the query based on the current logged in users’ credentials, we can write a query interceptor to intercept the query for an entity set and restrict the returned results. Since the query interceptor is part of the ADO.NET Data Service, the client will not be aware that an interceptor was used on the results.

To understand query interceptors, let us do a very simple implementation. Let us create a query interceptor that returns only Customer entity instances from UK when someone queries our Customers entity set.

C# Code

We can define the above method inside our data service class ContosoDal. A query interceptor will not accept any arguments and will return an expression that accepts a Customer object and returns a Boolean value indicating on what condition the instance should be included in the result. We also adorn the method with the QueryInterceptor attribute indicating which entity set our query interceptor works on. Now when we query the Customers entity set, we will get only Customer entity instances where the Country property value is set to UK.

Manipulating Data

We can manipulate data by making use of the HTTP verbs POST, PUT and DELETE for inserts, updates and deletes respectively. We can do these operations by making web requests using the WebRequest class to do the respective operations. But a simpler way is to create clients as detailed in the Consuming ADO.NET Data Services section in this article. This shields the HTTP verbs and provides a simpler model in manipulating and retrieving data.

Change Interceptors

Similar to query interceptors, change interceptors execute code during inserts, updates and deletes. We can define a change interceptor to perform any validations etc during inserts, updates and deletes. To create a change interceptor, we can define a method that accepts an entity instance and the UpdateOperations enumeration.  The UpdateOperations enumeration can be used within the method body to restrict the change interceptor to execute only for one or more specific operations such as insert, update or delete.  For example the following change interceptor assigns the Country property as UK during inserts, if there is no value set for the Country property.

C# Code:

Similar to the query interceptor, we need to adorn the method with the ChangeInterceptor attribute to specify the entity set against which the change interceptor would be executed.

Extending ADO.NET Data Services

You can extend ADO.NET Data Services by providing your own methods called as service operations to your data services. Service operations enables you to write your own custom logic that can access stored procedures mapped in the ADO.NET Entity Data Model etc.

Service Operations

C# Code:

The above will create a method that can be accessed by sending a GET request. One additional thing we must do is enable access to the service operation by marking it in the InitializeService method of our ADO.NET Data Service class

C# Code:

Consuming ADO.NET Data Services

We can consume ADO.NET Data Services by creating proxy classes based on the URI. Visual Studio.NET 2008 SP1 ships a command line tool named DataSvcUtil. This command line tool can be used to generate the proxy classes to consume the ADO.NET Data Services. For example the following command generates a class file named proxy.cs based on the ADO.NET Data Services available at the URI http://localhost:8885/ContosoDal.svc.

We can include the generated proxy.cs in our client application and use the code in the proxy to make HTTP calls to the ADO.NET Data Service. For example the following code retrieves the list of Customer entity instances:

C# Code:

The ContosoEntities class is the data context and can be used to communicate with our data service. The queries we write in will change the URI to send to the data service.  For example the above code will send a GET request to the URI http://localhost:8885/ContosoDal.svc/Customers.

The simplest way to see the queries being executed is to create a handler for the SendingRequest event of the ContosoEntities data context. So let us go ahead and create a handler to display the URI in the console. Let us also modify the code to run a Linq query on the Customers entity set.

C# Code:

When you execute the above code it will display the URI…
http://localhost:8885/ContosoDal.svc/Customers()?$filter=Country eq ‘UK’
… in the console. So any linq query you write prepares the appropriate URI to send the GET request to.

We can also do inserts, updates and deletes by using the data context object. For example the following code shows inserting a new Customer entity

C# Code:

We can also execute our service operations by using our data context object.

C# Code:

The above code calls the service operation named GetCustomerFromCountry by passing the value “UK” as a parameter.

Another type of client that is very suitable for ADO.NET Data Services is AJAX based clients. The ability to send a request to the ADO.NET Data Service requesting for data in JSON format, makes it a good technology to do server side operations in AJAX scenarios. There is a JavaScript library in codeplex available at http://www.codeplex.com/aspnet/Release/ProjectReleases.aspx?ReleaseId=13357  that can be used to make calls to ADO.NET Data Services. Once you add a reference to the downloaded JavaScript library, you can start writing JavaScript code for AJAX scenarios. For example the following JavaScript code queries for Customer entity instances that have the Country property set to UK.

JavaScript Code:

The above JavaScript function doQuery executes the GET request on the URI passed to the query method call of the DataService object.  Then it will raise the callback function onSuccess on successful execution. Within the onSuccess function the res variable will have the query results, in this instance an array of Customer objects. Similarly we can do inserts, updates and delete also using the DataService object.

Exposing in-memory data

Data doesn’t necessarily mean database. ADO.NET Data Services is very extensible and can work with in-memory data (such as a list of objects) as well as data from databases.

For example I can create the following class:

C# Code:

Once I add an ADO.NET Data Service to my project I can set the CustomerContainer as the DataService type and set the Customers property as the entity set to be exposed.

C# Code

Conclusion

ADO.NET Data Services is a solution that enables you to expose a data model over a REST based service. This opens up a whole lot of possibilities to access data since the client application can access data by just sending a GET request to an URI and specify the response data format. By using the other HTTP methods such as PUT, POST and DELETE, we can modify the data as well. Since this represents the way the web works with resources, it fits naturally to web applications accessing data as resources.