Calling Stored Procedures from Windows Azure Mobile Services

I was surprised, yet delighted, that Windows Azure Mobile Services uses a SQL database.   Schema-less table storage has its place and is the right solution at times, but for most data driven applications, I’d argue otherwise. In my last post, I wrote about sending notifications by writing the payload explicitly from a Windows Azure Mobile Service.   In short, this allows us to include multiple tiles in the payload, accommodating users of both wide and square tiles.   In my application, I want to execute a query to find push notification channels that match some criteria.  If we look at the Windows Azure Mobile Services script reference, the mssql object allows us to query the database using T-SQL and parameters, such as: mssql.query('select top 1 * from statusupdates', { success: function(results) { console.log(results); } }); .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }In my case, the query is a bit more complicated.  I want to join another table and use a function to do some geospatial calculations – while I could do this with inline SQL like in the above example, it’s not very maintainable or testable.  Fortunately, calling a stored procedure is quite easy. Consider the following example:  every time the user logs in, the Channel URI is updated.  What I’d like to do is find out how many new locations (called PointsOfInterest) have been modified since the last time the user has logged in.  To do that, I have a stored procedure like so: create procedure [darkskies].[NewLocationsForChannel] ( @channelUri as nvarchar(512) = null ) as select c.ChannelUri, count(1) as NumNewLocations from darkskies.Channel c inner join darkskies.PointOfInterest p on c.UserId = p.UserId where p.LastUpdated > c.LastUpdated and c.ChannelUri = @channelUri group by c.ChannelUri .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Writing something like that inline to the mssql object would be painful.   As a stored procedure, it’s much easier to test and encapsulate.  In my WAMS script, I’ll call that procedure and send down a badge update: function updateBadge(channelUri) { var params = [channelUri]; var sql = "exec darkskies.NewLocationsForChannel ?"; mssql.query(sql, params, { success: function(results) { if (results.length > 0) { for (var i=0; i< results.length; i++) { if (results[i].ChannelUri !== null && results[i].ChannelUri.length > 0) { push.wns.sendBadge(results[i].ChannelUri, results[i].NumNewLocations); } } } } }); } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }This section of code only updates the badge of the Windows 8 Live Tile, but it works out nicely with tile queuing: Note: this app is live in the Windows 8 Store, however, at the time of this writing, these features have not yet been released.  In the next few posts, we’ll look at the notifications a bit more, including how to pull off some geospatial stuff in WAMS.

Best Practice for Sending Windows 8 Tiles from Mobile Services

Those that know me know I am not a fan of javascript, in pretty much all of its forms (including node.js), however, I’m really digging Windows Azure Mobile Services (WAMS).  WAMS allows you to easily provide a back end to applications for storing data, authenticating users, and supporting notifications on not just Windows and Windows Phone, but also iOS with future plans of supporting Android soon. Now, I mention javascript because WAMS provides a slick node-like powered data service that makes it really easy to store data in the cloud.  The ToDoList example exercise illustrates the ease at storing user data in the cloud and hooking it up with authentication and notification support.   The nice thing about the authentication is that it’s easily integrated into the backend: But, more on this later.  Right now, I want to deal with notifications in WAMS.  In WAMS, you have the opportunity to right custom server-side javascript to do things like send notifications on insert/update/delete/read access: In my case, I want to send a tile update if the new data meets some criteria.   Let’s start all the way down the code and work our way out, starting with the notification piece.    One page you MUST have bookmarked is the tile template catalog on MSDN.   This page defines the XML syntax for all possible tiles your tile can have, including both small/square tiles, and large/wide tiles.    All of these have a defined schema, such as this for TileSquarePeekImageAndText04: <tile> <visual> <binding template="TileSquarePeekImageAndText04"> <image id="1" src="image1" alt="alt text"/> <text id="1">Text Field 1</text> </binding> </visual> </tile> .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Which produces a tile that “peeks”, such as this (which flips between the top half and bottom half): Yes, it’s easy to laugh at the magic “04” in the template title.  I like to joke that my personal favorite is TileWideSmallImageAndText03.   But, there variety is crucial to creating the ideal app experience and that depends on how you want to display the data -- and that requires knowing the XML template. Now, in WAMS, there’s a great tutorial on sending some basic notifications.   In that walkthrough, a notification is sent via the server-side javascript like so: push.wns.sendTileSquareText02(“https://bn1.notify.windows.com?[snip]”, { text1: “some text”, text2: “more text”}); .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Now, at first glance, this is very nice because WAMS will write the XML for you.  However, you still must know what data the template requires.  Does it need an image?  One text line?  Two?   You get the point.  Unsurprisingly, calling that method will generate XML like: <tile> <visual> <binding template="TileSquareText02"> <text id="1">some text</text> <text id="2">more text</text> </binding> </visual> </tile> You can learn more about this in the WAMS script reference.  Another must-have bookmark.  However, I recommend you don’t use these at all, and instead write the XML payload directly.   This is for a few reasons, but primarily, it’s for control – and, really, you have to know the fields required anyway and you’ll still have the tile catalog page open for reference. In looking at the mpns (Microsoft Push Notification Service) library a bit closer (awesome job by the guys, by the way) up on git, it has this method: var raw = new mpns.rawNotification('My Raw Payload', options); .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } When developing my app, I realized I had no idea what tile size the user has.   Some may opt to use a wide tile, others a small tile.  I needed different tiles to support both.   I didn’t like sending two notifications (seems wasteful, doesn’t it?) and to do this efficiently, it’s easier to just create the payload explicitly that includes all tiles.  For example, this includes two completely different tiles: var payload = "<tile><visual><binding template='TileWideImageAndText02'>" + "<image id='1' src='" + xmlEscape(bigImage) + "' alt='map'/>" + "<text id='1'>" + text1 + "</text>" + "<text id='2'>" + text2 + "</text>" + "</binding>" + "<binding template='TileSquareImage' branding='none'>" + "<image id='1' src='" + xmlEscape(smallImage) + "' alt='map'/></binding></visual></tile>"; push.wns.send(channelUri, payload, 'wns/tile', { client_id: 'ms-app://<snip>', client_secret: 'i will never tell', headers: { 'X-WNS-Tag' : 'MainTile' } }... .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Sure, it doesn’t look as clean and (gasp!) we have to do string concatenation.   But, it’s only a couple of minutes more work and just more flexible.  Like I said: either way, you need to know the template.   In my case, I’m sending both notifications in one payload.  The first is TileWideImageAndText02, which produces a nice image with the text on the bottom describing the image.  If the user has a small tile, it will use TileSquareImage, which basically just forgoes the text and just displays the image.  After trying a few, I settled on this combination as the best user experience.  This is an easy way, with minimal effort, to support both wide and narrow tiles. As an aside, I recommend setting the tag (X-WNS-Tag) header, particularly if your app cycles tiles and you want to replace a specific tile.  Also, it’s a good ideal to XML escape all data, which I’m doing with the long image URLs … and this, I believe, is taken right from the mpns library: var xmlEscape = function (text) { return text.replace(/&/g, '&amp;') .replace(/</g, '&lt;') .replace(/>/g, '&gt;') .replace(/"/g, '&quot;'); } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } If you don’t escape the data and have some illegal chars in there as a result, the notification gets sent correctly (that is, accepted), but gets ignored by the client. Now that I’ve got the basic code to send a tile, I needed to filter some data and run a query and sort users by distance.  Sound like fun?  I’ll write about that next…

My Apps

Dark Skies Astrophotography Journal Vol 1 Explore The Moon
Mars Explorer Moons of Jupiter Messier Object Explorer
Brew Finder Earthquake Explorer Venus Explorer  

My Worldmap

Month List