Skip to content

NodeJS-Windows-SQL-DB.md

Latest commit

 

History

History
211 lines (152 loc) · 7.69 KB

NodeJS-Windows-SQL-DB.md

File metadata and controls

211 lines (152 loc) · 7.69 KB

Using SQL Database with NodeJS on Windoes

Requirements

You might already have some of the following required installations. ??

  • Node.js – Version 0.8.9 (32 bit version). Make sure you download the x86 version and not the x64 version. You might have to uninstall your current version and re-install this version to ensure compatibility.
  • Python 2.7.6.
  • Visual C++ 2010 - the Express edition is freely available from Microsoft.
  • SQL Server Native Client 11.0 - available as Microsoft SQL Server 2012 Native Client found in the SQL Server 2012 Feature Pack.

Install the required modules

Run cmd.exe as administrator. Navigate to your directory and enter the following commands:

  1. npm install msnodesql
  2. npm install -g node-gyp

Once node-gyp is installed, run the following commands inside the YourProjectDirectory > node_modules > msnodesql directory:

  1. node-gyp configure
  2. node-gyp build

You should now see a build folder inside msnodel. Navigate to build > release. Copy the sqlserver.node file and paste it in the msnodesql > lib folder. Replace the old file if needed.

Create a database and retrieve your connection string

See the getting started page to learn how to create a sample database and retrieve your connection string. It is important you follow the guide to create an AdventureWorks database template. The examples shown below will only work with the AdventureWorks schema.

Connect to your SQL Database

Copy the following code in a .js file located in your project directory. Run it using the following command

node index.js


var http = require('http');
var sql = require('msnodesql');
var http = require('http');
var fs = require('fs');
var useTrustedConnection = false;
var conn_str = "Driver={SQL Server Native Client 11.0};Server=tcp:yourserver.database.windows.net;" + (useTrustedConnection == true ? "Trusted_Connection={Yes};" : "UID=yourusername;PWD=yourpassword;") + "Database={AdventureWorks};";
sql.open(conn_str, function (err, conn) {
    if (err) {
        console.log("Error opening the connection!");
        return;
    }
    else
        console.log("Successfuly connected");
});

Execute a query and retrieve the result set

var http = require('http');
var sql = require('msnodesql');
var http = require('http');
var fs = require('fs');
var useTrustedConnection = false;
var conn_str = "Driver={SQL Server Native Client 11.0};Server=tcp:yourserver.database.windows.net;" + (useTrustedConnection == true ? "Trusted_Connection={Yes};" : "UID=yourusername;PWD=yourpassword;") + "Database={AdventureWorks};";
sql.open(conn_str, function (err, conn) {
    if (err) {
        console.log("Error opening the connection!");
        return;
    }
    else
        console.log("Successfuly connected");


    conn.queryRaw("SELECT c.CustomerID, c.CompanyName,COUNT(soh.SalesOrderID) AS OrderCount FROM SalesLT.Customer AS c LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, c.CompanyName ORDER BY OrderCount DESC;", function (err, results) {
        if (err) {
            console.log("Error running query1!");
            return;
        }
        for (var i = 0; i < results.rows.length; i++) {
            console.log(results.rows[i]);
        }
    });
   
});

Inserting a row, passing parameters, and retrieving the generated primary key value

var http = require('http');
var sql = require('msnodesql');
var http = require('http');
var fs = require('fs');
var useTrustedConnection = false;
var conn_str = "Driver={SQL Server Native Client 11.0};Server=tcp:yourserver.database.windows.net;" + (useTrustedConnection == true ? "Trusted_Connection={Yes};" : "UID=yourusername;PWD=yourpassword;") + "Database={AdventureWorks};";
sql.open(conn_str, function (err, conn) {
    if (err) {
        console.log("Error opening the connection!");
        return;
    }
    else
        console.log("Successfuly connected");


    conn.queryRaw("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES ('SQL Server Express', 'SQLEXPRESS', 0, 0, CURRENT_TIMESTAMP)", function (err, results) {
        if (err) {
            console.log("Error running query!");
            return;
        }
        for (var i = 0; i < results.rows.length; i++) {
            console.log("Product ID Inserted : "+results.rows[i]);
        }
    });
   
});

Transactions

[AZURE.NOTE] The method conn.beginTransactions will not work in SQL Database. Please follow the code example to perform transactions in SQL Database.

var http = require('http');
var sql = require('msnodesql');
var http = require('http');
var fs = require('fs');
var useTrustedConnection = false;
var conn_str = "Driver={SQL Server Native Client 11.0};Server=tcp:yourserver.database.windows.net;" + (useTrustedConnection == true ? "Trusted_Connection={Yes};" : "UID=yourusername;PWD=yourpassword;") + "Database={AdventureWorks};";
sql.open(conn_str, function (err, conn) {
    if (err) {
        console.log("Error opening the connection!");
        return;
    }
    else
        console.log("Successfuly connected");


    conn.queryRaw("INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.ProductID VALUES ('SQL Server Express New ', 'SQLEXPRESS New', 1, 1, CURRENT_TIMESTAMP)", function (err, results) {
        if (err) {
            console.log("Error running query!");
            return;
        }
        for (var i = 0; i < results.rows.length; i++) {
            console.log("Product ID Inserted : "+results.rows[i]);
        }
    });
    
    conn.queryRaw("ROLLBACK TRANSACTION; ", function (err, results) {
        	if (err) {
    		console.log("Rollback failed");
    		return;
    	}
	    });
});

Stored procedures

[AZURE.NOTE] For this code sample to work, you must first have or create a stored procedure that inputs no parameters. You can create a stored procedure with a tool such as SSMS.

var http = require('http');
var sql = require('msnodesql');
var http = require('http');
var fs = require('fs');
var useTrustedConnection = false;
var conn_str = "Driver={SQL Server Native Client 11.0};Server=tcp:yourserver.database.windows.net;" + (useTrustedConnection == true ? "Trusted_Connection={Yes};" : "UID=yourusername;PWD=yourpassword;") + "Database={AdventureWorks};";
sql.open(conn_str, function (err, conn) {
    if (err) {
        console.log("Error opening the connection!");
        return;
    }
    else
        console.log("Successfuly connected");
	
    conn.query("exec NameOfStoredProcedure", function (err, results) {
    	if (err) {
		console.log("Error running query8!");
		return;
	}
    });
});