Node.js for .NET Developers (2015)

Chapter 6. Working with external data in Node.js

Rendering static or dummy data is one thing, but connecting your UI to live data is something else again. If you want your application to handle the real world, you need it to track and assimilate data provided by back-end data stores, whatever they may be.

In our case, we’ll start with databases—specifically, with Microsoft SQL Server. Why SQL Server? There are two reasons.

First, there is a good chance you are exploring Node.js as some sort of complement to or replacement of a .NET web application. In the vast majority of cases, the back-end database for .NET is SQL Server. Second, SQL Server has within it stored procedures (chunks of prebuilt SQL code that are held in the database that you can call by name from your code). Because these are widely used but not available in other databases such as MySQL, I want to cover the feature and show how to employ them from Node.js.

That being said, if you are trying to get things going by doing your first development with Node.js and are using SQL Express as your database, well, the fact is I couldn’t get either of the primary npm packages for SQL Server, including our choice tedious, to work on my machine with SQL Express. Many hours of diligent effort to connect to my localhost left me with only ECONNREFUSED and ENOTFOUND errors as I played with the settings in my connection.

This is obviously frustrating at certain levels, if only because the process of connecting smoothly to a fresh install of MySQL is quite simple and happens instantly with no aggravation at all. That tells you that the problem clearly has to do with permissions. That can be a challenging area in the land of Microsoft products and is not covered in this tome. So just in case you are trying to work locally, for the moment you will also install MySQL because I know you can easily get to it on your machine.

MySQL is a relational database system that is almost the same as working with SQL Server but for the absence of stored procedures. As we pass through our various CRUD (Create, Update, Delete) operations, I’ll include a few lines about how to work with it so as not to leave anything out. We’ll also go ahead and grab the MySQL npm package so that you can work with both databases if you want to. (You already installed the SQL Server npm package when we started.) Entering the following in the correct location of your command prompt is usually all it takes to get set up for MySQL:

npm install mysql

Installing MySQL isn’t directly a topic of this book but if you want to retrieve it, head over to this site:

http://dev.mysql.com/downloads/windows/

You’ll find that the install process is very fast and easy. By default, you won’t have any user interface in the same way that you need to use SQL Server Management Studio as a visual tool to get into your SQL Server. However, you won’t need one because you’ll be doing directly from code whatever you need to do.

For interacting with your chosen SQL Server, you’ll need to add a couple of statements to the top of your file with some objects returned that you need in order to do that interaction:

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;

Now you can get back to your post operation:

router.post('/survey', function (req, res) {
var config = {
userName: 'fromCache',
password: ' fromCache ',
server: '123.45.67.89',
options:
{
database: "Test"
}
};
var sInput = req.body.txtInbound;
console.log(sInput);
});

Notice that you have now inserted your connection configuration information. To keep it simple for the moment, you can simply insert your hard-coded credentials into the code. You can already see from the example some of the potential power and ease of using the cache to hold application-wide data.

At this point, after the configuration you just try to turn it on and also provide some error handling if you don’t succeed:

var connection = new Connection(config);
connection.on('connect', function (err) {
// If no error, then good to go...
// Do some SQL here;
} );
connection.on('debug', function (text) {
console.log(text);
}

Assuming you have no errors (and this is where you will get them if you do), you are good to go with the database.

As you see here, the code to connect to a local version of MySQL is nearly identical assuming you have already made a similar npm reference to it:

var connection = mysql.createConnection(
{
host: 'localhost',
user: 'fromCache',
password: 'fromCache',
database: 'test'
});
connection.connect();

The database test indicated in the code already exists in MySQL, and it has a couple of tables with a small number of fields you can use to begin development work.

Next you need code to execute some SQL. Even in a post insert or a put edit, there are times when you might want to pull back the data you just inserted, especially if default values were provided to the record during the insert. Typically, fetching data is done during a get operation, but we’ll include both aspects here. So you need both a way to execute your insert and also a way to receive a collection and loop through it row by row.

Obviously, you can’t post any data to, or pull any data from, a table that doesn’t exist. So, either in your SQL Server database or in your MySQL database, create a table that will hold the player information you used before with these fields:

id, firstName, lastName, sport

Then populate it with the following values:

1, Payton, Walter, Football

2, Ruth, Babe, Baseball

3, Gretzky, Wayne, Hockey

4, Woods, Tiger, Golf

I’ll assume for the example that this table is called tblPlayers.

Let’s start with the insert. I won’t even talk about SQL to SQL Server without parameters. If you’re still doing SQL that way, basically whatever gets hacked in your database gets hacked. You’ve only had a decade of warning not to use SQL without parameters and, I apologize, but I just can’t generate any sympathy. The data you need to add is this:

5, Orr, Bobby, Hockey

For your insert code, you need the SQL and the parameters along with your input fields from your form post and some tedious specifics such as the first line, which is self-explanatory:

var TYPES = require('tedious').TYPES;
var sql = 'insert into tblPlayers (id, firstName, lastName, sport)';
sql += 'values (@id, @first, @last, @sport)';
var request = new Request(sql, function(err) {
....
});
var sFirst = req.body.txtFirst;
var sLast = req.body.txtLast;
var sSport = req.body.txtSport;
var iID = req.body.txtID;

request.addParameter('id', TYPES.Int, iID);
request.addParameter('first', TYPES.VarChar, sFirst);
request.addParameter('last', TYPES.VarChar, sLast);
request.addParameter('sport', TYPES.VarChar, sSport);
connection.execSql(request);

Then send a response that indicates something good happened. In many cases, this would be the ID just created, although for our example we are supplying the value. The whole function looks like this:

router.post('/survey', function (req, res) {
var config = {
userName: 'fromCache',
password: ' fromCache ',
server: '123.45.67.89',
options:
{
database: "Test"
}
};
var connection = new Connection(config);
connection.on('connect', function (err) {
var TYPES = require('tedious').TYPES;
var sql = 'insert into tblPlayers (id, firstName, lastName, sport)';
sql += 'values (@id, @first, @last, @sport)';
var request = new Request(sql, function(err) {
....
});
var sFirst = req.body.txtFirst;
var sLast = req.body.txtLast;
var sSport = req.body.txtSport;
var iID = req.body.txtID;
request.addParameter('id', TYPES.Int, iID);
request.addParameter('first', TYPES.VarChar, sFirst);
request.addParameter('last', TYPES.VarChar, sLast);
request.addParameter('sport', TYPES.VarChar, sSport);
connection.execSql(request);
res.send(iID.toString() + ' entered ok');
} );
connection.on('debug', function (text) {
console.log(text);
}
}); // closes the post function and callback

With this post action receiver in place, you just need to take care of adding a couple of fields to your user interface. To keep it all clean, just go ahead and add a new view to your project quickly that you can use for your player data insert. Create playerAdmin.js and playerAdmin.ejs, and add them to your project as usual.

We’ll start with playerAdmin.ejs. Add four input fields and a button wrapped in a form like this:

<form action="/playerAdmin" method="post">
<h3>
Enter new values for db</h3>
ID: <input id="txtID" name="txtID" type="text" /><br />
First Name: <input id="txtFirst" name="txtFirst" type="text" /><br />
Last Name: <input id="txtLast" name="txtLast" type="text" /><br />
Sport: <input id="txtSport" name="txtSport" type="text" /><br />
<input type="submit" />
</form>

Notice the specific action we intend to implement to a route in your new playerAdmin.js file. So let’s turn our attention to the route. First, make sure you add the references you need:

var express = require('express');
var router = express.Router();
var Connection = require('tedious').Connection;
var Request = require('tedious').Request;

Next, create a post handler that does exactly the same as the one you created earlier. Copy and paste is fine if you correct the path as shown. Also, notice that you render your results inside the callback of the top-level Request function because this is the only time you can be assured that all of your internal Request functions have run completely and populated the object collection you need to render your view.

router.post('/playerAdmin', function (req, res) {
var config = {
userName: 'fromCache',
password: ' fromCache ',
server: '123.45.67.89',
options:
{
database: "Test"
}
};
var connection = new Connection(config);
connection.on('connect', function (err) {
var TYPES = require('tedious').TYPES;
var sql = 'insert into tblPlayers (id, firstName, lastName, sport)';
sql += 'values (@id, @first, @last, @sport)';
var request = new Request("select * from tblPlayers", function (err, rowCount)
{
if (err) {
console.log(err);
} else {
res.redirect('/survey');
res.render('survey', {
players: cache.get("PlayerList")
});
}
});
connection.close();
var sFirst = req.body.txtFirst;
var sLast = req.body.txtLast;
var sSport = req.body.txtSport;
var iID = req.body.txtID;
request.addParameter('id', TYPES.Int, iID);
request.addParameter('first', TYPES.VarChar, sFirst);
request.addParameter('last', TYPES.VarChar, sLast);
request.addParameter('sport', TYPES.VarChar, sSport);
connection.execSql(request);
} );
connection.on('debug', function (text) {
console.log(text);
});
});

Although you could post to it right now, if you tried to browse to the page as is, you’d get the following error:

Cannot GET playerAdmin

That’s because the default action of an HTTP web request is a GET and you actually haven’t created a function for that. So you’ll need a simple get as well as the post to render your view in the first place:

router.get('/playerAdmin', function (req, res) {
res.render('playerAdmin');
});
module.exports = router;

It does not matter where in your file this function is placed relative to the post function. Don’t forget to globalize the router and route with the last line.

Now you just need to add the static reference to your app.js file along with the rest, like this:

server.use(require('./playerAdmin'));

You should be all set to browse to the page. When it opens, you should see your input boxes. Enter the following values:

5, Orr, Bobby, Hockey

Click the Submit button, and you should see an indication of success. Now let’s verify that’s true by fetching all the rows and displaying that simple list of players using our Object-Oriented JavaScript (OOJS) connected to more SQL functions.

To select and iterate rows, you need several lines. To keep everything neat, let’s create a function called fetchPlayers and put this code in it:

Now you can call this function after your insert to easily confirm your data interaction was completely successful:

. . .
connection.execSql(request);
fetchPlayers();

As you see from the code, you simply wrote the results to the console for the moment, but you can clearly see where within the code you need to populate your collection of Player objects to display in the view. You need to take apart your resultset into individual columns and set the properties of each Player object to the associated values in the forEach loop of the request.on function:

request.on('row', function (columns)
{
var oPlayer = new Player();
columns.forEach(function (column)
{
if (column.value === null) {
console.log('NULL');
} else {
// console.log(column.metadata.colName);
switch (column.metadata.colName) {
case "firstName":
oPlayer.firstName = column.value;
break;
case "lastName":
oPlayer.lastName = column.value;
break;
case "sport":
oPlayer.sport = column.value;
break;
case "id":
oPlayer.id = column.value;
break;
}
}
}); // columns for each
arrPlayers.push(oPlayer);
cache.put("PlayerList", arrPlayers);
}); // rows for each
cache.put("PlayerList", arrPlayers);
});

Then you just add each object to an array and stick that array in the cache. Again, for all of this to work properly, don’t forget to add your Person and Player class-functions to the top of the file as well as your cache reference.

Now that you have the list of Player objects in cache, you can get to them easily to provide the arguments you need to render your list. Because the survey page is all set up to read those values, you used that page for your display by supplying the redirect like this as I indicated in the top-level Request function:

res.redirect('/survey');

Now when you do your data insert on one page, you’ll get sent to another page to show the result. If you run this code and submit values to add to your list of players, you should see your ever-growing list on the survey page as you do so.

Using MySQL to do the same is similar:

As you see, it also allows row-by-row access to values. Simply assign the database values column-by-column as was done with the values returned from SQL Server. Otherwise, the surrounding Node.js code is the same.

By this point, you’re in pretty good shape. You’ve connected to a relational database management system (RDBMS) for CRUD operations, you’ve used your OOJS to easily manage values, and you’ve routed your results as desired using the cache.

But data can also be stored in the file system, so next let’s look at accessing and processing files.