ADO.NET Data Services in the .NET Framework

Introduction

This article will concentrate on ADO.NET Data Services in the .NET framework version 3.5. I will also be creating a sample ADO.NET Data service using Microsoft Visual Studio 2008 IDE and will explain various aspects of the service. In order to develop ADO.NET Data services Microsoft Visual Studio 2008 IDE and SP1 are required. In .NET framework 4.0 ADO.NET data service has been renamed WCF Data Service.

What is an ADO.NET Data Service?

An ADO.NET data service is a .svc file which is also said to be a specialized form of WCF service. It is a service which exposes the data from an underlying data source and the data can be requested to the service through specifically constructed URLs like the REST services.

For creating an ADO.NET data service you have to have:

  1. Data Source: The data source is the element which will be holding the actual data. It can be an .mdf SQL database file.
  2. Data Model Representation: This will lie in between the ADO.NET data service and the data source. This should provide the actual representation of the underlying data model. The data model representation can be done using a LINQ To SQL or ADO.NET Entity Model or even with custom classes. This intermediate element helps the ADO.NET data service to be loosely coupled with the underlying database, hence making the task of pointing the service to a different data source relatively easier.

Fig 1.0 shows a diagrammatic representation:

Diagrammatic representation
Fig 1.0

The ADO.NET data service supports all the http verbs like GET, POST, PUT and DELETE. The response formats of the ADO.NET data service are:

  1. ATOM
  2. JSON

The ADO.NET data service will take care of converting the URL representation into an actual request for data and will be able to perform queries on the IQueryable data provided by the intermediate data model.

Creating a ADO.NET Data Service

Let us now create a sample ADO.NET data service. Once the Data Service is ready we will get through running it and fetching the ATOM response. Also we will go through various useful query string options that are supported.

Create a simple ASP.NET website and delete the Default.aspx since we don’t have anything to do with the .aspx file. Now under the App_Data folder add a SQL server database and name it as BarDatabase.mdf. Add two tables namely Customer and Liquor to the database. Insert some sample records into the tables.

As a next step we should create a data model representation. In this application we will achieve it by using an ADO.NET Entity Data Model. So, let us add an ADO.NET Entity Data Model pointing to the existing database BarDatabase.mdf and name it as BarEntityModel.edmx. This will in turn create a BarEntityModel .designer.cs file under the App_Code folder with the class name BarDatabaseEntities. Fig 2.0 shows the design view of the .edmx file.

Design view of the .edmx file
Fig 2.0

Add an ADO.NET Data service to the web site and name it as BarDataService.svc. Go to the code behind file BarDataService.svc.cs and provide generic type as BarDatabaseEntities for the inherited generic class DataService. You will also find a comment that looks like this:

  /* TODO: put your data source class name here */

In the initialize service method provide the service access permissions and stuff. Below is the code for BarDataService.svc.cs:

  public class BarDataService : DataService<BarDatabaseEntities>
  {
      // This method is called only once to initialize service-wide policies.
      public static void InitializeService(IDataServiceConfiguration config)
      {
          config.SetEntitySetAccessRule("*", EntitySetRights.All);
      }
  }

Note that I have provided full access to all the entities by passing “*” as the entity name and EntitySetRights as All to the SetEntitySetAccessRule method.

Our first ADO.NET data service is ready to run. In order to test our ADO.NET data service simply run it. It should display response in ATOM format containing the entity set names.

Root URL accessed would be http://<host name>/<virtualdirectory name>/BarDataService.svc. Below is the response for the root URL request.

   <?xml version="1.0" encoding="utf-8" standalone="yes" ?>
   - <service xml_base="http://localhost:59290/ADONETDataService/BarDataService.svc/" xmlns_atom="http://www.w3.org/2005/Atom" xmlns_app="http://www.w3.org/2007/app" >
   - <workspace>
     <atom:title>Default</atom:title>
   - <collection href="Customer">
     <atom:title>Customer</atom:title>
     </collection>
   - <collection href="Liqour">
     <atom:title>Liqour</atom:title>
     </collection>
     </workspace>
     </service>
 

Just through URL construction you could point to the underlying data through the ADO.NET data service. Listed below are a few actions that you could perform using ADO.NET data service.

  1. Request a particular entity set – http://<host name>/<virtualdirectory name>/BarDataService.svc/<entityset name>.
  2. Request a particular record – http://<host name>/<virtualdirectory name>/BarDataService.svc/<entityset name>(<key>).
  3. Request a particular field of a record – http://<host name>/<virtualdirectory name>/BarDataService.svc/<entityset name>(<key>)/<field name>.
  4. You can use the below query string options. You need to prefix these query string options with $ symbol.
    1. Expand
    2. Filter
    3. Orderby
    4. Skip
    5. Top
  5. You can use the operators such as Eq, Or, And, Or, etc.

Below are the few sample URLs and the response from our BarDataService.svc. Request these URLs directly from Internet Explorer to see the response displayed on the same.

  1. Use http://localhost:59290/ADONETDataService/BarDataService.svc/Customer(1), this will select the customer with CustomerId as 1. CustomerId is the primary key for the Customer entityset. Below is the response:

        <?xml version="1.0" encoding="utf-8" standalone="yes" ?>
      - <entry xml_base="http://localhost:59290/ADONETDataService/BarDataService.svc/" xmlns_d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns_m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" >
        <id>http://localhost:59290/ADONETDataService/BarDataService.svc/Customer(1)</id>
        <title type="text" />
        <updated>2010-08-03T17:09:40Z</updated>
      - <author>
        <name />
        </author>
        <link rel="edit" title="Customer" href="Customer(1)" />
        <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Liqour" type="application/atom+xml;type=entry" title="Liqour" href="Customer(1)/Liqour" />
        <category term="BarDatabaseModel.Customer" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
      - <content type="application/xml">
      - <m:properties>
        <d:CustomerId m_type="Edm.Int32">1</d:CustomerId>
        <d:FirstName>Peter</d:FirstName>
        <d:LastName>Willey</d:LastName>
        <d:LastVisited m_type="Edm.DateTime">2009-04-02T00:00:00</d:LastVisited>
        <d:FirstVisited m_type="Edm.DateTime">2008-02-24T00:00:00</d:FirstVisited>
        </m:properties>
        </content>
        </entry>
    

  2. Use http://localhost:59290/ADONETDataService/BarDataService.svc/Customer/?$top=2. This will fetch top 2 records from the Customer entity set. Below is the response:

         <?xml version="1.0" encoding="utf-8" standalone="yes" ?>
       - <feed xml_base="http://localhost:59290/ADONETDataService/BarDataService.svc/" xmlns_d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns_m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" >
         <title type="text">Customer</title>
         <id>http://localhost:59290/ADONETDataService/BarDataService.svc/Customer/</id>
         <updated>2010-08-03T17:12:55Z</updated>
         <link rel="self" title="Customer" href="Customer" />
       - <entry>
         <id>http://localhost:59290/ADONETDataService/BarDataService.svc/Customer(1)</id>
         <title type="text" />
         <updated>2010-08-03T17:12:55Z</updated>
       - <author>
         <name />
         </author>
         <link rel="edit" title="Customer" href="Customer(1)" />
         <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Liqour" type="application/atom+xml;type=entry" title="Liqour" href="Customer(1)/Liqour" />
         <category term="BarDatabaseModel.Customer" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
       - <content type="application/xml">
       - <m:properties>
         <d:CustomerId m_type="Edm.Int32">1</d:CustomerId>
         <d:FirstName>Peter</d:FirstName>
         <d:LastName>Willey</d:LastName>
         <d:LastVisited m_type="Edm.DateTime">2009-04-02T00:00:00</d:LastVisited>
         <d:FirstVisited m_type="Edm.DateTime">2008-02-24T00:00:00</d:FirstVisited>
         </m:properties>
         </content>
         </entry>
       - <entry>
         <id>http://localhost:59290/ADONETDataService/BarDataService.svc/Customer(2)</id>
         <title type="text" />
         <updated>2010-08-03T17:12:55Z</updated>
       - <author>
         <name />
         </author>
         <link rel="edit" title="Customer" href="Customer(2)" />
         <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Liqour" type="application/atom+xml;type=entry" title="Liqour" href="Customer(2)/Liqour" />
         <category term="BarDatabaseModel.Customer" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
       - <content type="application/xml">
       - <m:properties>
         <d:CustomerId m_type="Edm.Int32">2</d:CustomerId>
         <d:FirstName>David</d:FirstName>
         <d:LastName>Shepard</d:LastName>
         <d:LastVisited m_type="Edm.DateTime">2010-04-30T00:00:00</d:LastVisited>
         <d:FirstVisited m_type="Edm.DateTime">2009-05-02T00:00:00</d:FirstVisited>
         </m:properties>
         </content>
         </entry>
         </feed>
     

  3. Use http://localhost:59290/ADONETDataService/BarDataService.svc/Customer(5)/FirstName. This will fetch the FirstName of the customer record with CustomerId as 5. Below is the response:

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

Conclusion

ADO.NET data services are best suited for providing data feed. Hope this article is informative and provides a good insight on ADO.NET data services. In later aticles I will write an article on consuming the ADO.NET data service through a .NET framework client shortly. As always I have added the code to the article.

Happy reading! See you in my next article.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read