Querying and exploring REST API with oData in SharePoint 2013 with DataBase .net

Solutions Architect

May 13, 2013

Rate this article

Views

1918

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.

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

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

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

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

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

Skip Records

Records based on specific operator

String Filter

Numeric Filter

Restricting Columns and string filter based on column values

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

$filter Parameter and String Functions

$filter Parameter and Date Functions

$filter Parameter and Math Functions

$filter and Type Functions

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