Testing and Consuming OData Services using Fiddler, LinqPad, Excel and SharePoint

Posted by: Pravinkumar Dabade , on 4/12/2013, in Category SharePoint
Views: 69299
Abstract: Consume and test OData Services by using different tools like Fiddler, LINQPAD, EXCEL, and SharePoint Server 2013 Excel Services OData.

In this article, we will take a closer look on the different tools we can use for Consuming and Testing OData Services. We will look at Fiddler, LINQPAD, EXCEL, and SharePoint Server 2013 Excel Services OData.

A couple of days ago, I demonstrated how to Perform CRUD Operations using OData Services in .NET. If you have not gone through the article, you can take a quick peek at it to understand how OData works. OData (Open Data Protocol) is a web protocol for performing CRUD operations which is built upon web technologies like HTTP, Atom Publishing Protocol (AtomPub) and JSON to provide access to the data to various applications, services and stores. Today we’ll see how we can use some tools and products available to test and consume OData services.

 

The OData Application

For this demonstration, I am using Visual Studio 2012 and SQL Server 2012 with Northwind database preconfigured.

Let’s start first by creating a new empty Web Application with the name “NorthwindODataServiceWebApp”. Once your project is ready, right click the web application and add a new “ADO.NET Entity Data Model” with the name Northwind–

addentitymodel

You should see the Entity Data Model Wizard. Click on “Generate from database”. In the next step, select “Northwind database”. In case you don’t see the Northwind database connection string, click on New Connection button and set a connection string to work with the Northwind database.

Once you setup the connection string, in the next step, we will select the following tables –

  1. Customers
  2. Orders
  3. Order Details
  4. Products
  5. Employees

Now click on the Finish button. You will see a list of entities with appropriate relationships as shown below –

entites

Now it’s time to create an OData Service. WCF Data Services supports OData out of the box, so for today’s demo we’ll use a WCF Data Service. Right click the web application and add a new item. Select WCF Data Service and name the service “NorthwindWCFDataService” as shown below –

addwcfdatasrv

Now if you take a look at the code of our WCF Data Service, it has been derived from DataService<T>. Replace the T with NorthwindEntities and write the following code in our service –

public class NorthwindWCFDataService : DataService<NorthwindEntities>
{
public static void InitializeService(DataServiceConfiguration config)
{
  config.SetEntitySetAccessRule("Customers", EntitySetRights.All);
  config.SetEntitySetAccessRule("Orders", EntitySetRights.All);
  config.SetEntitySetAccessRule("Order_Details", EntitySetRights.All);
  config.SetEntitySetAccessRule("Products", EntitySetRights.All);
  config.SetEntitySetAccessRule("Employees", EntitySetRights.All);
  //config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
  config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
}
}

Now press “F5” to run your service and you will see the following output in Internet Explorer:

srvoutput

Our sample application is ready. Let’s explore the OData Service using various utilities that will help us to debug real life OData services.

Testing our OData Service from various clients

Using IE

Now let’s try different options in Internet Explorer by changing the URI as shown below –

1. Show all the Customers –

http://localhost:14596/NorthwindWCFDataService.svc/Customers

2. Show Top 10 Customers –

http://localhost:14596/NorthwindWCFDataService.svc/Customers?$top=10

3.Filter the Customers according to City –

http://localhost:14596/NorthwindWCFDataService.svc/Customers?$filter=City eq 'Berlin'

As we can see each search returns a different XML

Using Fiddler

Now let’s test our OData Service using the Fiddler tool. If you don’t have the Fiddler tool installed on your machine, get it from www.fiddler2.com.

Now let’s open Fiddler and click on Compose tab. Write a URL in the address bar and click on Execute button as shown below –

fiddler1

If you check the Web Session window, you will find a request result with HTTP status code 200 which means your request has been executed with “OK” status. It should look like below –

websession

If you take a closer look on our Content-Type, it is application/atom+xml with type=feed. Now double click on the above web session. You will see the “Inspectors” table in which you will see Request Header with HTTP GET verb. You will also see the response as shown below –

websessionresponse

Now let’s try the same URL’s that we tested sometime back in our browser.

1. Show Top 10 Customers –

http://localhost:14596/NorthwindWCFDataService.svc/Customers?$top=2

websessionresponse1

If you check, you will see the output of two records from Customers entity as we have mentioned top 2 in our URI.

Now let’s try below URI –

2. Filter the Customers according to City –

http://localhost:14596/NorthwindWCFDataService.svc/Customers?$filter=City eq 'Berlin'

If you type above URI, you will find the Address bar goes red which implies that the URI is incorrect. So, what’s wrong in our URI? If you a take a closer look, there is a space between city and eq and eq and Berlin. The spaces should be replaced with %20%. Now try the URI and let’s check the output.

The new URI should look like below –

http://localhost:14596/NorthwindWCFDataService.svc/Customers?$filter=City%20eq%20'Berlin'

You will see the output like below –

websessionresponse2

Now let’s try a join query as shown below –

http://localhost:14596/NorthwindWCFDataService.svc/Customers('ALFKI')/Orders

You will see the following output–

websessionresponse3

Using LinqPad

Instead of typing our raw queries, if we wanted to query our service using Linq, LINQPad is an awesome utility. Let’s see how we can use LINQPad to query an OData Service.

If you don’t have LINQPad, you can download the same from http://www.linqpad.net/.

linqpad

Now click on “Add Connection” from the left side pane. This will display the “Choose Data Context” window. Select “WCF Data Service 5.1 (OData 3)” as shown below –

linpadwizard1

Click on the “Next” button. Enter the URI, select formatter and test the connection with our OData Service as shown below –

linpadwizard2

Click on the OK button. Now at the left hand side pane, you will see our OData Service connection with all the Entities exposed by our service as shown below –

linpadentities

On the right hand side in a “Query 1” window, select Language as C# and select connection to our OData Service. Now from the left side pane, right click on the Customers entity and select Customers.Take (100). Click on the Execute button and select “Request Log”. You will see the constructed URI as shown below –

queryoutput1

You can construct queries manually as well. Let’s write the following query in a query window and observe the output –

query2

So, by using the LINQPAD tool, we can construct simple URIs which we can use to fetch data from OData services.

Using Excel

Now let’s try fetching our OData service in Excel 2013. Open Microsoft Excel 2013. Create a new Excel Workbook.

Now go to “DATA” ribbon. Click on “Get External Data” dropdown button. Select “From Other Sources” and “From OData data feed” as shown below –

excelds

This will show you a Data Connection Wizard. Specify the link - http://localhost:14596/NorthwindWCFDataService.svc/ and click on next button as shown below –

exceldataconnection

In the next step, you will see a list of tables. Select all the tables.

chooseexceltbl

Click on Next button and you should see the data connection. Keep the default settings and click on “Finish” button. The wuzard will ask how do you want to import the data. Select Tables as shown below –

exceldataimport

Once you click on the “OK” button, you will see a separate Excel Sheet for each table which we have chosen in our wizard. It should look similar to the following –

exceloutput

Now select each table. Go to design ribbon. Specify the name of each table as –

  • CustomersTable.
  • OrdersTable.
  • OrderDetailsTable.
  • ProductsTable.
  • EmployeesTable.

Save your workbook with the name Northwind.xlsx on your local drive.

Using SharePoint 2013 Excel Services

Now it’s time to consume the OData for Excel Service in SharePoint Server 2013. Open SharePoint Site and upload our excel report which we have saved with the name “Northwind.xlsx” in Documents library. It should look like the following –

uploadexcelsps

Now let’s open Internet Explorer and type the following URI in the address bar –

http://localhost:42439/_vti_bin/ExcelRest.aspx/Shared%20Documents/Northwind.xlsx/Odata/CustomersTable

You will see a similar output –

spsexceloutput

Now you can filter the data, select top records and try multiple URI’s like below –

The above URI consists of three different parts as mentioned below –

1. REST ASPX Page URI – http://<<ServerName>>/_vti_bin/ExcelRest.aspx.

2. Workbook Location – The SharePoint Library where you have kept your Excel Workbook. For Example – /Shared%20Documents/Northwind.xlsx.

3. Resource Location – /OData/TableName.

Summary

In this article, we saw how to consume and test OData Services by using different tools like Fiddler, LINQPAD, EXCEL, and SharePoint Server 2013 Excel Services OData.

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Pravinkumar, works as a freelance trainer and consultant on Microsoft Technologies. He is having over 10 years of experience in IT and is also a Microsoft Certified Trainer(MCT). He has conducted various corporate trainings on all versions of .NET Technologies including .NET, SharePoint Server, Microsoft SQL Server, Silverlight, ASP.NET, Microsoft PerformancePoint Server 2007 (Monitoring). He is passionate about learning new technologies from Microsoft. You can contact Pravinkumar at dabade[dot]pravinkumar [attherate] gmail[dot]com


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by jeremiah on Tuesday, July 16, 2013 7:49 AM
MUST you put your silly watermark on every image?  Are your screenshots of other people's applications so precious to you that you must watermark them?

You need two versions of the image.  One, without a watermark, when the image is served from your own site.  A second version with the watermark to prevent hotlinking.  You can determine which to serve by the HTTP referral header.  Don't change the link for each version just serve up both images from the same URL.

Watermarking your own images on your own site is very very ugly.
Comment posted by Manoj Jangid on Thursday, September 19, 2013 11:27 AM
how do i update my data using excel (odata put method)

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

FREE .NET MAGAZINES

Free DNC .NET Magazine

Tags

JQUERY COOKBOOK

jQuery CookBook