在ExecuteScalar之后，不会返回SQL Server中受影响的行 [英] Affected rows in SQL Server not being returned after ExecuteScalar

问题描述

我有以下存储过程：

 SET XACT_ABORT ON 
 
 BEGIN TRAN 
 
 INSERT INTO SystemUsers （usr_us_id）
 SELECT us_pk 
 FROM SFSUsers LEFT OUTER JOIN SystemUsers ON us_pk = usr_us_id 
 WHERE usr_us_id IS NULL 
 
 INSERT INTO Addresses（add_usr_id）
 SELECT usr_id 
来自SystemUsers LEFT OUTER JOIN地址ON usr_id = add_usr_id 
 WHERE add_usr_id IS NULL 
 
 COMMIT TRAN 
 
 
 END 

从具有在父类中定义的ExecuteScalar（）方法的业务对象调用...

  public   int  SynchronizeWithSecurity（）
 {
 sqlCommandBuilderApp oSQLCmd =  new  sqlCommandBuilderApp（  SystemUsers_SynchronizeWithSecurity，CommandType.Text）; 
 
  int  numberSynched = Convert.ToInt32（ExecuteScalar（oSQLCmd.SqlCmd））; 
 
  return （ int ）numberSynched; 
} 

即使三个用户记录已同步... ExecuteScalar总是返回0.我甚至尝试在proc结束时使用SELECT @@ ROWCOUNT 。但没有骰子。事务处理是否阻止将受影响的行数返回到调用方法？



谢谢

解决方案

< blockquote>看看这里： SqlCommand .ExecuteScalar方法 [ ^ ]以及 @@ ROWCOUNT [ ^ ]



如果您想要返回受影响的计数记录，您需要以这种方式编写SP：

  DECLARE   @ inserted   INT  
  SET   @ inserted  =  0  
 
  INSERT  .... 
  SET   @ inserted  =  @@ ROWCOUNT  
 
 
  INSERT  ... 
  SET   @ inserted  =  @ inserted  +  @@ ROWCOUNT  
 
  SELECT   @ inserted   AS  ReturnedValue 

I have the following stored procedure:

SET XACT_ABORT ON

BEGIN TRAN

INSERT INTO SystemUsers (usr_us_id)
     SELECT us_pk
       FROM SFSUsers LEFT OUTER JOIN SystemUsers ON us_pk = usr_us_id
      WHERE usr_us_id IS NULL

INSERT INTO Addresses (add_usr_id)
     SELECT usr_id
       FROM SystemUsers LEFT OUTER JOIN Addresses ON usr_id = add_usr_id
      WHERE add_usr_id IS NULL

COMMIT TRAN


END

Being called from a business object which has an ExecuteScalar() method defined in the parent class ...

public int SynchronizeWithSecurity()
{
    sqlCommandBuilderApp oSQLCmd = new sqlCommandBuilderApp("SystemUsers_SynchronizeWithSecurity",CommandType.Text);

    int numberSynched = Convert.ToInt32(ExecuteScalar(oSQLCmd.SqlCmd));

    return (int)numberSynched;
}

Even though three user records were synchronized ... ExecuteScalar always returns 0. I even tried to use SELECT @@ROWCOUNT at the end of the proc. but no dice. Does the Transaction processing block the number of affected rows from being returned to the invoking method?

Thanks

解决方案

Have a look here: SqlCommand.ExecuteScalar Method [^] plus @@ROWCOUNT[^]

If you would like to return the count affected records, you need to write SP in that way:

DECLARE @inserted INT
SET @inserted = 0

INSERT ....
SET @inserted = @@ROWCOUNT


INSERT ...
SET @inserted = @inserted + @@ROWCOUNT 

SELECT @inserted AS ReturnedValue

这篇关于在ExecuteScalar之后，不会返回SQL Server中受影响的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！