使用节点 js 和 mssql 插入请求参数

Question

我在尝试使用以下代码创建插入语句时遇到问题，我试图分离请求正文参数，但也不起作用：

//Initiallising node modules
var express = require('express');
var bodyPasrser = require('body-parser');
var sql = require('mssql');
var app = express();

//Body Parser to parse to JSON
app.use(bodyPasrser.json());

//CORS Middleware
app.use(function(req,res,next){
    res.header("Access-Control-Allow-Origin","*");
    res.header("Access-Control-Allow-Methods","GET,HEAD,POST,PUT,OPTIONS");
    res.header("Access-Control-Allow-Headers","Origin,X-Requested-With,contentType,Content-Type,Accept,Authorization");
    next();
});

//Setting up server
var server = app.listen(process.env.PORT||8080,function(){
    var port = server.address().port;
    console.log("App now running on port ",port);
});

//setup database connection
var dbconfig = {
    user:"sa",
    password:"...",
    server : "localhost",
    database: "supermarket"  
};

// ConnectionPool

//connect to the database 
var executeQuery = function(res,query){
    sql.connect(dbconfig,function(err){
        if(err){
            console.log("there is a database connection error -> "+err);
            res.send(err);
        }
        else{
            // create request object
            var request = new sql.Request();
            // query to the database
            request.query(query,function(err,result){
                if(err){
                    console.log("error while querying database -> "+err);
                    res.send(err);
                }
                else{
                    res.send(result);
                    sql.close();
                }
            });
        }
    });
}

//POST API
app.post("/api/Category", function(req , res){
    var query = "INSERT INTO [Category] (CName,CSubCategory) VALUES (req.body.CName,req.body.CSubCategory)";
    executeQuery (res, query);
});

通过邮递员测试时显示的错误如下所示：

"无法绑定多部分标识符 \"req.body.CSubCategory\"。

Answer 1

你直接在字符串中访问 req.body.CName，这是行不通的，你需要为查询使用参数：

// Change execute query to accept parameters.
var executeQuery = function(res,query,parameters){
    sql.connect(dbconfig,function(err){
        if(err){
            console.log("there is a database connection error -> "+err);
            res.send(err);
        }
        else{
            // create request object
            var request = new sql.Request();
            // Add parameters
            parameters.forEach(function(p) {
                request.input(p.name, p.sqltype, p.value);
            });
            // query to the database
            request.query(query,function(err,result){
                if(err){
                    console.log("error while querying database -> "+err);
                    res.send(err);
                }
                else{
                    res.send(result);
                    sql.close();
                }
            });
        }
    });
}

//POST API
app.post("/api/Category", function(req , res){
    var parameters = [
      { name: 'CName', sqltype: sql.NVarChar, value: req.body.CName},
      { name: 'CSubCategory', sqltype: sql.NVarChar,  value: req.body.CSubCategory},
    ];
    var query = "INSERT INTO [Category] (CName,CSubCategory) VALUES (@CName, @CSubCategory)";
    executeQuery (res, query, parameters);
});