node.js mssql 应用,登录实例

最新推荐文章于 2024-08-10 07:30:18 发布

fddqfddq

最新推荐文章于 2024-08-10 07:30:18 发布

阅读量517

点赞数

分类专栏： node.js 文章标签： node.js

本文链接：https://blog.csdn.net/fddqfddq/article/details/111480183

版权

node.js 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

node.js下连接mssql server 操作数据

运行node login.js后，会listen 8081 port.

输入http://localhost:8081/login

调用db help从mssql表验证，最后返回数据到前端。

mssql api参考 https://www.npmjs.com/package/mssql#prepared-statement

安装包

npm install express --save
npm install body-parser --save

npm install multer --save

Express 框架核心特性：

可以设置中间件来响应 HTTP 请求。
定义了路由表用于执行不同的 HTTP 请求动作。
可以通过向模板传递参数来动态渲染 HTML 页面

body-parser - node.js 中间件，用于处理 JSON, Raw, Text 和 URL 编码的数据。
multer - node.js 中间件，用于处理 enctype="multipart/form-data"（设置表单的MIME编码）的表单数据。

如下文件都在同级目录。

1.DB 配置-> config.js

let app = {
    user: '',            //用户名
    password: '', //密码
    server: '',   //服务器
    database: '',  //数据库
    port: 1433,            //端口默认不用改
    options: {
    encrypt: true // Use this if you're on Windows Azure
    },
    pool: {
        min: 0,
        max: 10,
        idleTimeoutMillis: 3000
    }
  };
   
  module.exports = app;

2.db helper-> db.js

const mssql = require("mssql");
const conf = require("./config.js");
 
let restoreDefaults = function () {
    conf;
};
const con = new mssql.ConnectionPool(conf);
 
con.on('error', err => {
    if (err) {
        throw err;
    }
});
 
con.connect(err => {
    if (err) {
        console.error(err);
    }
});
let execProc = async function (procName, params, callBack) {
    con.connect(conf).then(pool => {
        return pool.request()
            .input('sql', sql.NVarChar(500), 'select getdate()')
            .execute(procName)
    }).then(result => {
        console.dir(result)
    }).catch(err => {
        // ... error checks
    })
};
let querySql = async function (sql, params, callBack) {
    try{
        let ps = new mssql.PreparedStatement(con);
        if (params != "") {
            for (var index in params) {
                if (typeof params[index] == "number") {
                    ps.input(index, mssql.Int);
                } else if (typeof params[index] == "string") {
                    ps.input(index, mssql.NVarChar);
                }
            }
        }
        ps.prepare(sql, err => {
            if (err)
                console.log(err);
            ps.execute(params, (err, recordset) => {
                callBack(err, recordset);
                ps.unprepare(err => {
                    if (err)
                        console.log(err);
                });
            });
        });
    }catch(err){
        console.error('SQL error', err);
    }
    restoreDefaults();
};
 
 
var select = async function (tableName, topNumber, whereSql, params, orderSql, callBack) {
    try{
        var ps = new mssql.PreparedStatement(con);
         var sql = "select * from " + tableName + " ";
       // var sql = tableName;
        if (topNumber != "") {
            sql = "select top(" + topNumber + ") * from " + tableName + " ";
        }
        sql += whereSql + " ";
        if (params != "") {
            console.log(params)
            for (var index in params) {
                console.log('111111'+params[index])
                console.log('222222'+index)
                if (typeof params[index] == "number") {
                    ps.input(index, mssql.Int);
                } else if (typeof params[index] == "string") {
                    ps.input(index, mssql.NVarChar);
                }
            }
        }
        sql += orderSql;
        console.log(sql);
        ps.prepare(sql, err => {
            if (err)
                console.log(err);
            ps.execute(params, (err, recordset) => {
                callBack(err, recordset);
                ps.unprepare(err => {
                    if (err)
                        console.log(err);
                });
            });
        });
    }catch(err){
        console.error('SQL error', err);
    }
    restoreDefaults();
};
 
var selectAll = async function (tableName, callBack) {
    try{
        var ps = new mssql.PreparedStatement(con);
        var sql = "select * from " + tableName + " ";
        ps.prepare(sql, err => {
            if (err)
                console.log(err);
            ps.execute("", (err, recordset) => {
                callBack(err, recordset);
                ps.unprepare(err => {
                    if (err)
                        console.log(err);
                });
            });
        });
    }catch(err){
        console.error('SQL error', err);
    }
    restoreDefaults();
};
 
var add = async function (addObj, tableName, callBack) {
    try{
        var ps = new mssql.PreparedStatement(con);
        var sql = "insert into " + tableName + "(";
        if (addObj != "") {
            for (var index in addObj) {
                if (typeof addObj[index] == "number") {
                    ps.input(index, mssql.Int);
                } else if (typeof addObj[index] == "string") {
                    ps.input(index, mssql.NVarChar);
                }
                sql += index + ",";
            }
            sql = sql.substring(0, sql.length - 1) + ") values(";
            for (var index in addObj) {
                if (typeof addObj[index] == "number") {
                    sql += addObj[index] + ",";
                } else if (typeof addObj[index] == "string") {
                    sql += "'" + addObj[index] + "'" + ",";
                }
            }
        }
        sql = sql.substring(0, sql.length - 1) + ")";
        ps.prepare(sql, err => {
            if (err)
                console.log(err);
            ps.execute(addObj, (err, recordset) => {
                callBack(err, recordset);
                ps.unprepare(err => {
                    if (err)
                        console.log(err);
                });
            });
        });
    }catch(err){
        console.error('SQL error', err);
    }
    restoreDefaults();
};
 
var update = async function (updateObj, whereObj, tableName, callBack) {
    try{
        var ps = new mssql.PreparedStatement(con);
        var sql = "update " + tableName + " set ";
        if (updateObj != "") {
            for (var index in updateObj) {
                if (typeof updateObj[index] == "number") {
                    ps.input(index, mssql.Int);
                    sql += index + "=" + updateObj[index] + ",";
                } else if (typeof updateObj[index] == "string") {
                    ps.input(index, mssql.NVarChar);
                    sql += index + "=" + "'" + updateObj[index] + "'" + ",";
                }
            }
        }
        sql = sql.substring(0, sql.length - 1) + " where ";
        if (whereObj != "") {
            for (var index in whereObj) {
                if (typeof whereObj[index] == "number") {
                    ps.input(index, mssql.Int);
                    sql += index + "=" + whereObj[index] + " and ";
                } else if (typeof whereObj[index] == "string") {
                    ps.input(index, mssql.NVarChar);
                    sql += index + "=" + "'" + whereObj[index] + "'" + " and ";
                }
            }
        }
        sql = sql.substring(0, sql.length - 5);
        ps.prepare(sql, err => {
            if (err)
                console.log(err);
            ps.execute(updateObj, (err, recordset) => {
                callBack(err, recordset);
                ps.unprepare(err => {
                    if (err)
                        console.log(err);
                });
            });
        });
    }catch(err){
        console.error('SQL error', err);
    }
    restoreDefaults();
};
 
var del = async function (whereSql, params, tableName, callBack) {
    try{
        var ps = new mssql.PreparedStatement(con);
        var sql = "delete from " + tableName + " ";
        if (params != "") {
            for (var index in params) {
                if (typeof params[index] == "number") {
                    ps.input(index, mssql.Int);
                } else if (typeof params[index] == "string") {
                    ps.input(index, mssql.NVarChar);
                }
            }
        }
        sql += whereSql;
        ps.prepare(sql, err => {
            if (err)
                console.log(err);
            ps.execute(params, (err, recordset) => {
                callBack(err, recordset);
                ps.unprepare(err => {
                    if (err)
                        console.log(err);
                });
            });
        });
    }catch(err){
        console.error('SQL error', err);
    }
    restoreDefaults();
};
 
exports.config = conf;
exports.del = del;
exports.select = select;
exports.update = update;
exports.querySql = querySql;
exports.selectAll = selectAll;
exports.restoreDefaults = restoreDefaults;
exports.add = add;
exports.execProc = execProc;

3.登录 api -> login.js

var db = require('./db')
var express = require('express')
var app = express();
var bodyParser = require('body-parser');
const { debug } = require('console');
// 创建 application/x-www-form-urlencoded 编码解析
var urlencodedParser = bodyParser.urlencoded({ extended: false })
app.get('/login.html', function (req, res) {
   res.sendFile( __dirname + "/" + "login.html" );
})
app.post('/login',urlencodedParser,function(req,res){
    let username = req.body.username;
    let password = req.body.password;
    let where = `where account=@account and password=@password`;   //where 条件
    let params = {account:username,password:password};    //传参数
    db.select('user',1,where,params,'',function(err,result){    //db help 里的方法调用
        let msg = ''
        let code = 203
        if (err) {
            msg = err
            code = -1
        } else {
            if(result.recordset.length>0){
                msg = 'success'
                code = 200
            }else{
                msg = 'fail'
                code = 203
            }
        }
        console.log('data :', result)
        res.json({
            code: code,
            data: result.recordset,
            msg: msg
        })
    });
})
var server = app.listen(8081,function(){
    var host = server.address().address;
    var port = server.address().port;
    console.log('http://%s:%s',host,port);
})

4 登录页面-> login.html

<html>
<body>
<form action="http://127.0.0.1:8081/login" method="POST">
Username: <input type="text" name="username">  <br>
 
Password: <input type="password" name="password">
<input type="submit" value="Submit">
</form>
</body>
</html>

5 运行 node login.js

6打开chrome ，输入 http://localhost:8081/login,

输入用户名和密码登录，返回数据库里的验证结果。