General > NodeJs >> NodeJs Views : 15090
Rate This Article :

How to connect SQL Server with Node Js

Introduction:

This article explain about connecting SQL server through NodeJs. In order to access MS SQL database, “mssql” driver needs to install through NPM manager.

Install Driver:

                Install mssql driver using npm command, “npm install mssql” in the command prompt. This will add mssql module folder in node_modules folder in your Node.js application. 

Steps and Verification:

                We have to modify the settings in the Sql Server Configuration Manager.

Step 1: At first we have to change the port numbers in Sql Server Configuration Manager and fallow the steps given below to change the port numbers.

Sql Server Configuration Manager à Sql Server Network Configuration à Protocols for SQLExpress      à TCP/IP(Enabled) à Right-click on TCP/IP and select Properties à IP Address à IP All àTCP Dynamic Ports – 47536 and TCP Port – 1433.

TCPIPSQlServer

Step 2: And now we have to check the Services given below are running or not.

1.       SQL Server Agent

2.       SQL Server Browser

3.       SQL Server

TCPIPSQLServerConfig

Step 3: After installing the driver, we are ready to access MS SQL server database. We will connect to a local SQLExpress database server and fetch all the records from Employee_Details table in msdb database shown in below example.

 

Now, create server.js and write the following code.

 

var sql = require('mssql');

 // config for your database

var config = {

    "user": 'sa',

    "password": '########',

    "server": '#######\\SQLEXPRESS',

    "database": 'master',

    "port": '1433',

    "dialect": "mssql",

    "dialectOptions": {

        "instanceName": "SQLEXPRESS"

    }

};

(async () => {

    try {

        // connect to your database

        let pool = await sql.connect(config);

            // create Request object

            const request = pool.request();

 

            // query to the database and get the records

            request.query('select * from Category where ID = 29', (err, result) => {

                  console.dir(result)

            })

    } catch (err) {

        // ... error checks

            console.log('This is Error');

            console.log(err);

            console.dir(err);

    }

})()

sql.on('error', err => {

    // ... error handler

      console.log('This is Error handler');

})

NodeJsCode

In the above example, we have imported mssql module and called connect() method to connect with our master  database. We have passed config object which includes database information such as userName, password, database server and database name. On successful connection with the database, use sql.request object to execute query to any database table and fetch the records.

Run the above example using node Server.js command and then you can see the result in command prompt as given in below figure.

NodeJsCommand

Basic methods:

1.       resultSet.recorset[i].columnName: To iterate the column data one by one and “i” is the particular row number in the resultSet.

2.       resultSet.recordset.length: It is used to get the length of the resultSet.

3.       resultSet.recordset[i]: It is used to get the particular row data in resultSet.

If you want to insert any values into the query, we need to use the “request.input()” method.

Syntax:

                request.input(‘value’, sql.datatype, column name);

                request.query('select * from category where Name=@value', (err, result) => {

                                                console.dir(result);

});

 

 

Example:

var sql = require('mssql');

 // config for your database

var config = {

    "user": 'sa',

    "password": '########',

    "server": '#######\\SQLEXPRESS',

    "database": 'master',

    "port": '1433',

    "dialect": "mssql",

    "dialectOptions": {

        "instanceName": "SQLEXPRESS"

    }

};

var Name = 'Node.js';

var Status = 'Active';

(async () => {

    try {

        // connect to your database

        let pool = await sql.connect(config);

            // create Request object

            const request = pool.request();

            request.input('value', sql.VarChar, Name);

            request.input('value1', sql.VarChar, Status);

            // query to the database and get the records

            request.query('update Category set Status = @value1 where Name = @value', (err, result) => {

                  console.dir(result)

            })

    } catch (err) {

        // ... error checks

            console.log('This is Error');

            console.log(err);

            console.dir(err);

    }

})()

sql.on('error', err => {

    // ... error handler

      console.log('This is Error handler');

})

SQLServerConnectwithNodeJs

The result will show as how many rows affected as shown in figure below.

NodeExecutionCmd



About Author
Madhavi Seethi
Total Posts 3
-
Comment this article
Name*
Email Address* (Will not be shown on this website.)
Comments*
Enter Image Text*
   
View All Comments
sivasankari
clear explanation
  Privacy   Terms Of Use   Contact Us
© 2016 Developerin.Net. All rights reserved.
Trademarks and Article Images mentioned in this site may belongs to Microsoft and other respective trademark owners.
Articles, Tutorials and all other content offered here is for educational purpose only and its author copyrights.