c# - 列表参数的对象名称在 ForEach 循环之外以 @ 为前缀

问题描述

在传递给存储过程之前，所有参数都将以@ 为前缀。即，在我的 foreach 循环区域之外，从第二次迭代开始，所有参数名称都将以 @ 为前缀，并在调用存储过程时从第二次迭代中获取异常消息

{"过程或函数 UspUpdateProctorSignalByScheduleUserID 指定的参数太多。"}

这是如下所示的片段的部分代码：

[WebMethod]
public static List<OnlineProctor> UpdateAllProctorStatus(ProctoringScheduledUserInfoList scheduledUsersList)
{
    #region Outside of foreach loop parameter's name will be prefixed to @ from the second iteration of the loop
        List<object> parameters = new List<object>();

        parameters.Add(SqlHelper.BuildSqlParameter("Status", SqlDbType.VarChar, 10, "status", null, ParameterDirection.Output));

        if (proctorStatus == "PAUSE")
            parameters.Add(SqlHelper.BuildSqlParameter("ScheduleExtensionTime", SqlDbType.Int, sizeof(Int16), "ScheduleExtensionTime", scheduledUsersList.PauseTime));

        parameters.Add(SqlHelper.BuildSqlParameter("ProctorSignal", SqlDbType.TinyInt, sizeof(Int16), "ProctorSignal", scheduledUsersList.ProctorSignal));

    #endregion

    foreach (Int64 scheduleUserID in scheduledUsersList.ScheduleUserID)
    {
        parameters.Add(SqlHelper.BuildSqlParameter("ScheduleDetailUserID", SqlDbType.BigInt, sizeof(Int64), "ScheduleDetailUserID", scheduleUserID));

        proctoringUserListDT = SqlHelper.ExecuteDataTable(CommandType.StoredProcedure, "UspUpdateProctorSignalByScheduleUserID", parameters.ToArray(), false);

        if (proctorStatus == "PAUSE")
            parameters.RemoveAt(3);
        else
            parameters.RemoveAt(2);
    }

}

实际结果：

一个。对于第 I 次迭代

parameters | Count = 4
[0] {Status}
            Value   null

[1] {ScheduleExtensionTime}
            Value   0

[2] {ProctorSignal}
            Value   1

[3] {ScheduleDetailUserID}
        Value  943417

declare @p1 varchar(10)
set @p1='S001'
exec UspUpdateProctorSignalByScheduleUserID @Status=@p1 output,@ScheduleExtensionTime=0,@ProctorSignal=1,@ScheduleDetailUserID=943417
select @p1

湾。对于第二次迭代

parameters | Count = 4
[0] {@Status}
        Value   "S001"

[1] {@ScheduleExtensionTime}
        Value   0

[2] {@ProctorSignal}
        Value   1

[3] {ScheduleDetailUserID}
        Value  943419


declare @p1 varchar(10)
set @p1=NULL
exec UspUpdateProctorSignalByScheduleUserID @@Status=@p1 output,@@ScheduleExtensionTime=0,@@ProctorSignal=1,@ScheduleDetailUserID=943419
select @p1

预期结果：

湾。对于第二次迭代

parameters | Count = 4
[0] {Status}
        Value   null

[1] {ScheduleExtensionTime}
        Value   0

[2] {ProctorSignal}
        Value   1

[3] {ScheduleDetailUserID}
        Value  943419


declare @p1 varchar(10)
set @p1='S001'
exec UspUpdateProctorSignalByScheduleUserID @Status=@p1 output,@ScheduleExtensionTime=0,@ProctorSignal=1,@ScheduleDetailUserID=943419
select @p1

这里的属性从第二次迭代开始改变

> ParameterName = "@Status" 
> ParameterNameFixed = "@Status"
> _parameterName = "@Status"

在 foreach 循环中声明和初始化时，相同的片段可以完美地工作。

但在 foreach 循环中，条件if (proctorStatus == "PAUSE")检查是否删除parameters.RemoveAt(3);特定索引 3 处的列表。

是否可以使用LINQorLambda运算符编写相同的条件，即通过ScheduleDetailUserID在循环的每次迭代中删除特定的对象？

用 SP 更新：

ALTER PROC [dbo].[UspUpdateProctorSignalByScheduleUserID]
(
  @ScheduleDetailUserID BIGINT,
  @ProctorSignal TINYINT,
  @Status VARCHAR(5)='' OUTPUT,
  @ScheduleExtensionTime INT=0
)
--WITH ENCRYPTION     
AS
BEGIN
SET NOCOUNT ON;
    BEGIN TRY 
        /*  ---#8*/
        DECLARE @UserTestStatus TINYINT

        SELECT @UserTestStatus = [Status]  
        FROM ScheduleUserDetail 
        WHERE ScheduleDetailUserID = @ScheduleDetailUserID

        IF @UserTestStatus = 2 --#14
        BEGIN
            SET @Status='S004'
            RETURN
        END --#14

        IF @UserTestStatus < 2  --#9
            BEGIN
                IF @ProctorSignal=2
                    BEGIN
                        UPDATE ScheduleUserDetail SET ProctorSignal=@ProctorSignal,ScheduleExtensionTime=(CASE WHEN ISNULL(@ScheduleExtensionTime,0)>0 THEN @ScheduleExtensionTime END), --#7
                        [Status]=@ProctorSignal
                        WHERE ScheduleDetailUserID=@ScheduleDetailUserID
                        SET @Status='S001'--Updated SuccessFully
                        SET @UserTestStatus = @ProctorSignal
                    END
                ELSE
                    BEGIN
                        UPDATE ScheduleUserDetail SET ProctorSignal=@ProctorSignal,ScheduleExtensionTime=(CASE WHEN ISNULL(@ScheduleExtensionTime,0)>0 THEN @ScheduleExtensionTime END) --#7
                        WHERE ScheduleDetailUserID=@ScheduleDetailUserID
                        SET @Status='S001'--Updated SuccessFully
                    END
            END

        IF @Status='S001'
            BEGIN
                SELECT LoginName AS StudentName,@ProctorSignal AS ProctorSignal,
                (
                    CASE    WHEN @UserTestStatus = 2 AND @ProctorSignal=2 THEN 5 --Suspended #14
                            WHEN @UserTestStatus = 1 AND @ProctorSignal=1 THEN 6 --Paused #14
                            ELSE SUD.[Status]
                    END
                ) AS TestStatus  --#10  --#11
                FROM [User] U
                INNER JOIN ScheduleUser SU ON SU.UserID=U.UserID
                INNER JOIN ScheduleUserDetail SUD ON SUD.ScheduleDetailUserID=SU.ID
                WHERE SU.ID=@ScheduleDetailUserID AND U.IsDeleted=0 AND SU.IsDeleted=0
            END
    END TRY
    BEGIN CATCH
        SET @Status='S002'--Update Failed
        DECLARE @ErrorDetail AS VARCHAR(MAX)
        SET  @Errordetail ='EXEC dbo.UspUpdateProctorSignalByScheduleUserID @ScheduleDetailUserID='+CAST(ISNULL(@ScheduleDetailUserID,'') AS NVARCHAR(MAX))+',@ProctorSignal='+CAST(ISNULL(@ProctorSignal,'') AS NVARCHAR(MAX))+',@Status='+CAST(ISNULL(@Status,'') AS NVARCHAR(MAX))+',@ScheduleExtensionTime='+CAST(ISNULL(@ScheduleExtensionTime,'') AS NVARCHAR(MAX))
        EXEC [GenerateErrorHandling] @ErrorDetail
        DECLARE @Exception AS NVARCHAR(MAX)  
        SET @Exception=ERROR_MESSAGE() +'-> '+  @ErrorDetail 
        RAISERROR (@Exception, 16, 1)
    END CATCH
SET NOCOUNT OFF;
END

标签： c#asp.netlistlinq

c# - 列表参数的对象名称在 ForEach 循环之外以 @ 为前缀

问题描述

解决方案

推荐阅读