Simple CRUD Node.js & Sql server

-
This is simple example of CRUD operation in node js with sql server

1 create a table in sql server


CREATE TABLE [dbo].[Customer](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[address] [nvarchar](max) NOT NULL,
[email] [nvarchar](max) NOT NULL,
[phone] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


2. Install package of "mssql". This is package for sql server in node js
3. create a separate js file "Settings" all credential of sql server ..

exports.dbConfig = {
    user: "sa",
    password: "admin@123",
    server: "localhost\\SQLEXPRESS",
    database: "Node",
    port: 1433
};  

4. Create Common file for execute sql queries in one function 

var sqlDb = require("mssql");
var settings = require("../settings");

exports.executeSql = function (sql, callback) {
    
    var conn = new sqlDb.Connection(settings.dbConfig);
    conn.connect().then(function () {
        
        var req = new sqlDb.Request(conn);
        req.query(sql).then(function (resultSet) {
            conn.close();
            callback(resultSet);
        }).catch(function (error) {
            console.log(error);
            callback(null, error);
        });
    }).catch(function (error) {
        
        console.log(error);
        callback(null, error);
    });
};

5. Create app file using routing and other namespace files

var express = require('express');
var routes = require('./routes');
var http = require('http');
var path = require('path');

 //load customers route
var customers = require('./routes/customers');
var app = express();

 app.set('port', process.env.PORT || 4300);
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

 app.get('/', routes.index);
app.get('/customers', customers.list);
app.get('/customers/add', customers.add);
app.post('/customers/add', customers.save);
app.get('/customers/delete/:id', customers.delete_customer);
app.get('/customers/edit/:id', customers.edit);
app.post('/customers/edit/:id', customers.save_edit);
//app.use(app.router);

 http.createServer(app).listen(app.get('port'), function () {
    console.log('Express server listening on port ' + app.get('port'));
});

6. Create customer file manage route operation 

var db = require("../db");
exports.list = function (req, resp) {
    db.executeSql("SELECT * FROM customer", function (rows, error) {
        if (err)
            console.log("Error Selecting : %s ", err);
        
        resp.render('customers', { page_title: "Customers - Node.js", data: rows });

    })
}

exports.add = function (req, resp) {
    resp.render('add_customer', { page_title: "Add Customers - Node.js" });
};

exports.edit = function (res, resp) {
    var id = req.params.id;
    db.executeSql("SELECT * FROM customer WHERE id=" + id, function (rows, error) {
        
        if (err)
            console.log("Error Selecting : %s ", err);
        
        res.render('edit_customer', { page_title: "Edit Customers - Node.js", data: rows });
    });

}
exports.save = function (req, res) {
    
    var input = JSON.parse(JSON.stringify(req.body));
    var data = {
        
        name    : input.name,
        address : input.address,
        email   : input.email,
        phone   : input.phone
        
    };
    db.executeSql("INSERT INTO customer set ? ", data, function (rows, error) {
        
        if (err)
            console.log("Error inserting : %s ", err);
        
        res.redirect('/customers');
          
    });
        
    
};


exports.save_edit = function (req, res) {
    var input = JSON.parse(JSON.stringify(req.body));
    var id = req.params.id;
    var data = {
        
        name    : input.name,
        address : input.address,
        email   : input.email,
        phone   : input.phone
        
    };
    db.executeSql("UPDATE customer set ? WHERE id = ? ", [data, id], function (rows, err) {
        
        if (err)
            console.log("Error Updating : %s ", err);
        
        res.redirect('/customers');
          
    });
};

exports.delete_customer = function (req, res) {
    
    var id = req.params.id;
    
    db.executeSql("DELETE FROM customer  WHERE id = ? ", id, function (err, rows) {
        
        if (err)
            console.log("Error deleting : %s ", err);
        
        res.redirect('/customers');
             
    });
};

here's html code (ejs template) for listing the customer

<%- include layouts/header.ejs %>
        <div class="page-data">
         <div class="data-btn">
           <button onClick="addUser();">+ Add</button>
         </div>
         <div class="data-table">
            <table border="1" cellpadding="7" cellspacing="7">
                <tr>
                    <th width="50px">No</th>
                    <th>Name</th>
                    <th>Address</th>
                    <th>Phone</th>
                    <th>Email</th>
                    <th width="120px">Action</th>
                </tr>                               
                <% if(data.length){ 
                                
                 for(var i = 0;i < data.length;i++) { %>                 
                <tr>
                    <td><%=(i+1)%></td>
                    <td><%=data[i].name%></td>
                    <td><%=data[i].address%></td>
                    <td><%=data[i].phone%></td>
                    <td><%=data[i].email%></td>
                    <td>
                        <a class="a-inside edit" href="../customers/edit/<%=data[i].id%>">Edit</a>                       
                        <a class="a-inside delete" href="../customers/delete/<%=data[i].id%>">Delete</a>                       
                    </td>
                </tr>
            <% }
            
             }else{ %>
                 <tr>
                    <td colspan="3">No user</td>
                 </tr>
            <% } %>
                                                             
            </table>
         </div>
        </div>        
<%- include layouts/footer.ejs %>


Solution structure ......


http://teknosains.com/i/simple-crud-nodejs-mysql







































Comments















Post a Comment



















Popular posts from this blog









Form submit resulting in “InvalidDataException: Form value count limit 1024 exceeded.” In ASP.NET Core






-















 You can change the default formvalue limit using the   FormOptions . If you are using MVC, then you can create a filter and decorate on action where you want to extend this limit and keep the default for rest of the actions.    namespace Filter {     using System;     using Microsoft.AspNetCore.Http.Features;     using Microsoft.AspNetCore.Mvc.Filters;     /// <summary>     /// Request Form Size Limit Attribute     /// </summary>     /// <seealso cref="System.Attribute" />     /// <seealso cref="Microsoft.AspNetCore.Mvc.Filters.IAuthorizationFilter" />     /// <seealso cref="Microsoft.AspNetCore.Mvc.Filters.IOrderedFilter" />     [AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, AllowMultiple = false, Inherited = true)]     public class RequestFormSizeLimitAttribute : Attribute, IAuthorizationFilter     {         /// <summary>         /// The form options         /// </summary>         private read








Read more










Repository Design Pattern in C#






-














Image







  What is the Repository Design Pattern in C#? The Repository Design Pattern in C# Mediates between the domain and the data mapping layers using a collection-like interface for accessing the domain objects. In other words, we can say that a Repository Design Patternacts as a middleman or middle layer between the rest of the application and the data access logic. That means a repository pattern isolates all the data access code from the rest of the application. The advantage of doing so is that, if you need to do any change then you need to do in one place. Another benefit is that testing your controllers becomes easy because the testing framework need not run against the actual database access code. With a repository  design pattern introduced, the above figure can be changed to: Why we need   the Repository Pattern in C#? Most of the data-driven applications need to access the data residing in one or more other data sources. Most of the time data sources will be a database. Again, the








Read more










Calculate Dynamic Current Calendar / Financial Year In C#






-















  I need a financial / calendar year based on current or today's date time   We need some time dynamically financial year or calendar year base on current date.   Suppose any company or organization have calendar year like 1 st  Jan to 31 th  Dec, 1 st  Feb to 31 th  Jan, 1 st  Apr to 31 Mar, 1 st  Oct to 31 th  Sep etc...…   So we need calculate current calendar year base on calendar start month of organization /company.   Examples   Suppose Today's date is 5 th  Feb 2017 and organization calendar start month is Jan(01) then current calendar year we will be 1 st  Jan 2017 to 31 th  Dec 2017.   Suppose Today's date is 10 th  Mar 2017 and organization calendar start month is Apr(04) then current calendar year we will be 1 st  Apr 2016 to 31 th  Mar 2017.   Suppose Today's date is 10 th  May 2018 and organization calendar start month is Apr(04) then current calendar year we will be 1 s t Apr 2018 to 31 th  Mar 2019.   public   static  DateTime GetCurrentCalendarStartDate(








Read more