MongoDB, Express, AngularJS, Node.js abbreviated as MEAN, these are development pack by all Javascript. In this sample we are going to use MSSQL Server instead of MongoDb.
With this application sample we can create a new user, show all the user, modify user data & also can able to delete the user through API build with Express from frontend using AngularJS.
Components:
- MSSql – SQL database
- Express – NodeJS Framework
- Angular – JavaScript frameworks for Frontend
- NodeJS – JavaScript Execution environment (Server)
Dependencies: It’s highly recommended to review those previous post to follow this post.
- Startup NodeJS : http://www.c-sharpcorner.com/article/how-to-start-with-node-js
- Package Manager NodeJS : http://www.c-sharpcorner.com/article/node-js-package-manager-vs2017
- Basic Templating : http://www.c-sharpcorner.com/article/basic-templating-using-node-js-and-express
Let’s create a database in MSSql server for user data storing which is going to operate by our application. After creating the db execute the below query by using that db.
CREATE TABLE [dbo].[User](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NULL,
[Email] [nvarchar](250) NULL,
[Phone] [nvarchar](50) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
This will create a new table named “user”. After that we need to create some stored procedure for SQL operation, copy below script and execute those by pasting it in MSSql query window.
CREATE PROCEDURE [dbo].[GetUsers]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * FROM [dbo].[User]
END
GO
CREATE PROCEDURE [dbo].[GetUserById]
@Id Int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * FROM [dbo].[User] WHERE Id = @Id
END
GO
CREATE PROCEDURE [dbo].[PutUser]
-- Add the parameters for the stored procedure here
@Id INT,
NVarchar(250),
NVarchar(250),
NVarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Update [dbo].[User]
SET [Name] = ,[Email] = ,[Phone] =
WHERE [Id] = @Id
END
GO
CREATE PROCEDURE [dbo].[SetUser]
-- Add the parameters for the stored procedure here
NVarchar(250),
NVarchar(250),
NVarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [dbo].[User]([Name],[Email],[Phone])
VALUES(,,)
END
GO
CREATE PROCEDURE [dbo].[DeleteUser]
-- Add the parameters for the stored procedure here
@Id Int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DELETE FROM [dbo].[User]
WHERE [Id] = @Id
END
--Exec DeleteUser 1
GO
We are done with the database work, let’s start with application development plan. From our previous sample of application we are going to start.
Download it from , then open the application using Visual Studio 2017. We need to install two more packages to fulfill our requirements.
- mssql – Microsoft SQL Server client for Node.js
- body-parser – Node.js body parsing middleware
Server-Side
Installation: Right click on project Go to > Open Command Prompt Here.
Run this command for mssql
npm install mssql
Run this command for body-parser
npm install body-parser
After completing those installation we need to add those to our node server. Here we have added by enabling
var bodyParser = require("body-parser");
then we have use
//Body Parser Middleware
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
operations are going to perform through API’s using Express route. Let’s get started with creating API’s process.
API’s:
Let’s create data service to perform the operations in database. Add a common dbService.js file to serve the requests like below image.
Open the newly added js file then add this line.
var mssql = require('mssql');
This mean we are requiring mssql module by require() function to load the source code in a new function.
After that we are adding the database server connection configuration
var dbConfig = {
user: "sa",
password: "sa",
server: "DESKTOP-80DEJMQ",
database: "dbNode",
pool: {
max: 10,
min: 0,
idleTimeoutMillis: 30000
}
};
As we can see the max pool is 10 & min is 0 & timeout in milliseconds before closing an unused connection which is default.
Get more details on Pool:
var executeQuery = function (sql, res) {
const conn = new mssql.ConnectionPool(dbConfig);
conn.connect().then(function () {
const req = new mssql.Request(conn);
req.query(sql).then(function (data) {
res(data);
}).catch(function (err) {
res(null, err);
})
}).catch(function (err) {
res(null, err);
})
}
Connections
const conn = new mssql.ConnectionPool(dbConfig);
we are creating a sql connection object using connectionpool
Request
const req = new mssql.Request(conn);
then we are executing the request using the global connection pool.
Get more details on connection: https://www.npmjs.com/package/mssql#connections-1
Finally we are exporting the module for another module call.
module.exports = {
executeQuery
}
Let’s create a specific data service using Express router. Create a new JS file then add those code snippet below to newly added file.
var express = require('express');
var router = express.Router();
var dbService = require('../dbService');
//GET API
router.get("/api/user/getAll", function (req, res) {
var query = "GetUsers";
dbService.executeQuery(query, function (data, err) {
if (err) {
throw err;
} else {
res.send(data.recordset);
}
res.end();
});
});
// GET API
router.get("/api/user/getUser/:id", function (req, res) {
var query = "[GetUserById] " + parseInt(req.params.id) + "";
dbService.executeQuery(query, function (data, err) {
if (err) {
throw err;
} else {
res.send(data.recordset);
}
res.end();
});
});
//POST API
router.post("/api/user/setUser", function (req, res) {
var query = "[SetUser] '" + req.body.Name + "', '" + req.body.Email + "', '" + req.body.Phone + "'";
dbService.executeQuery(query, function (data, err) {
if (err) {
throw err;
} else {
res.send(data.recordset);
}
res.end();
});
});
//PUT API
router.put("/api/user/putUser", function (req, res) {
var query = "[PutUser] " + parseInt(req.body.Id) + ", '" + req.body.Name + "','" + req.body.Email + "', '" + req.body.Phone + "'";
dbService.executeQuery(query, function (data, err) {
if (err) {
throw err;
} else {
res.send(data.recordset);
}
res.end();
});
});
//DELETE API
router.delete("/api/user/deleteUser/:id", function (req, res) {
var query = "[DeleteUser] " + parseInt(req.params.id) + "";
dbService.executeQuery(query, function (data, err) {
if (err) {
throw err;
} else {
res.send(data.recordset);
}
res.end();
});
});
module.exports = router;
After finishing all those we need to enable by app.use() function in the node server as middleware.
//Router Middleware
app.use('/', require('./data/userService/userDataService'));
also we are giving access to other user agent by enabling Cross-Origin Resource Sharing (CORS).
//CORS Middleware
app.use(function (req, res, next) {
//Enabling CORS
res.header("Access-Control-Allow-Origin", "*");
res.header("Access-Control-Allow-Methods", "GET,HEAD,OPTIONS,POST,PUT");
res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, contentType,Content-Type, Accept, Authorization");
next();
});
Finally this is the overview of Node Server
'use strict';
//var http = require('http');
var bodyParser = require("body-parser");
var path = require('path');
var express = require('express');
var app = express();
var port = process.env.port || 3000;
//Body Parser Middleware
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
//Serve Static Files
app.use(express.static(path.join(__dirname, 'app')));
app.use(express.static(path.join(__dirname, 'public')));
//Router Middleware
app.use('/', require('./data/userService/userDataService'));
//CORS Middleware
app.use(function (req, res, next) {
//Enabling CORS
res.header("Access-Control-Allow-Origin", "*");
res.header("Access-Control-Allow-Methods", "GET,HEAD,OPTIONS,POST,PUT");
res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, contentType,Content-Type, Accept, Authorization");
next();
});
app.get('/*', function (req, res) {
res.sendFile(path.resolve('layout.html'));
});
app.get('/*', function (req, res) {
res.render('error');
});
var server = app.listen(port, function () {
console.log('Node server is running on port..' + port);
});
Client-Side
We need to create user interface to operate data in database. First we need create folder for specific module like below.
As you can see we have created an angularJs controller with html page.
Html View
This view is going to render while the routing state is changed.
AngularJS Controller
In our angularJS controller we have use $http service to communicate with the API’s. Mehodts that used:
- $http.get: get data
- $http.post: post new data
- $http.put: update existing data
- $http.delete: delete existing data
More about $http service here
templatingApp.controller('UserController', ['$scope', '$http', function ($scope, $http) {
$scope.title = "All User";
$scope.ListUser = null;
$scope.userModel = {};
$scope.userModel.Id = 0;
getallData();
//******=========Get All User=========******
function getallData() {
$http({
method: 'GET',
url: '/api/user/getAll/'
}).then(function (response) {
$scope.ListUser = response.data;
}, function (error) {
console.log(error);
});
};
//******=========Get Single User=========******
$scope.getUser = function (user) {
$http({
method: 'GET',
url: '/api/user/getUser/' + parseInt(user.Id)
}).then(function (response) {
$scope.userModel = response.data[0];
}, function (error) {
console.log(error);
});
};
//******=========Save User=========******
$scope.saveUser = function () {
$http({
method: 'POST',
url: '/api/user/setUser/',
data: $scope.userModel
}).then(function (response) {
showNotif("Data Saved")
$scope.reset();
getallData();
}, function (error) {
console.log(error);
});
};
//******=========Update User=========******
$scope.updateUser = function () {
$http({
method: 'PUT',
url: '/api/user/putUser/',
data: $scope.userModel
}).then(function (response) {
showNotif("Data Updated")
$scope.reset();
getallData();
}, function (error) {
console.log(error);
});
};
//******=========Delete User=========******
$scope.deleteUser = function (user) {
var IsConf = confirm('You are about to delete ' + user.Name + '. Are you sure?');
if (IsConf) {
$http({
method: 'DELETE',
url: '/api/user/deleteUser/' + parseInt(user.Id)
}).then(function (response) {
showNotif("Data Deleted")
$scope.reset();
getallData();
}, function (error) {
console.log(error);
});
}
};
//******=========Clear Form=========******
$scope.reset = function () {
var msg = "Form Cleared";
$scope.userModel = {};
$scope.userModel.Id = 0;
showNotif(msg)
};
}]);
Publishing the App: Let’s go to gulp modification to get publish files finally.
gulp.task('publish', function () {
gulp.src('layout.html')
.pipe(gulp.dest(paths.publish));
gulp.src('package.json')
.pipe(gulp.dest(paths.publish));
gulp.src('server.js')
.pipe(gulp.dest(paths.publish));
gulp.src('app/**/*')
.pipe(gulp.dest(paths.publish + 'app'));
gulp.src('public/**/*')
.pipe(gulp.dest(paths.publish + 'public'));
gulp.src('data/**/*')
.pipe(gulp.dest(paths.publish + 'data'));
gulp.src('bin/**/*')
.pipe(gulp.dest(paths.publish + 'bin'));
});
Go to task explorer in Visual Studio like below image
Run the task, this will copy all our application file to published folder.
Go to Publish folder
Open command prompt here (Shift + Right Mouse) then type “nodemon”. We are starting our application using nodemon. If we have any change in our application nodemon will automatically restart the application.
Now open browser type the URL: http://localhost:3000
OutPut:
Source Code: I’ve uploaded the full source code to download/clone , Hope this will help J
