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 [email protected]
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','[email protected]'
UNION ALL SELECT 22,'NielT','Niel Ty','[email protected]'
UNION ALL SELECT 25,'ImmanuelK','Immanuel Kan','[email protected]'
UNION ALL SELECT 30,'BillG','Bill William Gate','[email protected]'
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.
]]>REST API (or RESTful API) is a application programming interface (abbreviated as API) that complies with REST architectural style and is created to allow communication, information flow and interaction with REST (Representational State Transfare) web services. API is used as an interface for communication between two or more programs (or machines) and provides content required from one side to another side (known as consumer and producer; call/request and response).
Getting data from and to database using API is great for building integration with application and retrieving to client all the information in JSON format only once, instead of every time an action occurs on frontend. Yes there are multiple ways to push data to frontend, it can be entity framework, it can be Dapper, it can be T-SQL with procedures layer, it can be REST API, and many many others.
You can think of an API as a mediator between the users or clients and the resources or web services they want to get. It’s also a way for an organization to share resources and information while maintaining security, control, and authentication—determining who gets access to what.
Another advantage of an API is that you don’t have to know the specifics of caching—how your resource is retrieved or where it comes from.
In General, what you will need to do is:
1. MSSQL Server installed
2. MSSQL Server configuration (Network pipes, Security, user, sp_configure)
3. Node Js. installed
4. Install npm packages
5. Setup configuration and JS script
First we will create a new API database on SQL Server. After that, we will stage a sample dataset:
USE APITest;
GO
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','[email protected]'
UNION ALL SELECT 22,'NielT','Niel Ty','[email protected]'
UNION ALL SELECT 25,'ImmanuelK','Immanuel Kan','[email protected]'
UNION ALL SELECT 30,'BillG','Bill William Gate','[email protected]'
Make sure to also do the following important steps on your SQL Server:
Set up and configure the security. In my case, I am creating a single user (Windows or SQL):
USE APItest;
GO
CREATE LOGIN tk123
WITH PASSWORD = 'tk123';
GO
-- Creates a database user for the login created above.
CREATE USER tk123 FOR LOGIN tk123;
GO
CREATE USER [tk123] FROM LOGIN [sntk\tk123];
GO
The next step, what do you to is go to SQL Server Configuration Manager and in Network configuration for your SQL Server (in my case SQL Server is “MSSQLSERVER2019”) and enable TCP/IP protocol.
In the Services, check for SQL Server Browser service is up and running.
After TCP/IP protocol changes, we need to restart SQL Server service in order for changes to be committed. On SQL Server side, this is what needs to be done.
In next couple of steps, we will install Node.js. Go to their website (https://nodejs.org/en/) and download the nodejs engine. I am using LTS (Long Time Support) version and works flawlessly. After installation is completed, there some some of the steps needed to be created. There will be PATH added to your system environment variable and making Node.js run from any user specified directory.
We will need to create a directory to hold your application, and make that your working directory. So anywhere on your client, create a directory, where running user will have access. Run Node.js command prompt:
Go to your D:\ drive (on windows) or preferred location and run:
mkdir SQLAPI
cd SQLAPI
After this, we will need to initialize the node.js application for this directory. And run:
npm init
and install following node packages:
npm install --save express
npm install --save mssql
npm install --save msnodesqlv8
npm install --save json-beautify
In this folder SQLAPI, we will now create a *.js (JavaScript) files that will be node.js instructions. So we will create two files, one holding the configuration and connection string to SQL Server and the other one will be application that will retrieve the content.
We will name these two files as:
1) config.js (great article by Phil Factor on Redgate Simple Talk on diving prod/dev environments)
2) ReadApp.js
The content of the config.js will be the following. Make sure you change the connection string and set the user and password or leave to trusted_connection.
var config = {
production: {
driver: 'msnodesqlv8',
connectionString: 'Driver=SQL Server Native Client 11.0;Server=sntk\\mssqlserver2019;Database=APITest;Trusted_Connection=yes'
}
};
module.exports = config;
The second file will be called ReadApp.js and will have the previous file reference and all the app.get node.js functions:
// 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];
// 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);
}
}
)
// Input as Integer
app.get('/UsersAD/EmloyeeID/:empID/', function(req, res) {
connection.connect().then(pool => {
var conn=pool.request()
var forInteger = /\b\d+\b/i;
if (forInteger.test(req.params.empID)) {
conn.input('input_parameter', sql.Int, req.params.empID)}
else {conn.input('input_parameter', sql.Int, 32116)}
var string = 'SELECT * FROM dbo.UsersAD WHERE EmloyeeID = @input_parameter'
return conn.query(string)
}).then(result => {
let rows = result.recordset
res.setHeader('Access-Control-Allow-Origin', '*')
// Result to URL
res.status(200).type('JSON').send(beautify(rows, null, 2, 100));
// result to log
console.log(beautify(rows, null, 2, 100));
connection.close();
}).catch(err => {
console.log(err);
res.status(500).send({
message: err
})
connection.close();
});
});
// input as VarChar
app.get('/UsersAD/SamAccountName/:SamAccountName/', function(req, res) {
connection.connect().then(pool => {
var conn=pool.request()
conn.input('input_parameter', sql.VarChar, req.params.SamAccountName)
var string = 'SELECT * FROM dbo.UsersAD WHERE SamAccountName = @input_parameter'
return conn.query(string)
}).then(result => {
let rows = result.recordset
res.setHeader('Access-Control-Allow-Origin', '*')
res.status(200).type('JSON').send(beautify(rows, null, 2, 100));
// result to log
console.log(beautify(rows, null, 2, 100));
connection.close();
}).catch(err => {
console.log(err);
res.status(500).send({
message: err
})
connection.close();
});
});
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 ReadApp.js
And open the URL location in your browser type:
localhost:2908/UsersAD/EmloyeeID/22
localhost:2908/UsersAD/SamAccountName/BillG
And in the CMD log the output would be:
In next blog post, we will look into the ability to write to database using API.
As always, all the code is available on Github – tomaztk/MSSQLServerRestAPI.
]]>