With the Azure Mobile Services client SDK, it’s fairly simple to insert an item into a table in Azure. Inserting multiple items can be also done fairly easily, simply inserting one at a time. But there are some scenarios where we want to actually do multiple insertions at once, to minimize the number of networking requests made from the client to the service. In the E2E Test Application that we have for our iOS client, the first thing we do in our query tests is to pre-populate a table with some data (if the table was empty), and since we don’t that to affect the test performance (we use about 250 items), we decided to insert them all at once. This has also been asked in our forums, so I decided to post how I went out to implement it.
Let’s look at the server script. In this example, we’re adding populating our table called ‘iosMovies’, and the data to be inserted is an array in the “movies” property of the received item. The first thing we do is to check whether we actually need to do the insertion (that’s something specific to the scenario of my app; maybe in your case you always want to do the insertion, so that step won’t be necessary). By reading only the first element of the table, we can check whether the table is populated or not (since this script runs in the “insert’ operation, we assume that the table is either fully populated or it needs the items to be inserted). If the data is already there, we respond with an appropriate status. Otherwise, we proceed with the multiple insert operations.
Populating the table with multiple items is done by looping through the items to be inserted, and inserting them one by one (*). Since the insert operation in the table object is asynchronous, we can’t use a simple sequential loop; instead, we’ll insert each element when the callback for the previous one is called. After the last one is inserted, we can then respond to the client with an appropriate status.
So that’s the script for the server. Now for the client code. Let’s look at the flavors we have. First, managed code, where we have the classes for that data:
And to insert multiple movies at once, we create an instance of our type which holds the array, and call insert on that object:
For Objective-C, we don’t need to create the types, so we can use the NSDictionary and NSArray classes directly. The implementation of the ‘getMovies’ method can be found in our GitHub repository.
That’s about it. This is one of the ways we have to prevent multiple networking requests between the client and the mobile service to perform multiple insertions at once.
(*) Bonus info: As I mentioned before, this still does multiple calls, between the mobile service and the database, but since they’re co-located, the latency is small. You also can make really only one call, from the service to the database, by using the mssql object, and creating one insert request for multiple rows at once. You’d need to do the translation between the data types and the SQL expression yourself, and use the “union all” trick to create a temporary table and insert from that table – see an example below. Notice that you’d also need to create the table columns (if they don’t exist yet), since the dynamic schema feature doesn’t work with the mssql object – once you go down to that level, you’re in full control of the database communication.
So which one to use? I really didn’t see much advantage in going full SQL for my scenario. Once a communication between the mobile service and the database is successful, chances are that over the next second or so the subsequent ones will be as well, so doing ~250 insert operations, although not really an atomic transaction, has a very good chance of being one. As usual, it may vary according to the scenario of your application.