Jeff Sanders Technical Blog

I am a Microsoft employee that has worked on all aspects of the Web Stack for a long time. I hope these blogs are useful to you! Use this information at your own risk.

<< Go Back

Execute Transactions From Azure Mobile App Api Node Js

- 21 Sep 2016

Using the Azure Mobile App API interface (like: https://mymobileapp.azurewebsites.net/api/doTransActionStuff ) with a node.js backend, I found documentation around executing SQL Transactions a little light.  So… Here are some options you could use:

1. Call a stored procedure that does everything for you

This would be the way I would do it!  Stick all of the logic in a stored procedure and make the Azure SQL Database do the work for you.  This also is the best performing solution since there is only one call to the Database and the Database is doing the work for me.  This assumes you can write a stored procedure of course!  Simply call the stored procedure with arguments from within the API using something like this:

// an example of executing a stored procedure that internally is using transactions
     post: (request, response, next) => {
             var query = {
                     sql: 'EXEC completeAllStoredProcedure @completed',
                 parameters: [
                     { name: 'completed', value: request.query.completed }
             ]
         };
         request.azureMobile.data.execute(query)
             .then(function (results) {
                     response.json(results);
                 });
     }

Sample: https://github.com/Azure/azure-mobile-apps-node/blob/master/samples/custom-api-sql-stmt/api/completeall.js
Note: The parameters are passed in via the client.  The raw HTTP would look something like this –> http://mymobileapp.azurewebsites.net/api/doTransActionStuff?completed=true.  For .NET Clients you would use InvokeApiAsync https://msdn.microsoft.com/en-us/library/azure/dn268343(v=azure.10).aspx
iOS clients: InvokeApi https://azure.microsoft.com/en-us/documentation/articles/app-service-mobile-ios-how-to-use-client-library/
 

2. Call execute and build your statements into a query

Yet another way that would be fairly efficient is to build up the SQL statement as a string and embedded any passed in parameters (like above) if necessary and call execute

"get": function (req, res, next) {
    var query = {
        sql: "SET XACT_ABORT ON \
                    BEGIN TRANSACTION \
                    <”normal” statement that works> \
                    <”invalid” statement that goes against a constraint I created> \
                    COMMIT TRANSACTION \
                    SET XACT_ABORT OFF",
        parameters: []
    };
    req.azureMobile.data.execute(query);
}

 

3. User the built in Driver Transaction functionality

I would use this if I am already familiar with driver (like tedious) and want all my code to be in the Mobile App.  Not the most efficient and results in several calls to the Database:

module.exports = {
    "get": function (req, res, next) {
        var globalErrMessage = "";
        function handleErr(source, err) {
            var theError = source + ": " + err;
            console.error(theError);
            globalErrMessage = globalErrMessage + theError;
            globalSuccess = false;
        }
        var sql = require('mssql');
        var config = {
            driver: 'tedious', // use tedious driver
            server: 'jsandersmobileappdbserver.database.windows.net',
            user: 'username@jsandersmobileappdbserver',
            password: 'password',
            port: '1433',
            options: {
                encrypt: true,
                database: 'jsandersmobileDb',
                debug: {
                    packet: true,
                    data: true,
                    payload: true,
                    token: false,
                    log: true
                }
            }
        };
        var rolledBack = false;
        var globalSuccess = true;
        console.info("starting");
        sql.connect(config, function (err) {
            if (err) {
                handleErr("sql.connect", err);
            }
            else {
                // ... error checks - todo
                console.info("OK:connected");
                var transaction = new sql.Transaction(/* [connection] */);
                transaction.begin(function (err) {
                    // ... error checks
                    if (err) {
                        handleErr("transaction.begin", err);
                    }
                    //Rollback event
                    transaction.on('rollback', function (aborted) {
                        // emited with aborted === true
                        console.info("Event:transaction.on('rollback')");
                        rolledBack = true;
                    });
                    var request = new sql.Request(transaction);
                    request.query('insert into mytable (bitcolumn) values (2)', function (err, recordset) {
                        // insert should fail because of invalid value
                        if (err) {
                            handleErr("ERR:request.query", err);
                            // if not rolled back then rollback this transaction
                            if (!rolledBack) {
                                transaction.rollback(function (err) {
                                    if (err) {
                                        handleErr("ERR:transaction.rollback", err);
                                    }
                                    else {
                                        console.info("OK:transaction.rollback");
                                    }
                                });
                            }
                        } else {
                            transaction.commit(function (err) {
                                // ... error checks
                                if (err) {
                                    console.log("ERR:transaction.commit");
                                    console.log(err);
                                }
                                else {
                                    console.log("OK:transaction.commit");
                                }
                            });
                        }
                    });
                });
            }
        });
        if (rolledBack) {
            res.json("Rolled Back");
        }
        else if (globalSuccess) {
            res.json("success");
        }
        else {
            res.json("error");
        }
    }
};

 
I hope this is useful in getting you jumpstarted!
Drop me a note if you find this useful!

<< Go Back