Providing data through ADO.NET Data Service

Most of the modern application work with some data. Weather it is thick client or thin client application. Almost always we have to work with data and all framework has some support in their class library to work with data. Microsoft .NET Framework has some classes and interfaces to work with data. This set of classes and interfaces are called ADO.NET.

Connected and Disconnected data access

In two different style we can work with data in .NET. One option is connected data access in which all the work is done in live connection and the another option is disconnected data access in which we store our data in a in memory data structure and work with that even when data source connection is closed. There are many situation when we need to work in disconnected mode. For example think you are working on a project which is multilayered ed application and after retrieving the data in your data access layer, you have to send the data to your business logic class. In that case you have to store the data in a disconnected data structure like DataSet and send that to business logic layer.

Use of Web Service

Web Service is an entity which has no interface and it provides a particular functionality throughout the internet. Web Service consumer application can be any kind. Desktop, web or mobile. Web Service has another property, that is service provider and service consumer may be in different platform. For example a Web Service consumer developed in php and running on Linux can consume a Web Service which is developed in ASP.NET and running on Windows operating system.

Use of ADO.NET Data Service

Often we need to provide data to a remote consumer. For that we can use web service. But ADO.NET Data Service will be a better option. For example suppose you are providing some data to your consumer through Web Service. Web Service have some web methods. So you are creating some web method for Create, Update, Delete and Retrieve operation. But think you have to create web methods for every combination of requirements of your consumer. ADO.NET Data Service gives a flexible framework to overcome this problem. Here your consumer can consume data through a URI based query. ADO.NET Data Services use the concepts of Representation State Transfer (REST) to create a URI-based application programming interface (API) to the data. In contrast, Web Services use an Extensible Markup Language (XML) envelope-based API to call the service. The URI syntax is made up of two parts, a path to a resource and a query string. For example, if you assume that you have created an end point called “Customers”, you could request the entire list of customers by making a request to the server using the URI After that you can place some query. For example$filter=Name%20eq%20Bob. Here your consumer can get the customers who have the name ‘bob’. eq stands for equal.

Query option supported in ADO.NET Data Service:

$filter Limits the results based on a predicate
$orderby Sorts the results (ascending or descending)
$skip Seeks into the results before returning results
$top Limits the results to a set number of results
$expand Embedded related entities instead of providing links

Logical operators supported in ADO.NET Data Service:

and True if both sides are true
or True if either sides are true
not True if the operand is false
eq True if both sides have the same value
ne True if sides have different values
lt True if the left side is less than the right side
gt True if the left side is greater than the right side
le True if the left side is less than or equal to the right side
ge True if the left side is greater than or equal to the right side

Also you can combine two query options.

Arithmetic operators supported in ADO.NET Data Service:

add Performs addition
sub Performs subtraction
mul Performs multiplication
div Performs division
Mod Returns the remainder of a division

Expression syntax supported in ADO.NET Data Service:

substringof Tests whether a string is completely contained in another string
endswith Tests whether a string ends in a specific set of characters
startswith Tests whether a string starts with a specific set of characters
length Returns the length of a string
indexof Returns the ordinal of a specific string in another string
insert Performs an insertion in a string
remove Removes characters from a string
replace Replaces characters in a string
substring Returns part of an existing string
tolower Returns a lower-case equivalent of a string
toupper Returns an upper-case equivalent of a string
trim Removes leading and trailing spaces in a string
Concat Concatenates several strings
day Returns the day part of a date value
month Returns the month part of a date value
Year Returns the year part of a date value
hour Returns the hour part of a time value
minute Returns the minute part of a time value
second Returns the second part of a time value
round Performs a rounding of a numerical value
floor Calculates the floor of a numerical value
ceiling Calculates the ceiling of a numerical value
isof Determines if an entity type is a specified type
Cast Treats an entity as a specific type if the entity supports the type

ADO.NET Data Service is not an another data access layer of your application. It is a solution to provide data to your consumer through internet. It uses a LINQ provider to do the database work.

Your application


ADO.NET Data Service

LINQ Provider

Data Store

Create a ADO.NET Data Service

To create a ADO.NET Data Service you need database and a LINQ provider in your web site. For example Entity Framework, LINQ To SQL or any third-party solution. Then you can add a new ADO.NET Data Service from Add New Item dialog window. Visual Studio will create a .svc file and a code behind file. The .svc file is just a host for the code behind file. The code behind file is created under App_Code folder.

ADO.NET Data Service Solution Explorer

In the code behind file Visual Studio creates a boiler plate code. For example I am creating a service for my Northwind database.

using System.Data.Services;
using System.Linq;
using System.ServiceModel.Web;
using NORTHWNDModel; // Namespace for data source class.

public class Northwind : DataService // Specify data source class.
    public static void InitializeService(IDataServiceConfiguration config)
        // Entities.
        config.SetEntitySetAccessRule("Customers", EntitySetRights.AllRead);
        config.SetEntitySetAccessRule("Employees", EntitySetRights.AllRead);

        // Service operations.
        config.SetServiceOperationAccessRule("GetCustomer", ServiceOperationRights.All);

    // This method is a service operation method. It is just like a store
    // procedure of a database. Service consumer can call this service
    // operation from url. They also quary the result returned from this method.
    public IQueryable GetCustomer(string city)
        IQueryable quary = from customer in this.CurrentDataSource.Customers
                                      where customer.City == city
                                      select customer;

        return quary;

You can see here that it creates a class which is inherited from DataService class. This is a generic class and you have to specify your data source class. After that there is a static method to write your server wide data service logic. Here you can see two things, one is
SetEntitySetAccessRule is for entities and the second is SetServiceOperationAccessRule. By default your entities are not showing on your result set. So you have to give access to your consumer the particular entity. To give access all the entities you can use a * sign. SetServiceOperationAccessRule is to give access service operations. SetEntitySetAccessRule and SetServiceOperationAccessRule are two method of IDataServiceConfiguration interface, which is the input parameter of the static InitializeService method. There is a second parameter of SetEntitySetAccessRule and SetServiceOperationAccessRule methods, which is of EntitySetRights and ServiceOperationRights type respectively. This is for how you want to give access of the entity or service operation.

EntitySetRights Enumeration
All Allows full read and write access.
AllRead Allows full read access.
AllWrite Allows full write access.
ReadSingle Can read single data items only.
ReadMultiple Can read sets of data.
WriteAppend Can add new items.
WriteDelete Can delete items.
WriteMerge Can merge items.
WriteReplace Can replace entire items.
None No access.

Now set the .svc file as the start page and run the website. You can see the result in your browser. Now you can query your end points and test your service. Now you are providing some data to a remote consumer and your service consumer has the flexibility to query data like a database from your service. That’s the power of ADO.NET Data Service.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s