CRUD operations on Microsoft SharePoint 2010 oData Lists with cURL

I’ll get straight to the point. cURL is a command line tool for transferring data with URL syntax, supporting HTTP, HTTPS, etc. oData is a Web protocol for querying and updating data built on top of upon Web technologies such as HTTP, Atom Publishing Protocol (AtomPub) and JSON.

Microsoft SharePoint 2010 exposes their Lists using the oData protocol which you can access from the following URL – http://<sharepoint>/_vti_bin/ListData.svc.

Here’s how you can do all 4 CRUD (Create, Read, Update, Delete) operations on your Microsoft SharePoint 2010 Lists using cURL which you can easily translate it to JSON and JQuery once you understand what’s going on.

Setting up

Basic syntax (for our purpose):
[sourcecode language="bash" autolinks="false"]
curl -ntlm -netrc http://sharepoint/_vti_bin/[moreurl]
[/sourcecode]
where:
[sourcecode language="bash" autolinks="false"]
.netrc (mode 600)
machine sharepoint login user password pwd
[/sourcecode]

Create

In order to create an entry into the list, you have to pass in the data as shown below with the command “-d”. I’m passing in JSON in this case as set with the command “-H”, but you can pass in an XML string if you wish too. I find JSON shorter and more concise. You use the POST method request in order to create an entry or item.
[sourcecode language="bash" autolinks="false"]
curl –ntlm –netrc
-d "{ Title: ‘January Team Meeting’, StartTime: ’2011-01-15T17:00:00′, EndTime: ’2011-01-15T18:00:00′ }"
-H content-type:application/json
-X POST http://sharepoint/_vti_bin/ListData.svc/Calendar
[/sourcecode]
Notice I did not set all the fields from the Calendar List. You do need to set all the fields that are required, but those not required and not specified will be set to NULL or the default value.

You will get back a reply with the oData entry if successful.

Read

Getting data is as simple as doing a normal GET method request from the List itself.
[sourcecode language="bash" autolinks="false"]
curl –ntlm –netrc http://sharepoint/_vti_bin/ListData.svc/Calendar
[/sourcecode]
Here is a quick list of what kind of filters you can do.

Syntax:

http://sharepoint/_vti_bin/ListData.svc/{Entity}[({identifier})]/[{Property}]

Example to get budget hours for Project $4:

http://sharepoint/_vti_bin/ListData.svc/Projects(4)/BudgetHours

Example to get Projects for Clients in Chicago:
http://sharepoint/_vti_bin/ListData.svc//Projects?$filter=Client/City eq ‘Chicago’

Example to get a Project and its related Client:

http://sharepoint/_vti_bin/ListData.svc/Projects?$expand=Client

Shows me parent that is associated with this XML

QueryString parameters for REST:
$filter
$expand
$orderby
$skip
$top
$metadata (will bring back all the XML metadata about the object. Think of it like WSDL for your REST call)
You can stack these parameters

Update

There are 2 ways of updating Microsoft SharePoint 2010 oData items. Notice the URL is pointing to the specific entry, in this case the entry with id=2 in the Calendar list.

One way is to use the PUT method request which essentially replaces an entirely new version of data into that entry. This is similar to creating an entry or item – You do need to set all the fields that are required, but those not required and not specified will be set to NULL or the default value.
[sourcecode language="bash" autolinks="false"]
curl –ntlm –netrc
-d "{ Title: ‘January 2011 Team Meeting’, StartTime: ’2011-01-15T17:00:00′, EndTime: ’2011-01-15T18:00:00′}"
-H content-type:application/json
-H If-Match:*
-X PUT "http://sharepoint/_vti_bin/ListData.svc/Calendar(2)"
[/sourcecode]
Notice that you need to have the header “If-Match:” in order to enable the update to happen. This is to prevent concurrent updates and modifications to happen. Essentially, the right way is to read the data and get the “If-Match:” header tag which looks something like this If-Match: W/”X’000000000000D2F3′”, then put it in the header when you update. However, in this case, If-Match:* essentially ignores all concurrency problems and just tells the oData service to update.

The other way is to modify only the changes made, keeping all the other fields with data intact. You use the MERGE method request instead as seen below.
[sourcecode language="bash" autolinks="false"]
curl –ntlm –netrc
-d "{ Title: ‘January 2011 Team Meeting’}"
-H content-type:application/json
-H If-Match:*
-X MERGE "http://sharepoint/_vti_bin/ListData.svc/Calendar(2)"
[/sourcecode]

It will return 200 (OK), 201 (Created) or 202 (Accepted) if successful. (I can’t remember which at the moment)

Delete

Deleting is as simple as using the DELETE method request.
[sourcecode language="bash" autolinks="false"]
curl –ntlm –netrc
-X DELETE "http://sharepoint/_vti_bin/ListData.svc/Calendar(2)"
[/sourcecode]

A successful delete will return a 200 (OK) status code.

Summary

So that’s how you do CRUD operations with all that low level HTTP method request. Create – POST, Read – GET, Update – PUT or MERGE, Delete – DELETE. Remember, in order to update, you need the “If-Match:” header when you send a PUT or MERGE method request else it will fail.