Archive
Insert multiple oData records with a single post
A few months ago I was playing around with some user interface ideas. I used the jQuery sortable which will save to my oData service after the user had made a change. This caused multiple posts to the database, depending on the amount of items added, removed or reordered should the user navigate away before it was all completed.
After some searching I found a way around this which I decided to play around with again today for a project I will be doing soon. The idea is to combine all the updates/inserts into a sequence and then submit it in a single post. This will make it a lot quicker and also have a lot less code to display a single message when all the updates are complete.
This post will not go into much detail about getting the whole lot set up, so here is what you need already set up:
- ASP.NET website building and running
- WCF Data Service set up to return values when queried through the browser
- MS AJAX Toolkit (Had to download the source to get all the .js libraries I needed)
For the sake of not struggling I copied all the files from the .\MS Ajax\SampleWebSites\AjaxClientWebSite\Scripts\MicrosoftAjax folder into my website’s Scripts folder and referenced only Start.debug.js (or Start.js for release). This takes care of loading all the rest of the required libraries.
My data service was called TestDataService.svc with a single table containing two columns since this was just the bare minimum to get my head around it again.
In my html page I have the following code to load, display and insert data.
// We require this library Sys.require([Sys.components.dataView, Sys.components.openDataContext, Sys.components.openDataServiceProxy]); // A reference to our service var exampleService; // Page loaded and DOM ready Sys.onReady(function () { // Create the proxy to our data service exampleService = new Sys.Data.OpenDataServiceProxy("/TestDataService.svc"); // Load the data from the service loadData(); }); function loadData() { // Query the service exampleService.query("/testTables", cbSuccess, cbFailure); } // Success callback for the loadData query function cbSuccess(result, context, operation, userContext) { // Clear the list $("#dbData").children().remove(); // Add all the items from the database/service to the list $.each(result, function (index, row) { $("#dbData").append("<li>" + row.name + ": " + row.value + "</li>"); }); } // Failure callback for the loadData query function cbFailure() { alert("Error contacting service"); } // Insert multiple recors into the database using a single post function insertMultiple() { // Sequence action for inserting data var actionSequence = exampleService.createActionSequence(); // Creating 3 records for (var i = 0; i < 3; i++) { var tmpData = { name: "Sequence" + i, // Column name for the table value: "" + i // Column name for the table }; // Add the freshly create item to the sequence for testTable actionSequence.addInsertAction(tmpData, "/testTables"); } // Execute the sequence actionSequence.execute(cbInsertComplete, "Inserted all three records"); } // Success callback for the bulk insert function cbInsertComplete(results) { // Results returned after successful insert as _result. $.each(results, function (index, row) { $("#dbData").append("<li>" + row._result.name + ": " + row._result.value + "</li>"); }); }
There is not much to the HTML
<p> <ul id="dbData"> <!-- Data gets loaded here --> </ul> </p> <p> <input id="Button2" type="button" value="Reload Data" onclick="loadData();" /> <input id="Button1" type="button" value="Insert Data" onclick="insertMultiple();" /> </p>
As you can see in the image below, when running this through FireBug you can see the first request to load the data which is currently empty, then there is a single post which posts the data and get the results back. We use the results to add the new data to the list.
This code is a very rough guide to executing a sequence of actions using oData services and should not be used as is in a live environment. No error checking is done and no best practices are followed in this post, it is purely the very basics to getting started.
If you would like more detailed information then please do contact me, I will be more than happy to help where I can.
jEditable Dropdown Text
I was stuck for a few minutes trying to figure out how to display the text of the selected item in the dropdown when using jEditable.
My script was querying an odata service (WCF Data Service) which returned a list of values with their ids. I wanted this to be represented in a dropdown with the text displayed instead of the default “selected value”. After struggling for a bit I came up with an idea, since someone else was struggling with the same problem I decided to create a post about it.
Here is my JavaScript code:
var settings = new Array(); // holds settings function loadSettings(selected) { // loads the settings from the database via oData service $.ajax({ type: "GET", url: "dataService.svc/Settings?$orderby=Code%20asc", data: {}, // required for Chrome contentType: "application/json; charset=utf-8", dataType: "json", async: false, // wait until it's done before setting up jEditable success: function (data) { /* Settings * int SettingId (PK) * string Title */ // step through all the results $.each(data.d, function () { // add them to a hashtable settings[this.SettingId] = this.Title; }); // check if a selected one was passed in if (selected != undefined) settings["selected"] = selected; // set the selected one }, error: function (msg, a) { // call error method // TODO: Display correct error here by checking resulting code showError("ERROR: Could not load the settings from the database"); } }); // get the selected one's TEXT from the hashtable var set = settings[val]; // TODO: Make this code better, use .append("<span />"); $('#settings').html('<span id="ddSettings">' + set + '</span>'); // setup field for inline editing $('#ddSettings').editable(function (value, settings) { saveSettings(value, docId, settings); // function to save settings return suffixes[value]; // return the text from the hastable }, { data: settings, // my hashtable cancel: 'Cancel', placeholder: '---', submit: 'Save', tooltip: 'Click to edit', type: 'select', style: 'display: inline;' }); }
And my html:
<html> <head> <script src="/Scripts/jquery-1.4.2.min.js" type="text/javascript"></script> <script src="../Scripts/jquery.jeditable.mini.js" type="text/javascript"></script> <script type="text/javascript"> $(document).ready(function() { loadSettings(); // load the settings dropdown }); // put the JavaScript source from above in here </script> </head> <body> Settings: <div id="settings"></div> </body> </html>
I hope this will help someone get around this problem.
ETag value in header different from object ETag
This is more a note to self which will hopefully help someone.
The last couple of weeks I have been working on a web application that uses WCF Data Services (oData) and jQuery. I started receiving the following error: "The etag value in the request header does not match with the current etag value of the object.". After hours of struggling I found out that it was the oninsert trigger in my database causing the problem. It seems that when you insert the data the trigger changes that and then it is different from what you inserted and it then throws an error instead of returning the record you have just inserted.