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);
    }
});

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

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);
                           }             
                    }               
              }
        }
    });
} 

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.