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









What is the importance of EDMX file in Entity Framework






-














Image







 ADO.NET entity is an ORM (object relational mapping) which creates a  higher abstract object model over ADO.NET components. So rather than  getting into dataset, datatables, command, and connection objects as  shown in the below code, you work on higher level domain objects like  customers, suppliers, etc.   DataTable table = adoDs.Tables[ 0 ]; for  ( int  j = 0 ; j < table.Rows.Count; j++) {     DataRow row = table.Rows[j];      //  Get the values of the fields     string  CustomerName =         ( string )row[ " Customername" ];     string  CustomerCode =         ( string )row[ " CustomerCode" ]; }      Benefits of using EF   The main and the only benefit of EF is it auto-generates code for the Model (middle layer), Data  Access Layer, and mapping code, thus reducing a lot of development time.   The importance of EDMX file in Entity Framework             EDMX (Entity Data Model XML) is an XML file which contains all the  mapping details of how your objects map ...








Read more










TRIGGER in sql server






-















  Trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.  DML triggers  execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected. For more information Types of DML Triggers AFTER trigger     AFTER triggers are executed after the action of the INSERT, UPDATE, MERGE, or DELETE statement is performed. AFTER triggers are never executed if a constraint violation occurs INSTEAD OF trigger     INSTEAD OF triggers override the standard actions of the triggering statement. Therefore, they can be used to perform error or value checking on one or more columns and the perform additional actions before insert, updating or deleting the row or rows. For example, when the value being updated in ...








Read more










Sending Email in asp.net or mvc using gmail or other smpt.






-














Image







   . how to configure email's setting to sending email by user account.    I have sent a email to user when his/her registration time. In this exam. ,I have sent email address, username, password and user verify link..  I have used a snippet code of email sending.   First of all, I have add the emailId,password and smpt details in web.config file.     <configSections>   <appSettings>  <add key="EmailHost" value="smtp.gmail.com" />      <add key="EmailPort" value="587" />      <add key="NoReplyEmail" value="adc@gmail.com" />      <add key="NoReplyPassword" value="password" />      <add key="NoReplyEmailDisplayName" value="display name" />      <add key="Sitename" value="site name"/>      <add key="StationeryPath" value="Content\Stationery\" />    </appSettings>    <connectionStrings...








Read more