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

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–

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 –

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 –

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:

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 –

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 –

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 –

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

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 –

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

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

You will see the following output–

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/.

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 –

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

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 –

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 –

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

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 –

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

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

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 –

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 –

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 –

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 –

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

• http://localhost:42439/_vti_bin/ExcelRest.aspx/Shared%20Documents/Northwind.xlsx/Odata/CustomersTable?$top=5
• http://localhost:42439/_vti_bin/ExcelRest.aspx/Shared%20Documents/Northwind.xlsx/Odata/CustomersTable?$filter=City eq 'Berlin'

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.

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

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