Server.js-file
/**
* Module dependencies.
*/
'use strict';
var express = require('express');
var http = require('http');
var path = require('path');
var app = express();
var routes = require('./config/routeConfig');
var sql = require('./config/connectionConfig');
var sqlTableType = require('./config/sqlTableTypeConfig');
var utils = require('./public/App/utils');
var sqlUtils = require('./public/App/sqlUtils');
// cross domain setup
const cors = require('cors');
const corsOptions = {
origin: '*'
}
app.use(cors(corsOptions));
// all environments
app.set('port', process.env.PORT || 3000);
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');
app.use(express.favicon());
app.use(express.logger('dev'));
app.use(express.json());
app.use(express.urlencoded());
app.use(express.methodOverride());
app.use(app.router);
app.use(require('stylus').middleware(path.join(__dirname, 'public')));
app.use(express.static(path.join(__dirname, 'public')));
// development only
if ('development' == app.get('env'))
{
app.use(express.errorHandler());
}
//get the url request
app.get('/', routes.index);
app.get('/customer', routes.customer);
//create server
http.createServer(app).listen(app.get('port'), function ()
{
console.log('Express server listening on port ' + app.get('port'));
});
//---------------------------------------------
app.post("/api/SaveProduct",
function (req, res) {
try {
var product = req.body;
//create table type tblProduct
var tblProduct = new sql.mssql.Table();
// Columns must correspond with type we have created in database.
tblProduct.columns.add('ProductId', sql.mssql.INT);
tblProduct.columns.add('ProductCode', sql.mssql.VarChar(4));
tblProduct.columns.add('DocumentationDescription', sql.mssql.VarChar(60));
tblProduct.columns.add('EffectiveDate', sql.mssql.Date);
tblProduct.columns.add('EndDate', sql.mssql.Date);
tblProduct.columns.add('ProductDescription', sql.mssql.VarChar(40));
tblProduct.columns.add('ProductGroup', sql.mssql.Char(2));
tblProduct.columns.add('ProductNumber', sql.mssql.SmallInt);
tblProduct.columns.add('ProductType', sql.mssql.VarChar(1));
tblProduct.columns.add('StatutoryCode', sql.mssql.VarChar(1));
tblProduct.columns.add('CreatedBy', sql.mssql.VarChar(100));
tblProduct.columns.add('CreatedDate', sql.mssql.VarChar(100));
tblProduct.columns.add('UpdatedBy', sql.mssql.VarChar(100));
tblProduct.columns.add('UpdatedDate', sql.mssql.VarChar(100));
tblProduct.columns.add('IsDeleted', sql.mssql.Bit);
tblProduct.columns.add('DeletedBy', sql.mssql.VarChar(100));
tblProduct.columns.add('DeletedDate', sql.mssql.VarChar(100));
tblProduct.rows.add(0,product.ProductCode,
product.DocumentationDescription
, product.EffectiveDate
, product.EndDate
, product.ProductDescription
, product.ProductGroup
, product.ProductNumber
, product.ProductType
, product.StatutoryCode
, "sa"
, "2017-09-01"
, "sa"
, "2017-09-01"
,0
, product.DeletedBy
, product.DeletedDate);
//post query
var param = [{ "ParameterName": "tblProdcut", "DataType": "TableType", "Value": tblProduct }];
sqlUtils.SqlSpBulkExecute("bulkInsertProduct", param, function (result, error)
{
console.log(result);
console.log("Bulk Inserted");
res.send("1");
});
} catch (e)
{
console.log(e);
sql.close();
}
});
//--------DataBase api ------------------
app.get("/api/GetProducts",
function (req, res)
{
sqlUtils.SqlQueryExecute("select * from Product", function (result, error)
{
res.send(result);
});
});
//api for get data from database
app.get("/api/GetCustomer",
function (req, res)
{
sqlUtils.SqlQueryExecute("select * from customer", function (result, error)
{
res.send(result);
});
});
//api for insert data into database
app.post("/api/AddCustomer", function (req, res)
{
var param = [{ "ParameterName": "CustomerName", "DataType": "varchar", "Value": req.body.CustomerName },
{ "ParameterName": "City", "DataType": "varchar", "Value": req.body.City }];
sqlUtils.SqlSpExecute("CreateCustomer", param, function (result, error)
{
console.log(result);
res.send("1");
});
});
//api for Upadte data
app.post("/api/UpdateCustomer", function (req, res)
{
var param = [{ "ParameterName": "CustomerID", "DataType": "INT", "Value": req.body.CustomerID },
{ "ParameterName": "CustomerName", "DataType": "varchar", "Value": req.body.CustomerName },
{ "ParameterName": "City", "DataType": "varchar", "Value": req.body.City }];
sqlUtils.SqlSpExecute("UpdateCustomer", param, function (result, error)
{
console.log(result);
res.send("1");
});
});
//api for Delete data from database
app.post("/api/DeleteCustomer", function (req, res)
{
console.log("DeleteCustomer");
var param = [{ "ParameterName": "CustomerID", "DataType": "INT", "Value": req.body.CustomerID }];
sqlUtils.SqlSpExecute("DeleteCustomer", param, function (result, error)
{
console.log(result);
res.send("1");
});
});
//------------------------------------
CoonectionCofig.js
var mssql = require("mssql");
var dbConfig = {
user: 'sa',
password: '123',
server: '192.168.90.40\\SQLEXPRESS',
database: 'OneOfficeRefreshDevDB_20170925',
port: 1433
};
var dbConn = null;
var sql = { mssql: mssql, dbConfig: dbConfig, dbConn: dbConn };
module.exports= sql;
---------------------------------
RouteConfig.js
exports.index = function (req, res) {
res.render('index', { title: 'NodeJsDemo' });
};
exports.customer = function (req, res) {
res.render('customer', { title: 'Customer' });
};
//-----------Table Type-------
//create table type tblProduct
var tblProduct = new sql.mssql.Table();
// Columns must correspond with type we have created in database.
tblProduct.columns.add('ProductId', sql.mssql.INT);
tblProduct.columns.add('ProductCode', sql.mssql.VarChar(4));
tblProduct.columns.add('DocumentationDescription', sql.mssql.VarChar(60));
tblProduct.columns.add('EffectiveDate', sql.mssql.Date);
tblProduct.columns.add('EndDate', sql.mssql.Date);
var tableType = { tblProduct: tblProduct };
module.exports = tableType;
//-----------------------------------------
/// <reference path="../../config/connectionConfig.js" />
(function (exports)
{
var sql = require('../../config/connectionConfig.js');
function dataTypeMap(dataType) {
if (dataType === "INT")
return sql.mssql.INT;
else
return sql.mssql.VarChar(500);
}
exports.SqlQueryExecute = function (sqlQuery, callback)
{
const dbConnection = new sql.mssql.ConnectionPool(sql.dbConfig, err =>
{
new sql.mssql.Request(dbConnection).query(sqlQuery, (err, result) =>
{
if (err)
{
console.log(err);
callback(null, err);
}
dbConnection.close();
callback(JSON.stringify(result.recordset),null);
});
});
dbConnection.on('error', err =>
{
callback(null, err);
dbConnection.close();
});
};
exports.SqlSpExecute = function (spName, param, callback)
{
const dbConnection = new sql.mssql.ConnectionPool(sql.dbConfig, err =>
{
var request = new sql.mssql.Request(dbConnection);
for (var i = 0; i < param.length; i++) {
console.log(param[i]["ParameterName"] + " " + param[i]["DataType"] + " " + param[i]["Value"]);
request.input(param[i]["ParameterName"], dataTypeMap(param[i]["DataType"]), param[i]["Value"]);
}
request.execute(spName, (err, result) =>
{
if (err)
{
console.log(err);
// callback(null, err);
}
dbConnection.close();
callback("Successfully Updated",null);
});
});
dbConnection.on('error', err =>
{
callback(null, err);
dbConnection.close();
});
};
exports.SqlSpBulkExecute = function (spName, param, callback) {
sql.mssql.close();
const dbConnection = new sql.mssql.ConnectionPool(sql.dbConfig, err =>
{
var request = new sql.mssql.Request(dbConnection);
for (var i = 0; i < param.length; i++)
{
console.log(param[i]["ParameterName"] + " " + param[i]["DataType"] + " " + param[i]["Value"]);
if (param[i]["DataType"] === "TableType")
request.input(param[i]["ParameterName"], param[i]["Value"]);
else
request.input(param[i]["ParameterName"], dataTypeMap(param[i]["DataType"]), param[i]["Value"]);
}
request.execute(spName, (err, result) =>
{
if (err)
{
console.log(err);
// callback(null, err);
}
console.log(result);
dbConnection.close();
callback("Successfully Updated", null);
});
});
dbConnection.on('error', err =>
{
callback(null, err);
dbConnection.close();
});
};
})((typeof process === 'undefined' || !process.versions) ? window.common = window.common || {} : exports);
----------------------------------------------
customer.js
var CustomersApp = angular.module("CustomersApp", []);
CustomersApp.controller("CustomersController",
function ($scope, $http) {
$scope.Customer = {};
$scope.CustomerList = {};
$scope.getCustomer = function () {
$.ajax({
url: "/api/GetCustomer",
dataType: 'json',
type: 'GET',
success: function (data)
{
$scope.CustomerList = angular.copy(data);
$scope.isEdit = false;
$scope.isNew = false;
$scope.$apply();
}
});
}
$scope.createCustomer = function () {
$("#ManageCustomer").show();
$("#Insert").show();
$("#Update").hide();
$scope.Customer.CustomerName = null;
$scope.Customer.City = null;
}
$scope.saveCustomer = function () {
if ($scope.Customer.CustomerName == null || $scope.Customer.CustomerName === "") {
alert("CustomerName is required !!");
return false;
}
var customer = $scope.Customer;
$http({
method : "Post",
url: "/api/AddCustomer",
dataType: 'json',
data: customer
}).then(function mySuccess(data) {
if (data.data=== "1") {
$scope.getCustomer();
alert("Data saved successfully !");
}
$("#ManageCustomer").hide();
$("#Update").hide();
$("#Insert").hide();
}, function myError(response) {
});
}
$scope.updateCustomer = function () {
if ($scope.Customer.CustomerName == null || $scope.Customer.CustomerName === "") {
alert("CustomerName is required !!");
return false;
}
var customer = $scope.Customer;
$http({
method : "Post",
url: "/api/UpdateCustomer",
dataType: 'json',
data: customer
}).then(function mySuccess(data) {
if (data.data=== "1") {
$scope.getCustomer();
alert("Data updated successfully !");
}
$("#ManageCustomer").hide();
$("#Update").hide();
$("#Insert").hide();
}, function myError(response) {
alert("error");
});
}
$scope.editCustomer = function (customer) {
$("#ManageCustomer").show();
$("#Update").show();
$("#Insert").hide();
$scope.Customer.CustomerName = customer.CustomerName;
$scope.Customer.City = customer.City;
$scope.Customer.CustomerID = customer.CustomerID;
}
$scope.closeCustomer = function () {
$("#ManageCustomer").hide();
}
$scope.deleteCustomer = function (customer) {
$("#ManageCustomer").hide();
$("#Update").hide();
$("#Insert").hide();
$http({
method : "Post",
url: "/api/DeleteCustomer",
dataType: 'json',
data: customer
}).then(function mySuccess(data) {
if (data.data=== "1") {
$scope.getCustomer();
alert("Data deleted successfully !");
}
$("#ManageCustomer").hide();
$("#Update").hide();
$("#Insert").hide();
}, function myError(response) {
alert("error");
});
}
});
/*
$(document).ready(function () {
getCustomer();
$('#Insert').click(function () {
var customerName = $('#CustomerName').val();
var city = $('#City').val();
var customer = { CustomerName: customerName, City: city };
$.ajax({
url: "/api/AddCustomer",
dataType: 'json',
data: customer,
type: 'POST',
success: function (data) {
alert(data.data);
}
});
});
$('#Update').click(function () {
var customerName = $('#CustomerName').val();
var city = $('#City').val();
var customer = { CustomerName: customerName, City: city };
$.ajax({
url: "/api/UpdateCustomer",
dataType: 'json',
data: customer,
type: 'POST',
success: function (data) {
alert(data.data);
}
});
});
function getCustomer() {
$.ajax({
url: "/api/GetCustomer",
dataType: 'json',
type: 'GET',
success: function (data) {
alert(data.customers[0].CustomerName);
}
});
}
});
*
* */
extends layout
block content
form(ng-app="CustomersApp" ng-controller="CustomersController" ng-init="getCustomer()")
div#ManageCustomer(style="display:none;")
.col-md-6
.panel.panel-default
.panel-heading.panel-default Customer Details
.panel-body
.row
.form-group
.col-md-3
|Customer Name
.col-md-8
input#CustomerName.form-control(name='CustomerName' ng-model="Customer.CustomerName")
|
br
.row
.form-group
.col-md-3
| City
.col-md-8
input#City.form-control(name='City' ng-model="Customer.City" style="margin-left:8px;")
|
br
.row
.col-md-3
.col-md-8
|
button#Insert(class="btn btn-xs btn-success" ng-click="saveCustomer()" style="display:none;") Save
|
button#Update(class="btn btn-xs btn-success" ng-click="updateCustomer()" style="display:none;") Update
|
button#Close(class="btn btn-xs btn-success" ng-click="closeCustomer()") Close
br
br
div
.col-md-6
.panel.panel-default
.panel-heading.panel-default Customer Details
a(class="btn btn-xs btn-primary pull-right" ng-click="createCustomer(e)") New
.panel-body
.row
.col-md-6
br
code.language-markup
pre.
<table class="table table-striped table-bordered" >
<tr><th>Name</th><th >City</th><th >Edit</th><th >Delete</th></tr>
<tbody>
<tr ng-repeat="customer in CustomerList track by $index">
<td style="display:none;">{{customer.CustomerID}} </td>
<td>{{customer.CustomerName}} </td>
<td>{{customer.City}} </td>
<td> <a class="btn btn-xs btn-primary margin-right-5" title="Edit" ng-click="editCustomer(customer)"><span class="glyphicon glyphicon-pencil"></span></a> </td>
<td><a class="btn btn-xs btn-danger margin-right-5" id="btnDelete" ng-click="deleteCustomer(customer)" title="Delete"><span id="spanDelete" class="glyphicon glyphicon-trash"></span></a> </td>
</tr>
</tbody>
</table>
script(src='/App/customer.js')
Comments
Post a Comment