使用Node.js和Tedious插入多条记录

I have an array of customer objects, that I wish to insert to the SQL database. The customer objects are retrieved from the req data.

我有一组客户对象,我希望将其插入到SQL数据库中。从req数据中检索客户对象。

I am using Tedious for the request, and Tedious Connectionpool in order to have multiple connections at the same time.

我正在使用Tedious来处理请求,而Tedious Connectionpool则是为了同时拥有多个连接。

When looping over the objects i am getting an error when trying to insert, the error being

在循环对象时,我在尝试插入时遇到错误,错误是

{ [RequestError: Violation of PRIMARY KEY constraint `'PK__Customer__A4AE64D873A5400C'. Cannot insert duplicate key in object 'dbo.Customer'. The duplicate key value is (2).]`

Note that I only have 3 object being send in the req at this time. It looks to me that it is only the last object that are being handled and inserted. But since I am new to using tedious with Node.js i cant spot my mistake. Any suggestions ?

请注意,此时我只在req中发送了3个对象。在我看来,它只是被处理和插入的最后一个对象。但由于我不习惯使用Node.js,我无法发现我的错误。有什么建议么 ?

router.post('/',jsonParser, function(req, res) {


var customers = req.body.customers;
var companies = req.body.companies;

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
var config = {
    userName: '*************',
    password: '*************',
    server: '***********.database.windows.net',
    // When you connect to Azure SQL Database, you need these next options.
    options: {encrypt: true, database: '*******'}
};

var poolConfig = {
    min: 1,
    max: 3,
    log: true
};

var pool = new ConnectionPool(poolConfig, config);

    for (var i = 0; i < customers.length; i++) {

        console.log('Inserting '+customers[i].firstname);
        var firstname = customers[i].firstname;
        var count = i;


        pool.acquire(function (err, connection) {
            if (err)
                console.error(err);



            //use the connection as normal
            var request = new Request("INSERT INTO dbo.Customer" +
                " (Firstname,CustomerId)" +
                "VALUES" +
                " (@Firstname,@CustomerId);", function (err, rowCount) {
                if (err)
                    console.error(err);

                console.log('rowCount: ' + rowCount);


                //release the connection back to the pool when finished
                connection.release();
            });

            request.addParameter('Firstname', TYPES.VarChar,firstname);
            request.addParameter('CustomerId', TYPES.Int, count);


            request.on('row', function (columns) {
                console.log('value: ' + columns[0].value);
            });

            connection.execSql(request);
        });

        pool.on('error', function (err) {
            console.error(err);
        });
    }
});

1 个解决方案

#1

The scope of your variables count and firstName are global. By the time the pool.acquire( function get's executed the for loop has completed and it is inserting the last customer twice. One possible solution would be to put an anonymous function inside the for loop e.g. (it doesn't have to be anonymous though)

变量count和firstName的范围是全局的。当pool.acquire(函数get执行for循环已经完成并且它正在插入最后一个客户两次。一个可能的解决方案是将一个匿名函数放在for循环中,例如(虽然它不必是匿名的) )

for (var i = 0; i < customers.length; i++) {
    (function(count, firstName) {
       ...do insert here...
    }(i, customers[i].firstname));
}