1: var conn_str = config.sqlAzureConnection;
2: var categories = [];
3: var query = "SELECT CategoryID, Name, Description
4: FROM dbo.Categories";
5: sql.open(conn_str, function(err, conn) {
6: if(err) {
7: res.render('error',
8: {
9: title: 'Error opening the connection',
10: layout: false,
11: error: 'Could not open the database'
12: });
13: return;
14: }
15:
16: conn.queryRaw(query, function(err, results) {
17: if(err) {
18: res.render('error',
19: {
20: title: 'Error running query',
21: layout: false,
22: error: 'Error running the query'
23: });
24: return;
25: }
26:
27: for(var i = 0; i < results.rows.length; i++) {
28: categories[i] = {
29: ID: results.rows[i][0],
30: Name: results.rows[i][1],
31: Description: results.rows[i][2]
32: };
33: }
34: res.render('index', { title: 'CategoryList',
35: categories: categories });
36: }); //conn.queryRaw
37: }); // sql.open
Query the Database for editing a single record
In the below code block, we are query the single Category data based on the given CategoryID and rendering a View for editing the data
1: //Edit Category
2: controller.editCategory = function(req, res) {
3: var params = [req.params.id];
4: var query = "SELECT CategoryID, Name, Description
5: FROM dbo.Categories WHERE CategoryID = ?";
6: sql.open(conn_str, function(err, conn) {
7: if(err) {
8: res.render('error',
9: {
10: title: 'Error opening the connection!',
11: layout: false,
12: error: 'Could not open the database'
13: });
14: return;
15: }
16: conn.queryRaw(query, params, function(err, results) {
17: if(err) {
18: res.render('error',
19: {
20: title: 'Error running query!',
21: layout: false,
22: error: 'Error running the query'
23: });
24: return;
25: }
26: var category = {
27: ID: results.rows[0][0],
28: Name: results.rows[0][1],
29: Description: results.rows[0][2]
30: };
31: res.render('edit', { title: 'Edit Category',
32: category: category });
33: }); //conn.queryRaw
34: }); // sql.open
35: };//end edit category
36:
In the config file, I have created two connection strings for a SQL Express database and for Windows Azure SQL Database. You can replace the connection string information with your own database information.
Update the SQL Database
In the below code block, we are updating the posted data to the SQL Database for a single Category which we have taken for editing in the previous step.
1: //Update Category
2: controller.updateCategory = function(req, res) {
3: if(req.body) {
4: var query = "UPDATE dbo.Categories
5: SET Name=?,Description=? WHERE CategoryID=?";
6: var params = [req.body.name,
7: req.body.description,
8: req.body.id];
9: sql.open(conn_str, function(err, conn) {
10: if(err) {
11: res.render('error.jade',
12: {
13: title: 'Error running query!',
14: layout: false,
15: error: 'Could not open database'
16: });
17: return;
18: }
19: conn.queryRaw(query, params, function(err, results) {
20: if(err) {
21: res.render('error.jade',
22: {
23: title: 'Error running query',
24: layout: false,
25: error: 'Database update failed'
26: });
27: return;
28: }
29: res.redirect('/category');
30:
31: });
32: }); // sql.open
33: }
34: else {
35: res.render('error.jade',
36: {
37: title: 'Error posting Category',
38: layout: false,
39: error: 'Posted data not found'
40: });
41: }
42: };
Demo App in Github
The completed demo app available on Github at https://github.com/shijuvar/NodeOnAzure/tree/master/SqlNode which provides the all CRUD operation against the Category table. The demo Node.js app is built with Express and Jade view engine.