Node JSPowerShell

Node JS – Store Self Requests Form information in SQL

This article is to show simple steps to save the form information in SQL – The cool part is, this solution works as web app and REST API as well. The intention of this quick write up is to show how MSSQL and msnodesqlv8 helped me to solve the needs.

Step 1 – Create Table as desired and mine is illustrated below

Step 2 – Build a Form using the below Pug Code

doctype html
style.
    input[type=text],
    select {
    width: 100%;
    padding: 12px 20px;
    margin: 8px 0;
    display: inline-block;
    border: 1px solid #ccc;
    border-radius: 4px;
    box-sizing: border-box;
    }
    input[type=submit] {
    width: 100%;
    background-color: #4CAF50;
    color: white;
    padding: 14px 20px;
    margin: 8px 0;
    border: none;
    border-radius: 4px;
    cursor: pointer;
    }
    input[type=submit]:hover {
    background-color: #45a049;
    }
    div {
    border-radius: 5px;
    background-color: #f2f2f2;
    padding: 20px;
    }
h3 Confirm User Information for Validation
div
    form(action='/Insert' method="post")
        
        label(for='f1') Requester ID
        input#f1(type='text', name='RequesterID')

        label(for='f2') First Name
        input#f2(type='text', name='RequesterFirstName')
        
        label(for='f3') Last Name
        input#f3(type='text', name='RequesterLastName')

        label(for='f4') Display Name
        input#f4(type='text', name='RequesterDisplayName')
        
        label(for='f5') Email 
        input#f5(type='text', name='RequesterEmail')

        label(for='f6') Time Stamp
        input#f6(type='text', name='TimeStamp')

        label(for='f7') Ticket Number
        input#f7(type='text', name='RandomNumber')

        input(type='submit', value='Next')

Step 3 : Use the Server.JS code (Adds Functionality)

var express = require('express'),
    app = express(),
    sql = require('mssql'),
    bodyparser = require('body-parser'),
    path = require('path');

app.use(bodyparser.urlencoded({ extended: true }));
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'pug');

app.get("/", function (request, response) {
    response.render('index')
})

var config = {
    user: "sa",
    password: "admin",
    server: "server001",
    database: "myDB",
    options: {
        encrypt: false
    },
    port: 14330,
    domain: "contoso",
    driver: 'msnodesqlv8'
}

app.post("/Insert", function (request, response) {
    var pool = new sql.ConnectionPool(config, err => {
        if (err) console.log(err);
        pool.request().query(
            "INSERT INTO [tblMicroServiceLogger] (RequesterID,RequesterFirstName,RequesterLastName,RequesterDisplayName,RequesterEmail,TimeStamp,RandomNumber) VALUES ('" +
            request.body.RequesterID + "','" + request.body.RequesterFirstName + "','" +
            request.body.RequesterLastName + "','" + request.body.RequesterDisplayName + "','" +
            request.body.RequesterEmail + "','" + request.body.TimeStamp + "','" +
            request.body.RandomNumber + "')", (err, result) => {
                if(err) response.send(err);
                response.send(JSON.stringify(request.body))
            })
    })
})

app.listen(3000)
console.log("Your Application is running on port 3000")

Step 4: Response is in JSON (After inserting rows in SQL)


Now, let us use PowerShell 🙂

$Body = @{
    RequesterID          = "ChenV"
    RequesterFirstName   = "Chen"
    RequesterLastName    = "V"
    RequesterDisplayName = "Chen V"
    RequesterEmail       = "Chen.V@Conoso.com"
    TimeStamp            = (Get-Date).ToShortDateString()
    RandomNumber         = "SD56789"
}
$Result = Invoke-RestMethod -Uri "http://localhost:3000/Insert" -Method Post -Body $Body
$Result

Cool – Its works – Plan for bulk insert 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *