Introduction of ADO.NET Data Service

This article will give introduction of ADO.NET Data service.

Objective:

This article will explain theoretical concept of ADO.NET Data Service.

Introduction:

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

So, ADO.NET Data service could be defined as,

It exposed all database operations as RESTful service on an entity model, if entity model supports both IUpdateable and IQueryable interface.

How HTTP Verbs being used ?

ADO.NET Data service uses HTTP to interact with data item using the HTTP Methods.

There are three options to make HTTP call.

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

Supported Message format

JSON message format is mainly to work with AJAX clients.
XML is highly readable message format. 
ATOM is default message format. It is highly descriptive in nature.

Explanation of database, going to be used for CRUD operation

IPL database has been created. This contains three tables. Details of tables with records are illustrated below

Player_Information

Team_Information

Player_Team

Note: To accommodate URI query explained below, it is assumed that one ADO.NET DataService is added to the web project. To add ADO.NET Data Service, Right click on project and add new Item.  ADO.Net Data Service works on an entity model. So to create Entity Model Add New Item and select Entity Model.  Name of this entity model class will be used as base class for ADO.NET Service. 

How to create entity model and add ADO.NET services in a project. For step by step explanation, see my ADO.NET Services part 2 article on  c-sharpcorner.com.

Querying using URI on database

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

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

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

Result would be in browser as below.

Above service is using ATOM message format

Metadata

• One feature of ADO.NET data service 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 service provides metadata accessible. 
• Metadata expose all the resource and custom services. 
• Metadata could be accessed by appending $metadata to service URI.

So, the URI to access metadata is

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

And metadata will be in browser as, the below is content in browser. 

URI Query options

Any complex query could we written using URI.

1. Below URI will display record from Player_Information table.

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

2. Below URI will display record from 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:

• Below query will fetch  record of Team_Information with Team_Id  equal to T2
  
  http://localhost:2888/DataService.svc/Team_Information?$filter= TeamId eq 'T2'
  
  
   
• Below 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'
  
  
   
• Below query will fetch top 2  records  of Team_Information
  http://localhost:2888/DataService.svc/Team_Information?$top=2
• Below query will fetch playerID P1 or P2 from 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 query to fetch records. 
2. If Browser is unable to show the results of URI query make sure FEED Reading of browser is OFF.  TO do so, in IE Browser click on TOOL-> Internet option -> Content. Select Feeds and Web slices setting. And uncheck Turn on reading view checkbox. 

Access Rules

ADO.Net Data service class must inherit from DataService<T> class.

Where, T is name of the entity model class.

Here IPLEntities is entity model class.  DataService is name of ADO.NET Data service.

By default ADO.NET Data service disable all the access to entity model.

So it need to be enabled, such that client could access the entity model.  Developer has explicitly give action to model to access.

SetEntitySetAccessRule(,) method is used to give access. This method accept 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 set.

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

This will permit all write access on Player_Information entity set.

Creating the proxy class

There are two way to create proxy class for ADO.NET Data service at client side.

1. By adding service reference of ADO.NET Data service URI. 
2. By using ADO.Net client library.  To use client library there is need to add 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

Above article has explained theoretical concept of ADO.NET Data Service.  For step to step explanation of how to create ADO.NET Data service and consume it in different type of clients, see my other articles on c-sharpcorner.com.

Happy Coding