How to connect Sql server database by NodeJS

Tarun Kumar Chatterjee
 
Net – Technology Specialist
September 5, 2016
 
Rate this article
 
Views
10534

In this article let me explain you about the Node JS utilities and one basic implementation on to connect with SQL Server database.

Node.js is an open source, cross-platform runtime environment for server-side and networking applications. Node.js applications are written in JavaScript and can be run within the Node.js runtime on Microsoft Windows, Linux etc.

Advantages

1. Node.js is open source, so it’s free to use and no need to pay for license.

2. It uses JavaScript as a programming language for both front-end and back-end which increase programmer productivity and code reusability.

3. You can scale your Node.js application by using two ways – Horizontal Scaling and Vertical Scaling, which helps you to improve your application performance.

a. In Horizontal scaling you can add more nodes to your existing system.

b. In Vertical scaling you can add more resources to a single node.

4. It provides better performance, since Node.js I/O operations are non-blocking. Also, it uses V8 JavaScript engine to execute JavaScript code. V8 engine compiles the JS code directly into machine code which makes it fast.

5. Node.js is based on JavaScript which can be executed on client side as well as server side. Also, it supports exchange of data using JSON which is easily consumed by JavaScript. This makes it light weight as compared to other frameworks.

6. Node.js is open source. Hence you can extend it as per your need.

7. It supports unit testing out of box. You can use any JS unit testing frameworks like Jasmin to test your Node.js code.

8. Node.js supports caching of modules. Hence, when a Node.js modules is requested first time, it is cached into the application memory. So next calls for loading the same module may not cause the module code to be executed again.

9. Using Node.js you can also develop Restful services API easily.

10. Node.js has some built-in API which helps you to create different types of Server like HTTP Server, DNS Server, TCP Server etc.

11. Node.js has a wide community of developers around the world. They are active in development of new modules or packages to support different types of applications development.

 

Limitations of Node.js

  1. It doesn’t support multi-threaded programming.
  2. It doesn’t support very high computational intensive tasks. When it executes long running task, it will queue all the incoming requests to wait for execution, since it follows JavaScript event loop which is single threaded.
  3. Node good for executing synchronous and CPU intensive tasks.

 

Installation

Install npm from: http://aka.ms/ntvs1.1.2013

On completing the NTVS 1.1.1 VS 2013.msi Setup, at last step you will get the below screen

clip_image002

Then you need to install node-v0.12.7-x64.msi by using the link on above snapshot “Click here to install Node.js and get started with NTVS” or you can go to the direct URL: https://nodejs.org/dist/v0.12.7/x64/node-v0.12.7-x64.msi

So, we are done with two above Node.JS installations.

Next, we need to make sure that the Sql instance we will be calling should be TCP/IP enabled

To configure that: Right click on My Computer — > Manage — > Go to Protocol for SQL server and enable TCP/IP

clip_image004

Now we will go to the visual studio and create a Blank Node.js Console Application. The selected template will be available as soon as we are done with two above Node js msi installations.

clip_image006

Give the name of the solution as “NodejsConsoleAppSample” and click on OK

clip_image008

Now right click on the npm node, which is under the solution and click on Install New npm Packages

clip_image010

It will take some time to download the packages.

As soon as downloads are completed, you will get the below screen

clip_image012

Select mssql server client and click on Install

In the solution we will have app.js where we will have to write the code to connect the SQL Server EmpDB database.

In the SQL Server databse I have created an instance as TARUN-PC\SQLSERVER and also created a sql login which is having full permission on EMPDB database.

Below is the SQL server Configuration in app.js:

 var sql = require('mssql');
 var fs = require('fs');
 
 var config = {
     user: '<User Name>',
     password: '<Password>',
     server: 'localhost\SQLSERVER', // You can use 'localhost\instance' to connect to named instance 
     database: 'EmpDB'
 };
 

Below are the codes to connect the sql database and Write the recordset to cosole window

 sql.connect(config).then(function () {
         
 
     //Inline query without parameter 
     new sql.Request()
     .query('select * from employees').then(function (recordset) {
         console.dir(recordset);
     }).catch(function (err) {
         console.log(err);
     });
     
     //Inline query with parameter 
     new sql.Request()
     .input('InputParameter', sql.Int, 2)
     .query('select * from employees where employeeid = @InputParameter').then(function(recordset) {
     console.dir(recordset);
     }).catch(function (err) {
         console.log(err);
     });
 
     // Stored Procedure With input parameter and return value as 1   
     new sql.Request()
     .input('EmpID', sql.Int, 1)
     .execute('[dbo].[GetEmployeeByEmployeeID]').then(function(recordsets) {
     console.dir(recordsets);
     }).catch(function (err) {
         console.log(err);
     });
 
     // Stored Procedure  with output parameter  
     var request = new sql.Request();
     request.input('EmpID', sql.Int, 1)
     request.output('OutputParameter', sql.VarChar(100))
     request.execute('[dbo].[GetEmployeeData]').then(function (recordsets) {
         console.dir(recordsets);
         console.dir(request.parameters.OutputParameter.value);
     }).catch(function (err) {
         console.log(err);
     });
 
 });
 

We can call the below ReadFile, AppendText, WriteFile methods for logging purpose.

The method to append the text on the existing text file :

 function AppendText(path, txt) {
            
     var content = fs.readFileSync(path, 'utf8')
     txt = content + txt;
     fs.writeFileSync(path, txt);
     
 }
 

The method to write the text on a new text file:

function WriteFile(path, txt) {

fs.writeFileSync(path, txt);

}

The method to read the text file

function ReadFile(path) {

if(fs.existsSync(path)){

return fs.readFileSync(path, ‘utf8’)

}

};

Hope in this artifact you help you to have some basic idea on Node js, utilities and implementation.

Happy Coding

Tarun Kumar Chatterjee

Category : Node, SQL

Author Info

Tarun Kumar Chatterjee
 
Net – Technology Specialist
 
Rate this article
 
Tarun has been working in IT Industry for over 12+ years. He holds a B-tech degree. He is passionate about learning and sharing the tricks and tips in Azure, .Net ...read more
 

Leave a comment