Friday, September 3, 2010

jQuery + OData

Some stuff I've been learning at work: jQuery+ OData! There's little docs on this, and what's out there is hard to get working. After numerous headaches, I found a way to get it working. You can grab a fully working example on my Github account.

Here's the recipe I used to create the app:

  1. MVC 2
  2. Add jQuery reference to Site.Master
  3. Add jQuery.Tmpl plugin to Site.Master
  4. Add json2 library to Site.Master
  5. Create ADO.NET Entity Data Model
  6. Add tables and fields as desired
  7. Publish SQL to your SQL server
  8. Create a WCF Data Service
  9. Set your entity access rule to "*" and EntitySetRights.All
  10. Disable WebDAV for your app
  11. Build the app
  12. Client-side code

There's a few neat things you get out of this. One is that your app is RESTful. REST is just a philosophy, but adopting it can make life really easy as it does here - jQuery is able to communicate with the server without any special tools (save the templating plugin, but that's so we're 100% client side). We're just sending JSON back and forth and using HTTP Verbs (that's all REST is, distilled). The particular structuring isn't defined by REST - it's OData. OData best thought of as a REST implementation. It also includes a query format so I can ask for certain kinds of objects, only certain fields, etc. Just to give some background, OData is an open standard that Microsoft is behind. The OData site itself has links to libraries used in many different languages, not just the .NET variety. Netflix uses it as well, and you can query movies in all different kinds of ways using their public OData API.

There seems to be a lot of cool things about OData, but the things that are interesting to me is that there's no work on the server-side to support different formats such as XML and JSON. Ask for what you want in the request type and you get a response back the way you wanted. You can filter what kind of objects you want server-side or even just pulling certain fields back using a query string. Again, there's no work on the server developer's end to support this.

Documentation and examples can be given freely with the jQuery code I have. One of the PHP or Java guys could pick this up and use it on their client side or figure out how to do it server-side by interpreting a language all web developers must learn: JavaScript. If they stick to client side, they have super easy examples.

I also don't have to get nitty gritty with MVC and how it handles me moving between pages and the like. I can be a one-page app very easily here.

The data service classes provide hooks for intercepting queries and sets so you can ensure validation, add additional constraints, and strip constraints to prevent DoS attacks with expensive queries. I haven't gotten into the details of the server-side yet.

1) Create MVC 2 Project
This is pretty self-explanatory (:

2) Add jQuery to reference Site.Master

Microsoft and Google host jQuery. This means you can add a JavaScript reference without even having to add a file to your project.

<script src="" type="text/javascript"></script>

3) Add jQuery.Tmpl plugin to Site.Master
This you'll need to snag from Github. At time of writing, the plugin only works on Firefox, but one of the other forks likely fixes this issue (it's an easy fix, but why not get all of the other community fixes?). nje's fork seems to be the most current/active, so we'll grab that. I like to use IIS to host my apps locally so I can get deployment issues out of the way. It also means I can refer to webservices and the like without having to worry about the port changing on me, or the app turning off because I didn't touch it every few minutes or leave my IDE running.

Without IIS:

<script src="Scripts/jquery.tmpl.js" type="text/javascript"></script>

With IIS:

<script src="ODataPrototype/Scripts/jquery.tmpl.js" type="text/javascript"></script>

4) Add json2 library to Site.Master
Same as #3 with regards to IIS. This is the official JSON library. Use it like so:
Without IIS:

<script src="Scripts/json2.js" type="text/javascript"></script>

With IIS:

<script src="ODataPrototype/Scripts/json2.js" type="text/javascript"></script>

5) Create ADO.NET Entity Data Model
Others have explored this in detail. We just need to create the edmx. I have to remind myself that this is the container I'm naming, not the table/entity. I named my data model as ODataPrototypeDb.

6) Add tables and fields as desired
The Entity framework calls database fields scalar properties. I'm going to name my entity as User and add the fields FirstName and LastName (strings).

7) Publish SQL to your SQL server
This can be done within Visual Studio pretty easily. Right click on your Entity designer and click "Generate Database from Model". The wizard will help you get your connection string configured. You'll need to create the DB manually (and ensure permissions is necessary). I configured it against my localhost and used Windows Authentication. After you finish the wizard, it'll generate a .sql file. You can execute this from within the IDE itself. Right click on the SQL editor in Visual Studio -> Connection -> Connect. Once connected right click again -> Execute SQL. Now all of your tables and fields should be configured! Be aware that you can update your Entity Data Model and push those changes back to the DB, but doing so as I described above will drop your tables and recreate them. You'll want something more intelligent after you've made it into production (:

8) Create a WCF Data Service

Add a new item to your project. Under the Web category is WCF Data Service. I named mine PrototypeDataService. Generically point the data service to the Entity container type made above (not one of the entity types itself).

9) Set your entity access rule to "*" and EntitySetRights.All
This will allow all entities to be read and written to in any manner. In a real enterprise app we'd constrain this a bit. You can just uncomment line 18 and change it to accomplish this.

10) Disable WebDAV for your app
WebDAV will swallow any HTTP PUT and DELETE verbs. This will prevent you from updating and deleting entities via your RESTful OData service. To disable WebDAV for your app, just add this to your web config under the configuration node:

This will only bite you in IIS, but you have to eventually deploy to IIS anyways. Why not also run in IIS locally in your project settings so you can feel these pains earlier? (:

11) Build the app
Ctrl + Shift + B - After that you're only doing UI. No more build/run as part of your dev cycle. Just save/run.

12) Client-side code
In your home page, add the JavaScript example. I've included the entire page. I made my changes in Views/Home/Index.aspx, which is the root page of the app.

Example of doing CRUD with a WCF Data Service using OData + jQuery (all client-side code):

Misc tips:
Add this line to your DataService to get descriptive errors back from the service while you're debugging.

To Get IIS working nicely, I had to set the identity for the default app pool (ASP.NET v4.0) to my logged in account, because that's the owning account for SQL Server.

Big thanks for Scott Hanselman for introducing me to jQuery + OData during this talk.


  1. [...] added the Github repository for my previous post regarding using jQuery and OData (WCF Data Services) in .NET. The original post has been updated as [...]

  2. Hello, i get a list of user in a json objec that displaying on my paget. I use datalinking to bind each values object with controls. Then i want to send back the collection of users to the server when i click "update". How can we proceed to send a collection of object and how OData manage this collection of object. The ChangeInterceptor can handle only one object at a time... Thanks in advance for your help. (P.S ; I want to find a solution in JQuery only) Regards.

  3. Have you tried sending all the data at once as a nested JSON object (an array of the objects you would normally send individually)? If you did that, be sure to leave the ID off of the PUT URL. I know OData supports batched updates, but that's my only guess as to how. I'm not sure if you'd want to use a ChangeInterceptor if it sends things individually, especially if you have an update button for everything. Let me know if any of that works!

  4. Thanks for your feedback. Ok ,I'll try to send all object in once and see how OData will react. See u soon.

  5. By the way, why don't you use the Merge verb for updating your data ? As i know , when you send a request with "PUT" method , OData will update all fields of your object present in the body of your request and the other ones not present in it will be set to a default value. In the case of Merge Method, only the fields sended into the request will be updated.

  6. Thanks Logan - I now know a little more about REST and OData.

  7. Issou,
    Keep in mind the concurrent update verb is changing from MERGE to the more standard PATCH.

    I didn't use this because I wanted a fairly simple example. I believe when you send up the Merge/Patch request you need to indicate which fields changed, or push along the originals along with the new changes. If there's a concurrency collision, you'll need to handle it, whereas blanket updates don't care. Either way, it's more complicated than just sending everything up, and I was going for simple

    However, my code is up on Github. If you'd like to add an example with merge/patch I'll be sure to update the post and give you mention!


Note: Only a member of this blog may post a comment.