Introduction

NOTE: Part 2 is available here

Node is one of the recent geek fever everyone has been going through. With the continued support from Microsoft for Nodejs on both Windows and Windows Azure, it is bound to reach great levels. Last week, during TechEd, Microsoft released it's own version of a Sql server driver for Nodejs. Yes, now you can write javascript code on the server that will talk to Sql Server.

Question: I haven't done any programming on Nodejs before this?

Answer: Don't worry about it. As long as you know typing on a notepad. You should be good.

NOTE: Documentation on this is still under construction but this blog post should get you started easily. If you have any questions during installation please feel free to contact me and I would be more than willing to help out.

First database call:

• Only on windows? Before we talk about it in detail it might be worthwhile to note that it only works if the nodejs server is running on a windows machine. Yes, that's true.
• Does that mean you can't code on a mac? No, you can. In that case you can run the server on a windows box or one of the Windows Azure VMs. Cool!

  Alrite. Let's take a small Hello world example. I have database called Test with a table Employee and two columns EmployeeID and Name.

• Creating the server: I am assuming you have Nodejs installed on your system. If not, please follow the directions here. Once Nodejs is installed on your computer, make sure you have NPM. NPM stands for Node Package Manager (you might be familiar with the Microsoft version Nuget). Go to a directory of your choice. I am using "C:\Chander\Blog\Node-SqlServer\Test" for this example. Open up command prompt and type
  
  npm install node-sqlserver

  NOTE: Make sure you have followed all the step discusses in blog post above, otherwise you will get an error. Also, make sure you have the environment variables set as described in the  blog post.

  

  NOTE: The Visual studio solution is not necessary, you can do this without it. But for people who prefer to have Visual Studio as their IDE, you can create a blank solution and do the same.

  Now add a blank file Server.js in the Test directory.

  Add the following code snippet to the Server.js file. Note this is a copy of the code from nodejs.org.

  var http = require('http'); http.createServer(function (req, res) { res.writeHead(200, {'Content-Type': 'text/plain'}); res.end('Hello World\n'); }).listen(1337, '127.0.0.1'); console.log('Server running at http://127.0.0.1:1337/');

• Running the server: The next step is to run the server.  Go to your browser and type http://localhost:1337/ and you should get a response Hello World .

You can also use Fiddler or curl to compose this request.

NOTE: This step is to make sure Nodejs is working on your machine. If it is not, please make sure it does before proceeding to the next section.

• Sql server: In order to use the Sql server driver for Node js, the first step is to include the module in your code. For now, we will add all the code in the same file i.e Server.js. In a future post, we will talk about how to do it more elegantly.

Also, do add your connection string in the following format.

var sql = require('node-sqlserver'); var conn_str = "Driver={SQL Server Native Client 10.0};Server=(local);Database=Test;Trusted_Connection={Yes}";

 

 Now that we have included 'node-sqlserver', we can use the functions inside that module. So, go ahead and use sql.open and open up a database connection.

sql.open(conn_str, function (err, conn) { if (err) { console.log("Database connection failed!"); return; } conn.queryRaw("SELECT Id, Name from Employee", function (err, results) { if (err) { console.log("Error running query!"); return; } for (var i = 0; i < results.rows.length; i++) { console.log("Id: " + results.rows[i][0] + " Name: " + results.rows[i][1]); } }); });

Cool: So, we can see when we run the Server again using the command 'node Server.js', we can see the values returned in the console.

If you are running this on a command prompt, hit 'Ctrl-C' to kill the server before using 'Node Server.js' to re-start it.

• Error: If you run this you will get an error - Cannot find module '.sqlserver.node'

 

This can be easily fixed by going to the following directory 'C:\Chander\Blog\Node-SqlServer\Test\node_modules\node-sqlserver\build\Release' and then copying 'sqlserver.node' and 'sqlserver.pdb' over to 'C:\Chander\Blog\Node-SqlServer\Test\node_modules\node-sqlserver\lib'. Please follow the directory structure on your machine.

Here you go and that's the result on your console.

 

Summary

In this post we had a cursory glance at the Microsoft SQL Server driver for Node.js. This post is just to get started and lots of cool stuff like creating your APIs and how to use this driver in better ways, will come up in the following posts.