Exposing a Stored Procedure in a .NET Backend Mobile Service

By default, Azure Mobile Services uses an Azure SQL Database for data storage. We have a new topic that explores how to call stored procedures in SQL Database from a JavaScript (node.js) backend mobile service: Calling SQL stored procedures with a JavaScript back end.

After we published this new topic, it quickly generated a question in the topic’s Disqus thread about how to expose a stored procedure when you are using a .NET backend. (I consider this is a reasonable question since we try to maintain parallel content between JavaScript and .NET backend versions). The good news is that since .NET backend mobile service is really just an ASP.NET Web API service running in Azure, you can take advantage of all of the goodness of the Entity Framework, which is how Web API accesses the data source (in this case SQL Database).

Create a Basic Stored Procedure

While it’s great to be able to say “just expose a stored procedure using Entity Framework like you would in Web API,” it’s even better to show an example (I was not actually able to find all that many examples). Consider the very simple TSQL function that we use in the Call a custom API from the client (.NET backend) topic.

 // Create a SQL statement that sets all uncompleted items
// to complete and execute the statement asynchronously.
var sql = @"UPDATE TodoItems SET Complete = 1 " +    
    @"WHERE Complete = 0; SELECT @@ROWCOUNT as count";

We can move this TSQL code, which executes in the database, from the Post method in our mobile service into the database as a stored procedure by executing the following query against the database in the portal-based SQL Database management tool:

 CREATE PROCEDURE todolist.completeAll
AS 
UPDATE todolist.TodoItems SET Complete = 1 
WHERE Complete = 0; 
SELECT @@ROWCOUNT as count;
GO

(Note that I’m using the schema name of our fictional todolist mobile service.)

Call the Stored Procedure from the .NET backend Mobile Service

Since the stored procedure is now in the database, we can revise the Post method in the existing CompleteAllController to use the SqlQuery method to instead call the sproc directly:

 // POST api/completeall        
public int Post()
{
    using (todolistContext context = new todolistContext())
    {
        // Get the database from the context.
        var database = context.Database;

        // Create a SQL statement that sets all uncompleted items
        // to complete and execute the statement asynchronously.               
        var result = database.SqlQuery<int>("todolist.completeAll")
                             .FirstOrDefault();

        // Log the result.
        Services.Log.Info(string.Format("{0} items set to 'complete'.",
            result.ToString()));

        return result;
    }
}

Thanks to Luc Bos for the tip on how to call a stored procedure from an EF Code First DbContext.

Calling a Stored Procedure with Parameters

If our procedure used parameters (which is something that we really need to add to improve the existing custom API topic), the SqlQuery method call might look more like this:

 var myParam = new System.Data.SqlClient.SqlParameter("@staleOnly",true);
var result = database.SqlQuery<int>("todolist.completeAll @staleOnly",  
    myParam).FirstOrDefault();

In this case, the stored procedure takes a single @staleOnly parameter that when true only marks items complete when they are older than a certain amount (let’s say).

For a comprehensive blog post on using the Mobile Services .NET client library to call custom APIs, including passing parameters, see Carlos’ blog post: Custom API in Azure Mobile Services – Client SDKs.

Hope that helps.

 

Thanks,

Glenn Gailey