In this article, we will learn about how we can perform CRUD operations in Node.js using Microsoft SQL Server.
If you are looking for Node.JS CRUD With MongoDB/Mongoose please click here.
Prerequisites:
- Basic knowledge of Node.js
- Code editor like Visual Studio 2019
If you’re new in Node.js? You must have to follow the link given below to set up a new Node.js App in Visual Studio.
How To Create Node.js App In Visual Studio
Create, Read, Update, and Delete operations in Node.js
First, we create a StudentInfo table with given fields in the SQL Server.
Firstly, right-click the project name in the Solution Explorer and select Add -> New Folder (Database).
Now, right-click the folder name (Database) in the Solution Explorer and select Add -> New File… (dbConnection.js).
Open the dbConnection.js file and add the code in it.
exports.dbConnection = function () {
var dbConfig = {
user: "sa", // SQL Server Login
password: "thecodehubs", // SQL Server Password
server: "DESKTOP-78L71550", // SQL Server Server name
database: "Student" // SQL Server Database name
};
return dbConfig;
};
Now, we have to install a package to connect SQL Server.
Install the mssql package with the NPM:
Right-click on the npm from Solution Explorer -> Install New npm Packages…
Search and install mssql package.
Open the index.js file from the routes folder and add the code in it.
'use strict';
var express = require('express');
var router = express.Router();
var sql = require("mssql");
var dbConfig = require('../Database/dbConnection');
/* Get All Students */
router.get('/', function (req, res) {
sql.connect(dbConfig.dbConnection()).then(() => {
return sql.query("SELECT * FROM StudentInfo;");
}).then(result => {
res.render('index', { studentList: result.recordset }) //res.render() pass a local variable to the view
}).catch(err => {
console.log(err)
})
});
/* Add Student */
router.post('/addStudent', function (req, res) {
sql.connect(dbConfig.dbConnection()).then(() => {
return sql.query("INSERT INTO StudentInfo VALUES('" + req.body.txtName + "', " + req.body.txtAge + ")");
}).then(result => {
res.redirect('back')
}).catch(err => {
console.log(err)
})
});
/* Delete Student */
router.get('/deleteStudent/:ID', function (req, res) {
sql.connect(dbConfig.dbConnection()).then(() => {
return sql.query("DELETE FROM StudentInfo WHERE ID = " + req.params.ID);
}).then(result => {
res.redirect('back')
}).catch(err => {
console.log(err)
})
});
/* Edit Student */
router.get('/editStudent/:ID', function (req, res) {
sql.connect(dbConfig.dbConnection()).then(() => {
return sql.query("SELECT * FROM StudentInfo WHERE ID = " + req.params.ID);
}).then(result => {
res.send(result.recordset[0])
}).catch(err => {
console.log(err)
})
});
/* Update Student */
router.post('/updateStudent', function (req, res) {
sql.connect(dbConfig.dbConnection()).then(() => {
return sql.query("UPDATE StudentInfo SET [Name] = '" + req.body.txtEName + "', Age = " + req.body.txtEAge + " WHERE ID = " + req.body.txtID);
}).then(result => {
res.redirect('back')
}).catch(err => {
console.log(err)
})
});
module.exports = router;
- Get All Students, returns all the records from the StudentInfo table when the home page is loaded successfully.
- Add Student, inserts a new record in the StudentInfo table.
- Delete Student, removes a record from the StudentInfo table.
- Edit Student, returns a single record from the StudentInfo table based on ID, later it can be used to update Student Info.
- Update Student, updates a selected record in the StudentInfo table.
Open the index.html file from the views folder and add the code in it.
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<title></title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-6">
<h3 class="text-center">Add Student</h3>
<!-- Inserts a new record in the StudentInfo table -->
<form action="addStudent" method="post">
<div class="form-group">
<label for="txtName">Name:</label>
<input class="form-control" placeholder="Enter name" id="txtName" name="txtName">
</div>
<div class="form-group">
<label for="txtAge">Age:</label>
<input type="number" class="form-control" placeholder="Enter age" id="txtAge" name="txtAge">
</div>
<button type="submit" class="btn btn-primary">Submit</button>
</form>
</div>
<div class="col-md-6">
<h3 class="text-center">Student List</h3>
<table class="table table-bordered">
<thead class="thead-dark">
<tr>
<th>ID</th>
<th>Name</th>
<th>Age</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<!-- Display list of records from the StudentInfo table -->
<% for (const i in studentList) { %>
<tr>
<td><%= studentList[i].ID %></td>
<td><%= studentList[i].Name %></td>
<td><%= studentList[i].Age %></td>
<td width="200px">
<a href="deleteStudent/<%= studentList[i].ID %>">Delete</a>
  |  
<a onclick="editStudent(<%= studentList[i].ID %>)" href="javascript:void(0)" data-toggle="modal" data-target="#editModal">Edit</a>
</td>
</tr>
<% } %>
</tbody>
</table>
<!-- Edit Student Modal -->
<div class="modal fade" id="editModal">
<div class="modal-dialog modal-dialog-centered">
<div class="modal-content">
<!-- Modal Header -->
<div class="modal-header">
<h4 class="modal-title">Edit Student</h4>
<button type="button" class="close" data-dismiss="modal">×</button>
</div>
<form action="updateStudent" method="post">
<!-- Modal body -->
<div class="modal-body">
<input type="hidden" id="txtID" name="txtID">
<div class="form-group">
<label for="txtEName">Name:</label>
<input class="form-control" placeholder="Enter name" id="txtEName" name="txtEName">
</div>
<div class="form-group">
<label for="txtEAge">Age:</label>
<input type="number" class="form-control" placeholder="Enter age" id="txtEAge" name="txtEAge">
</div>
</div>
<!-- Modal footer -->
<div class="modal-footer">
<button type="submit" class="btn btn-primary">Update</button>
<button type="button" class="btn btn-danger" data-dismiss="modal">Cancel</button>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</div>
<script>
function editStudent(id) {
// AJAX call to select a record in the editable modal popup
$.ajax({
url: 'editStudent/' + id,
type: 'GET',
success: function (res) {
$("#txtID").val(res.ID);
$("#txtEName").val(res.Name);
$("#txtEAge").val(res.Age);
},
error: function (err) {
alert(err);
}
});
}
</script>
</body>
</html>
Here, we used Bootstrap 4 for responsive design.
Output:
You can download code from here. Please give your valuable feedback and if you have any questions or issues about this article, please let me know. Don’t forget to check the comments mentioned in the code given above for better understanding.
Also, check How To Read And Write CSV File In C#
