Ashok Raja's Blog
Querying and exploring REST API with oData in SharePoint 2013 with DataBase .net
by Ashok Raja T 13. May 2013 06:17

Working with oData in SharePoint 2013 becomes a breeze with DataBase.net (free for non-commercial use) , which helps in exploring oData and query REST API or any conventional databases. In this blog post we can see how to configure DataBase.net to communicate with SharePoint 2013 REST APIs.

DataBase.net is a portable windows application that can be used to communicate with almost all databases out there in the market. One of the new addition to their feature set is to communicate with Rest API. You can download the latest version of DataBase.net from here.

Connecting SharePoint 2013

1. Start the database.net application and click the connect button and select oData from the list of available connection options and select the “Create” link.

clip_image001

2. Provide the connection details like URL of the REST API and User Name and password to connect the service. The URL to access list data would be http[s]://[servername:port]/_vti_bin/listdata.svc

clip_image002

3. Once successfully connected , database.net would display all the list from that web as shown below.

clip_image003

4. To query a particular list, right click the list and select the appropriate menu item you would like to execute against the list.

clip_image004

5. The below is the query result of Top 100 rows

clip_image006

6. Filters can be passed as parameters to restrict the amount of data to be retrieved from source list.

Skip Records

clip_image007

Records based on specific operator

String Filter

clip_image008

Numeric Filter

clip_image009

Restricting Columns and string filter based on column values

clip_image010

Querying Rest API with oData Queries

oData accepts certain set of string , date, numeric filters to be passed while querying REST API. The below are some of the available filter options that can be used in a oData query to filter data from REST API.

Note: The below details on filter parameters are from Ted Pattison’s SPC 2012 demos, which can be downloaded from his site.

$filter parameter

clip_image012

$filter Parameter and String Functions

image

$filter Parameter and Date Functions

clip_image016

$filter Parameter and Math Functions

clip_image018

$filter and Type Functions

clip_image020

Note: The above details on filter parameters are from Ted Pattison’s SPC 2012 demos, which can be downloaded from his site.
Note: This is not an article to promote a specific product or tool. I personally believe that this tool (“Database.net of fishcodelib”) will minimize SharePoint developer’s time in dealing with SharePoint 2013 Rest APIs. The alternative would be to accessing the REST API URL along with the filters in the browser. In that case, the result will be displayed as XML. But this tool will help you to visualize the output in clean and readable manner.

Links

Ted Pattison’s SPC 2012 Slide Deck ( You have to register in his site to download the deck)

Database.net from Fish Code Lib (Free for non-commercial use)

Knockout Js For SharePoint KoSpJs- Knockout Js Binders for SharePoint REST API

blog comments powered by Disqus