Writing data to Microsoft SQL Server from web browser using REST API and Node.js

In previous blog post Creating REST API for reading data from Microsoft SQL Server in web browser

we have looked into creating REST API calls for reading data from SQL Server Database. And we also looked into all the steps you should take to do all the installation. In this blogpost, we will do the reverse. Push the data into the SQL Server database using REST API that will result in INSERT statement (or UPDATE statement) on the database.

For this scenario, we will use two files (both available on Github):
1) Config.js (which remained the same and untouched from previous blog post)
2) WriteApp.js

The Content of WriteApp.js should be:

// Read functions
const express = require('express'); 
const app = express();
const sql = require('mssql/msnodesqlv8') //mssql with MS driver for SQL Server
var beautify = require("json-beautify");
 
var env = process.env.NODE_ENV || 'production';
var sqlConfig = require('./config')[env];

const path = require('path')


// View Engine Setup
app.set("views", path.join(__dirname))
app.set("view engine", "ejs")
  
 
// Start server and listen on http://localhost:2908/
var server = app.listen(2908, function() {
  var host = server.address().address
  var port = server.address().port
 
  console.log("app listening at http://%s:%s", host, port)
});
 
const connection = new sql.ConnectionPool(sqlConfig, function(err){
      if (err){
      console.log(err);
      }
    }
)

// Sample with mixed input parameters. Case sensitive and you can use special chars + space breaks
//Use URL: http://localhost:2908/insert/UsersAD?EmloyeeID=222&SamAccountName=tomazt&DisplayName=tomaz tom&Email=tt@gmail.com


 app.get('/insert/UsersAD', function(req,res){

   var EmloyeeID = req.query.EmloyeeID;
   var SamAccountName = req.query.SamAccountName;
   var DisplayName = req.query.DisplayName;
   var Email = req.query.Email;

   console.log("Value for EmployeeID: ",EmloyeeID);
   console.log("Value for SamAccountName: ",SamAccountName);
   console.log("Value for DisplayName: ",DisplayName);
   console.log("Value for Email: ",Email);

  
 connection.connect(function(err) {
 connection.query("INSERT INTO dbo.UsersAD (EmloyeeID, SamAccountName,DisplayName, Email) values ("+Number(EmloyeeID)+",'"+String(SamAccountName)+"', '"+String(DisplayName)+"', '"+String(Email)+"')",function(err,result){

 if(!!err){
 console.log(err);
 res.send('Error in inserting');
 }
 else{
   res.send('Successfully Insertion');
 }});});

 });

This file will send the information to the same SQL table, that we have created in previous blog post.

USE APITest;

CREATE TABLE dbo.UsersAD
( ID INT IDENTITY(1,1) NOT NULL
,EmloyeeID INT 
,SamAccountName VARCHAR(100)
,DisplayName VARCHAR(200)
,Email VARCHAR(100)
)


INSERT INTO dbo.UsersAD (EmloyeeID, SamAccountName, DisplayName, Email)
          SELECT 21,'MichelH','Michel Houell','michelh@account.com'
UNION ALL SELECT 22,'NielT','Niel Ty','NielT@account.com'
UNION ALL SELECT 25,'ImmanuelK','Immanuel Kan','ImmanuelK@account.com'
UNION ALL SELECT 30,'BillG','Bill William Gate','BillG@account.com'

Once you have changed and save both javascript files, you can now run the node application. In the same folder (SQLAPI) run the following command:

node WriteApp.js

And open the URL location in your browser type:
http://localhost:2908/insert/UsersAD?EmloyeeID=222&SamAccountName=tomazt&DisplayName=tomaz tom&Email=tt@tomaztsql

And you should receive a message in your browser:

In the background, a new record should be inserted in the database – emloyeeID = 222

And the result of log should also be printed in the command log:

Besides the INSERT command, you can do any CRUD statements, but we should be cautions about that.

In next blog post, we will look into the ability to do the Predictions with R model and SQL Server using API.

As always, all the code is available on Github – tomaztk/MSSQLServerRestAPI.

‹ Creating REST API for reading data from Microsoft SQL Server in web browser

Using R and Microsoft SQL Server to run prediction model with API call ›

Tagged with: API, API Calls, javascript, Node.JS, SQL, SQL Server
Posted in Uncategorized