javascript - 插入和选择时如何获取查询结果的数量？

Question

我正在尝试获取使用选择和插入查询时受影响的记录数

我已经尝试过插入查询:

recordSet.rowsAffected 

这用于选择查询:

recordSet.length

但它们都不起作用。

这是我的代码:

router.route('/').post((req, res)=> {
var postData = req.body; // get post params
    //  res.json({"message":req.body.firstName});
connection.connect().then(function(){
  var request = new sql.Request(connection);
  request.input('email', sql.VarChar(45), postData.email)
   .input('password', sql.VarChar(45), postData.password)
   .input('category', sql.VarChar(45), postData.category)
   .query('select * from Buyer where password = @password and category = 
@category and email = @email')
   .then((recordSet)=>{
       if (recordSet.length == 0) {
       request.input('buyerId', sql.VarChar(45), uid.v4())
        .input('firstName', sql.VarChar(45), postData.firstName)
        .input('lastName', sql.VarChar(45), postData.lastName)
        .input('email', sql.VarChar(45), postData.email)
        .input('password', sql.VarChar(45), postData.password)
        .input('phoneNumber', sql.VarChar(45), postData.phoneNumber)
        .input('category', sql.VarChar(45), postData.category)
        .query('insert into Buyer(buyer_id, first_name, last_name, email, 
 password, phone_number, category) values(@buyerId, @firstName, @lastName, 
 @email, @password, @phoneNumber, @category)')
        .then((recordSet)=>{
            if(recordSet.rowsAffected == 1){
              res.status(buyerCreatedCode).send("Account Created as 
 Buyer.");
            }else{
              res.status(buyerNotCreatedCode).send("Account not Created as 
 Buyer.");
            }
              connection.close();
            }).catch((err)=>{
              res.json({"error in adding buyer":err});
              connection.close();
            });
          }else{
            res.status(buyerNotCreatedCode).send("This Account already 
 exists.");
            connection.close();
          }
          }).catch((err)=>{
            res.json({"error in checking buyer":err});
            connection.close();
          });
        }).catch((err)=>{
          res.json({"error connection":err});
          connection.close();
        });
      });

所以我希望如果选择查询返回 0 条记录，则买家会被插入到数据库中，如果它返回记录，则不会插入买家(但无论如何都会插入)。

我的问题是如何正确获取受影响的行数以便任务正确执行？提前致谢。

Answer 1

您应该将大部分代码功能移至 SQL 代码中，从中创建一个存储过程，并使用您的值调用它。

例如，您的存储过程可能如下所示:

CREATE PROCEDURE AddBuyer 
    @email varchar(45), -- too small. up to 100
    @password varchar(45),
    @category varchar(45),
    @firstname varchar(45),
    @lastName varchar(45),
    @phoneNumber varchar(45)  -- Couldn't this be smaller?
AS
BEGIN
    DECLARE @buyerId uniqueidentifier
        , @error int

    -- Recommended - return the message and the ID from this procedure

    SELECT @buyerId = BuyerId
    FROM Buyer
    WHERE email = @email
        AND password = @password
        AND category = @category

    IF @@ROWCOUNT > 0
    BEGIN
        -- Return the actual existing ID 
        SELECT @buyerId as BuyerId, 
            'This Account already exists.' as StatusMsg
    END
    ELSE
    BEGIN
        SET @buyerId = NewID()  -- Generate UniqueIdentifier
        insert into Buyer(
            buyer_id, 
            first_name, 
            last_name, 
            email, 
            password, 
            phone_number, 
            category
        ) 
        values(
            @buyerId, 
            @firstName, 
            @lastName, 
            @email, 
            @password, 
            @phoneNumber, 
            @category
        );

        SET @error = @@ERROR
        IF @error > 0
        BEGIN
            SELECT CAST(NULL as uniqueidentifier) as BuyerId, 
                'Account not Created as Buyer. (Status=' + Cast(@error as varchar(10)) + ')' as StatusMsg
        END
        ELSE
        BEGIN
            SELECT @buyerId as BuyerId,
                'Account Created as Buyer.' As StatusMsg
        END
    END 
END

当您执行此操作时，您将返回一行记录集。它由 BuyerId 值(出现错误时为 NULL)和相应的消息文本组成。

变体包括返回 OUTPUT 变量中的值、TRY...CATCH 错误捕获等。