Click here to Skip to main content
13,663,349 members
Click here to Skip to main content
Add your own
alternative version

Stats

4.7K views
113 downloads
17 bookmarked
Posted 20 Feb 2018
Licenced CPOL

MEAN Stack

, 20 Feb 2018
Rate this:
Please Sign up or sign in to vote.
MongoDB, Express, AngularJS, Node.js abbreviated as MEAN These development tools all use 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
  • Node.js - JavaScript Execution environment (Server)

Dependencies:

It’s highly recommended to review those previous post to follow this post.

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,
	@Name NVarchar(250),
	@Email NVarchar(250),
	@Phone 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] = @Name,[Email] = @Email,[Phone] = @Phone
	WHERE [Id] = @Id
END
GO
CREATE PROCEDURE [dbo].[SetUser]
	-- Add the parameters for the stored procedure here
	@Name NVarchar(250),
	@Email NVarchar(250),
	@Phone 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(@Name,@Email,@Phone)

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 GitHub, 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

Package 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 it to our application.

//Body Parser Middleware
app.use(bodyParser.json()
app.use(bodyParser.urlencoded({ extended: true }));

In this sample 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@12345",
    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: https://github.com/coopernurse/node-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.
<div class="container-fluid">
    <div class="row">
        <div class="col-sm-4">
            <h3>Add New User</h3>

            <form name="frmUser" novalidate>
                <input type="hidden" ng-model="userModel.Id" name="uid" />

                <div class="form-group">
                    <label for="uname" class="control-label">User Name</label>
                    <input type="text" class="form-control" ng-model="userModel.Name" name="uname" placeholder="" required />
                    <span class="error" ng-show="(frmUser.$dirty||submitted) && frmUser.uname.$error.required">Customer name is Required</span>

                </div>
                <div class="form-group">
                    <label for="email" class="control-label">E-mail</label>
                    <input type="email" class="form-control" ng-model="userModel.Email" name="email" placeholder="" required />
                    <span class="error" ng-show="(frmUser.$dirty ||submitted) && frmUser.email.$error.required">EmailId is Required!</span>
                    <span class="error" ng-show="(frmUser.$dirty ||submitted) && frmUser.$error.email">Invalid EmailId!</span>

                </div>
                <div class="form-group">
                    <label for="phone" class="control-label">Phone</label>
                    <input type="text" class="form-control" ng-model="userModel.Phone" name="phone" placeholder="" />

                </div>
                <div class="form-group">
                    <button type="submit" class="btn btn-danger" ng-click="reset()">Reset</button>
                    <button type="submit" class="btn btn-primary" ng-click="saveUser()" ng-disabled="frmUser.$invalid"
                            ng-If="userModel.Id == 0">
                        Create
                    </button>
                    <button type="submit" class="btn btn-success" ng-click="updateUser()" ng-disabled="frmUser.$invalid"
                            ng-If="userModel.Id > 0">
                        Update
                    </button>
                </div>
            </form>
            <span class="warning">{{resmessage}}</span>
        </div>
        <div class="col-sm-8">
            <h3>All User</h3>
            <table style="width:100%" class="table table-striped">
                <tr>
                    <th>Sr.</th>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Phone</th>
                    <th>Option</th>
                </tr>
                <tr ng-repeat="item in ListUser">
                    <td>{{ $index+1 }}</td>
                    <td>{{ item.Name }}</td>
                    <td>{{ item.Email }}</td>
                    <td>{{ item.Phone }}</td>
                    <td>
                        <a href="#" ng-click="getUser(item)" title="Edit Record" class="btn btn-primary btn-xs pull-right">
                            Edit
                        </a>
                        <a href="#" ng-click="deleteUser(item)" title="Delete Record" class="btn btn-danger btn-xs pull-right">
                            Delete
                        </a>
                    </td>
                </tr>
            </table>
        </div>
    </div>
</div>

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:

Hope this will help :)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shashangka Shekhar
Architect OnAir International Ltd
Bangladesh Bangladesh
Hi, I am Shashangka Shekhar,

Working with Microsoft Technologies. Since March 2011, it was my first step to working with Microsoft Technologies, achieved bachelor’s degree on Computer Science from State University of Bangladesh(Dhaka). Have 6+ years of professional experience, currently working as Software Architect at OnAir International Ltd.

I believe in desire of learning & also love to be a part of .Net Community by sharing knowledge’s.

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 Pin
Shamim Uddin10-Mar-18 0:13
professionalShamim Uddin10-Mar-18 0:13 
GeneralRe: My vote of 5 Pin
Shashangka Shekhar11-Mar-18 15:53
professionalShashangka Shekhar11-Mar-18 15:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180810.1 | Last Updated 21 Feb 2018
Article Copyright 2018 by Shashangka Shekhar
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid