24 Hours of PASS – Session Review (18 – ADO .NET Data Services)

On September 2, 2009, I had the pleasure to deliver a session on ADO .NET Data Services, during that great SQL Server Marathon; 24 Hours of PASS! This great event was a series of 24 non-stop live, free webcasts dedicated to pure SQL Server knowledge. The exact topic of my session was: “Building Flexible Data Services for the Web Using the ADO .NET Data Services Framework”. Yes, it is true; it was the longest titled session of the event 🙂

Further below I provide a short review for my session and I also provide the PowerPoint presentation along with the DB scripts and source code (C#) of my demos.

The problem domain

Many applications, and especially web applications, face a set of problems in today’s environments. For example, it is difficult to identify the different types of clients and users accessing these web applications. Each different client has its own particularities which make it difficult, if not impossible, for web applications to perfectly work with all these types of clients. This requires different web methods for each client, different paging and filtering techniques and so on.

Furthermore, during the last few years, the wide usage of the Internet enabled the massive spreading of Web Applications. To this end, a new form of web applications has emerged; the well known “Connected” applications. This new type of web applications includes Rich AJAX web applications, Flash & Silverlight applications, Mashups, etc. These applications need continuous feeding of data in dedicated data channels in order to be fast and efficient. They somehow require a separation of the data and presentation layers.

The old approach

The traditional way for enabling web applications to efficiently work with the different types of clients was to include additional logic in their code in order to cover most of the different scenarios. Though, this is not anymore an efficient way for handling the uniformity of clients which access these applications as the different types of clients are highly increased.

How about using a framework that helps you develop and access data services in a uniform way?
Such case could be really interesting…

Introducing the Microsoft ADO .NET Data Services Framework

The ADO.NET Data Services framework consists of a combination of patterns and libraries which enable the creation and consumption of data services for the web. Using the framework, it is easy to create flexible data services that are naturally integrated with the web and maintain the REST style. To this end, URIs are used to point to pieces of data and well-known formats such as JSON and XML are used for representing that data.

Interacting with your data is now uniform and easy as by using the HTTP verbs GET, PUT, POST and DELETE, you can perform any operation you like on your data.

The mappings between the above HTTP verbs and the SQL language are:

24 Hours of PASS – Session Review (18 - ADO .NET Data Services)

If you use the web, and specifically HTTP to provide and consume your data, you will find ADO.NET Data Services very interesting as by utilizing the enormous amounts of infrastructure that already exists to support HTTP due to the wide spreading of the Internet, you are able to easily build flexible and powerful data services fully supporting “Connected” applications which is nowadays the trend in web applications design.

The following figure illustrates the different types of clients that access data through HTTP:

24 Hours of PASS – Session Review (18 - ADO .NET Data Services)

You can see that there is a plethora of different clients accessing the same data through HTTP.

Why Use the ADO .NET Data Services Framework?

The answer to this question is quite simple; as “Connected” applications (Rich AJAX web applications, Flash & Silverlight applications, Mashups, etc.) are the trending type of web applications, they need the corresponding data services in order to feed them with data, without affecting their presentation layer. ADO .NET Data Services allows these applications to have a dedicated data channel which allows it to be fed with data by an ADO .NET Data Service. Usually the only line of sight from these applications, is HTTP, so using the framework makes things really simple.

With this technology, the data is moved out-of-band with the presentation and behavior information thus allowing the web application on the one hand not have any delays on the presentation layer, and on the other hand to have continuous access to its data source.

The architecture of an ADO .NET Data Service

The following figure illustrates the architecture of a data service:

24 Hours of PASS – Session Review (18 - ADO .NET Data Services)
At the very bottom of this architecture there are the relational and non-relational sources.
Next there is the ADO .NET Entity Framework which is used for handling the relational sources and a custom LINQ provider for handling the non-relational sources. The Data Services Runtime exists as a DLL. Next, there is the Hosting/HTTP Listener for which the out of the box implantation is WCF hosted within an ASP .NET web page (though, you can author your own listener if you like). On the very top of this architecture, there is the HTTP stack, against which you can issue HTTP commands (using the abovementioned HTTP verbs) in order to access and process your data.

Consuming a Data Service

The following figure illustrates the various ways available for consuming a data service implemented within the ADO .NET Data Services framework:

24 Hours of PASS – Session Review (18 - ADO .NET Data Services)
As you can see, actually an HTTP stack is enough! Of course, if you like to access your data service using higher levels of abstractions you can use JSON & AtomPub clients and even easier; Windows Data Services Clients such as .NET Framework, Silverlight 2 or 3, AJAX, and so on.

Securing a Data Service

There are several ways available for securing a Data Service within the ADO .NET Data Services Framework.

With respect to Visibility, you can use:

  • Per-container visibility control
  • Read, Query and Write options

With respect to Authentication you can use:

    • Integration with hosting environment
    • ASP.NET, WCF or custom authentication schemes


With respect to Interceptors you can use:

  • Execution before HTTP GET/PUT/POST/DELETE
  • Enabling of validation and custom row-level security policies in order to maintain the REST style

With respect to Service Operators you can use:

  • Custom entry points, e.g. /MyTags?startDate=’2009-09-02′
  • Composable, follow URI scheme

PowerPoint Presentation

Please find below my PowerPoint presentation for this session.

Source Code for Demos

Please find below the source code for my demos.

Development Environment/Prerequisites:
Visual Studio 2008 SP1
.NET Framework 3.5 SP1
SQL Server 2008 SP1 (used as the relational data source)
ADO.NET Data Services v1.5 CTP1

* For all the demos, you will have to modify the connection string in the Web.config file, connection string entry in order to point to your SQL Server 2008 instance. To this end, please replace “.sql2008” with your SQL Server 2008 instance name. Also I used Windows Authentication (trusted connection) for connecting to the SQL Server Instance.

Download link for source database.

Demo 1: Examining an ADO .NET Data Service

Summary: In this demo, we examine a pre-built data service and see how we can interact with it using a Web Browser and an HTTP debugging tool.

Download link for demo 1 (and 2) source code.

Demo 2: Building a Data Service from Scratch

Summary: In this demo I build a data service from scratch using Visual Studio 2008 SP1.
Procedure: There is not sample code for this demo, as by completing the demo, you end up to a source code identical to the source code of demo 1.

Demo 3: Consuming a Data Service

Summary: In this demo, in addition to the data service which was build during demo 2, I also create a .NET client (Windows Console Application in C#) within the same project for consuming the service using LINQ to Data Services (this is actually LINQ to Entities).

Download link for demo 3 source code.

* Disclaimer: The source code for the demos is intended to be used only for demo purposes. Do not use it for Production systems as it is simplified for demo purposes.


Summing up this review, I would like to say that using ADO.NET Data Services makes it really easy to expose your data over HTTP and thus build powerful and flexible data services.

Using URIs (entities and associations –> resources and links), HTTP Verbs for data manipulation, and the well-known and simple formats JSON and AtomPub for data representation makes it easy to build RESTful data services with all the benefits derived by using the REST style.

ADO .NET Data Services are very useful for developing AJAX & Silverlight Applications, Online Data Services, Mashups and much more.

Of course, I could not end this review without mentioning the Cloud. Many of the Microsoft cloud services expose data using the same REST conventions as used by ADO .NET Data Services. This enables using the ADO .NET Data Services Framework not only for on premises services, but also with services hosted on the cloud!

This session’s topic falls under one of my favorite Microsoft Technologies; ADO .NET – Data Access! I plan to deliver even more webcasts in the future, again dedicated to ADO .NET.

I would like to thank PASS for inviting me to actively participate to this great SQL Server event and congratulate all the people who made this event a smashing success!

Drop me a line if you have any comments!


Learn useful SQL Server development tips! Check our new Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course

Learn More


Featured Online Courses:


Read Also:


Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Generate dynamic T-SQL scripts with Dynamic SQL Generator!

Check our latest software releases!

Check our eBooks!


Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)


Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub