app.js

//Initiallising node modules
var express = require("express");
var bodyParser = require("body-parser");
var sql = require("mssql");
var app = express();

// Body Parser Middleware
app.use(bodyParser.json());

//CORS Middleware
app.use(function (req, res, next) {
    //Enabling CORS
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Methods", "GET, POST, OPTIONS, PUT, PATCH, DELETE");
    res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, contentType,Content-Type, Accept, Authorization");
    next();
});

//Setting up server
 var server = app.listen(process.env.PORT || 8080, function () {
    var port = server.address().port;
    console.log("App now running on port", port);
 });

//Initiallising connection string
var dbConfig = {
    user:  'sa',
    password: 'wpcgfrbl1973',
    server: 'localhost',
    database:'testDB'
};

var  executeQuery = function(res, query){
    executeQuery(res, query,1);
}

//Function to connect to database and execute query
var  executeQuery = function(res, query,type){
     sql.connect(dbConfig, function (err) {
         if (err) {
                     console.log("Error while connecting database :- " + err);
                     res.send(err);
                  }
                  else {
                         // create Request object
                         var request = new sql.Request();
                         // query to the database
                         request.query(query, function (err, recordset) {
                           if (err) {
                                      console.log("Error while querying database :- " + err);
                                     }
                                     else
                                     {
                                         if (type == 2) {
                                            if (recordset.recordset.length) {
                                                res.send(recordset.recordset[0]);
                                            } else {
                                                res.status(404).send({"error":"notfound"});
                                            }
                                         } else {
                                            res.send(recordset.recordset);
                                        }
                                     }
                               });
                       }
      });
}



//UPDATE ADD API
app.post("/api/roomlog", function(req , res){
    var query = "INSERT INTO [room_log] (state) ";
    query += "VALUES (" + req.body.state + ")";
    query += " Select * from [room_log] WHERE id=@@IDENTITY ";

    executeQuery (res, query,2);
});



//TYPES GET API
app.get("/api/roomlog", function(req , res){
    var query = "select TOP 1 *  from [room_log] order by id desc";
    executeQuery (res, query);
});