Introduction to ADO.NET Data Services

Objective:

This article explains the theoretical concepts of the ADO.NET Data Services.

Introduction:

• ADO.NET Data Services framework provides an API that allows data to be created and consumed over HTTP using RESTful service. 
• ADO.NET Data Services supports all database operations using URI. 
• ADO.NET Data Services can expose an Entity Model via an URI. 
• ADO.NET Data Services is RESTful service to support CRUD operations on database. 
• ADO.NET Data Services could be consumed by any type of client, like Windows, Silverlight, Web, AJAX and console.

So the ADO.NET Data Services could be defined as exposing all database operations as RESTful services on an Entity Model, if the Entity Model supports both the IUpdateable and IQueryable interface.

How HTTP Verbs are used

ADO.NET Data Services use HTTP to interact with data items using the HTTP Methods.

The following are the three options to make a HTTP call:

1. Using Fiddler2
2. Using a web browser's address box
3. A client library. A client library is exposed by the System.Data.Service.Client namespace. The client library encapsulates the HTTP Request such that developers have no need to manually craft a HTTP Request.

Supported message format

The JSON message format's main purrose is to work with AJAX clients.
XML is a highly readable message format. 
ATOM is the default message format. It is highly descriptive in nature.

Explanation of database to be used for CRUD operations

An IPL database has been created. This contains three tables. The details of the tables with records are illustrated below.

Player_Information

Team_Information

Player_Team

Note: To accommodate a URI query explained below, it is assumed that one ADO.NET DataService is added to the web project. To add an ADO.NET Data Services, right-click on the project and select "Add new Item".  ADO.NET Data Services work on Entity Models. So to create an Entity Model add a new item and select "Entity Model".  The name of this Entity Model class will be used as the base class for the ADO.NET Service. 

For a step-by-step explanation of how to create an Entity Model and add ADO.NET services to a project, see my ADO.NET Services Part 2 article on c-sharpcorner.com.

Querying using URI on database

On running the service, the following URI will be invoked by the browser.

http://localhost:2888/DataService.svc/

1. localhost:2888 is the server and port number where the service is running 
2. DataService is the name of the ADO.NET Data Services.

The result will be in the browser as below.

The service above is using the ATOM message format.

Metadata

• One feature of ADO.NET Data Services is that, they provide operation to retrieve Metadata about the service and offer of the service.  
• This is very useful in determining structure before requesting them. 
• ADO.NET Data Services provides metadata accessible. 
• Metadata expose all the resource and custom services. 
• Metadata could be accessed by appending $metadata to the service URI.

So, the URI to access metadata is:

http://localhost:2888/DataService.svc/$metadata

And metadata will be in the browser, since the following is the content in the browser. 

URI Query options

Any complex query could we written using a URI.

1. The following URI will display a record from the Player_Information table.

http://localhost:2888/DataService.svc/Player_Information

2. The following URI will display a record from the Team_Information table.

http://localhost:2888/DataService.svc/Team_Information

 
URI options

Options	Descriptions
expand	Request set of related entities to be retrieved.
orderby	Indicates the sequence entity to be retrieved
skip	Skip number of data items.
top	At most returns top number of data items
filter	Applies the filtering condition

URI logical operators

Operators	Descriptions
eq	Equal
ne	Not Equal
gt	Greater Than
ge	Greater than or equal to
lt	Less than
le	Less than or equal to
and	Logical AND
or	Logical OR
not	Logical NOT

URI math operators

Operators	Descriptions
add	add
sub	Subtract
mul	multiply
div	Division
mod	remainder

Examples:

• The following query will fetch a record of Team_Information with Team_Id  equal to T2:
  
  http://localhost:2888/DataService.svc/Team_Information?$filter= TeamId eq 'T2'
   
• The following query will fetch all records of Team_Information with Team_Id not equal to T2:
  
  http://localhost:2888/DataService.svc/Team_Information?$filter= TeamId ne 'T2'
  
  
   
• The following query will fetch the top 2 records of Team_Information:
  
  http://localhost:2888/DataService.svc/Team_Information?$top=2|
  
  
• The following query will fetch playerID P1 or P2 from the Player_Information table.
  
  http://localhost:2888/DataService.svc/Player_Information?$filter=(PlayerId eq 'P1') or (PlayerId eq 'P2')
  
  
  

Very Important Note:

1. ? $ URI Option = is used in all URI queries to fetch records. 
2. If the Browser is unable to show the results of a URI query then ensure the FEED Reading of the browser is OFF. To do so, in the IE Browser click in "Tools" -> "Internet options" -> "Content" select the "Feeds and Web slices" setting and uncheck the "Turn on reading view" checkbox. 

Access Rules

ADO.NET Data Services class must inherit from the DataService<T> class.

Where T is the name of the Entity Model class.

Here IPLEntities is an Entity Model class. The DataService is the name of the ADO.NET Data Services.

By default, ADO.NET Data Services disables all access to the Entity Model.

So it needs to be enabled, such that the client can access the Entity Model. The developer has explicitly given an action to model to access.

The SetEntitySetAccessRule(,) method is used to provide access. This method accepts the following two parameters.

1. Entity name on which rule to be applied
2. Enumerator of EntitySetRight

Enumerator	Description
All	All Read and Write are permitted
AllRead	All Read  permitted
AllWrite	All Write is permitted
None	No access is permitted
ReadMultiple	Reading multiple row is permitted
ReadSingle	Reading a single row is permitted
WriteAppend	Creating New data is permitted
WriteDelete	Deleting data is permitted
WriteMerge	Merge updating is permitted
WriteReplace	Replace updating is permitted

 

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

This will permit all right access on all entity sets.

config.SetEntitySetAccessRule("Player_Information", EntitySetRights.AllWrite);

This will permit all write access on the Player_Information entity set.

Creating the proxy class

There are two ways to create a proxy class for ADO.NET Data Services at the client side.

1. By adding a service reference of the ADO.NET Data Services URI. 
2. By using the ADO.Net client library. To use the client library there is a need to add a System.Data.Services.Client.dll assembly to the project. The two main constructs in the client library are the DataServiceContext class and the DataServiceQuery class.

Conclusion

The above article has explained the theoretical concepts of ADO.NET Data Services. For a step-by-step explanation of how to create ADO.NET Data Services and consume it in various type of clients, see my other articles on c-sharpcorner.com.

Happy Coding