Node API With SQL Server

In this blog, we will try to learn Node API with SQL Server step by step.

Introduction

Node js is an open source server-side runtime environment. It provides asynchronous programming which means no blocking of a task. Due to this, it is faster than other frameworks. Node js is cross-platform that runs on Windows, Linux, and Mac. So, we can create a server-side application using JavaScript which is very fast and lightweight. Let us start building an application with node.

 

Description

Before we begin with node, we have to make sure that we have installed the node.js on our system. So, follow the following link and download node setup according to your operating system. Click here to download node setup After installing it successfully, go to your Start window and search for (Node.js command prompt). Open it and let's start with this.

 

Step 1

Write the following commands in your Node.js command prompt.

F:\Projects>mkdir NodeApiWithSql
F:\Projects>cd NodeApiWithSql
F:\Projects\NodeApiWithSql>npm init

It will create a file in your NodeApiWithSql folder with the name package.json with some basic information regarding your application.



You can see in the above image that it has created a file with the name, package.json, with the information related to your application. Now, let's see what we have in the package.json file.



You can see that in main, "index.js" is written in the package.json file. So, let's create a JS file with the name index in your folder. And let's install some required packages we need in our application with Node js command prompt.

Write the following commands in command prompt.



We have installed three packages here.

• npm install express - is used for minimalist web framework for node.
• npm install body-parser - body-parser incoming request bodies in a middleware.
• npm install mssql - is used for using SQL Server database.

You can see more description regarding above packages at www.npmjs.com/package/

 

Now let's write following code in our index.js file -:

var _expressPackage = require("express");
var _bodyParserPackage = require("body-parser");
var _sqlPackage = require("mssql");
//Initilize app with express web framework
var app = _expressPackage();
//To parse result in json format
app.use(_bodyParserPackage.json());

//Here we will enable CORS, so that we can access api on cross domain.
app.use(function (req, res, next) {
    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();
});

//Lets set up our local server now.
var server = app.listen(process.env.PORT || 4000, function () {
    var port = server.address().port;
    console.log("App now running on port", port);
});

//Set up your sql connection string, i am using here my own, you have to replace it with your own.
var dbConfig = {
    user: "sa",
    password: "@password1",
    server: "LAPTOP-7G1PNTT6",
    database: "SchoolManagment"
};

//Function to connect to database and execute query
var QueryToExecuteInDatabase = function (response, strQuery) {
    //close sql connection before creating an connection otherwise you will get an error if connection already exists.
    _sqlPackage.close();
    //Now connect your sql connection
    _sqlPackage.connect(dbConfig, function (error) {
        if (error) {
            console.log("Error while connecting to database :- " + error);
            response.send(error);
        }
        else {
            //let's create a request for sql object
            var request = new _sqlPackage.Request();
            //Query to run in our database
            request.query(strQuery, function (error, responseResult) {
                if (error) {
                    console.log("Error while connecting to database:- " + error);
                    response.send(error);
                }
                else {
                    response.send(responseResult);
                }
            });
        }
    });
 }

//GET API
app.get("/StudentList", function(_req ,_res){
    var Sqlquery = "select * from tbl_studentdetails";
    QueryToExecuteInDatabase(_res, Sqlquery);
});

 Now your get api is ready, now let's run the code and if everything is fine code will be run successfully. So go to your node command prompt and write following command -:

 

 So you can see that our code is running successfully on 4000 port, now go to browser and write "localhost:4000/StudentList" , you will see the result like below image.

 

So we created an api in node with sql server and run successfully on our local server. Now lets call a stored procedure from node. I have created following stored procedure in sql server.

ALTER PROCEDURE [dbo].[sp_CheckLogin]
@Username varchar(50),
@Password varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SELECT Userid,Username,IsActive from Membership_User where Username=@Username and Password=@Password and IsActive=1
END

Now let's call this stored procedure with node, write following code -:

var request = new _sqlPackage.Request();
   //calling a stored procedure
                request.input('Username', _sqlPackage.VarChar(50), 'admin');
                request.input('Password', _sqlPackage.VarChar(50), 'admin@123');
                request.execute('sp_CheckLogin', function (err, recordsets, returnValue) {
                    response.send(recordsets);
                });

I have just write above code to call stored procedure from node, you just have to implement the way we implemented above get api. And when you will run this code you will see result like this.

 

So that's it for now, we will do something else in further blog, till enjoy coding.