.net - ExecuteScalar 函数失败。必须声明标量变量“@DeviceId”

Question

我有一个接收对象列表的控制器。首先，它使用 ExecuteScalar 检查表中是否已经存在这些对象。如果行已经存在，我们运行更新存储过程。如果没有，我们运行 Insert 存储过程。但是，我的代码在执行标量函数处失败，该函数告诉我们“必须声明标量变量@DeviceId”。我不确定错误消息是指查询还是 Sql 参数。

 public BaseResponse Post([FromBody]List<PendingAttachment> pendingAttachmentRequest)
    {
        PendingAttachment pendingAttachment = new PendingAttachment();
        List<SqlParameter> sqlParameters = new List<SqlParameter>() 
        {
            new SqlParameter("@Datasource", pendingAttachment.DataSource),
            new SqlParameter("@LastUpdated", pendingAttachment.LastUpdated),
            new SqlParameter("@PendingCount", pendingAttachment.PendingCount),
            new SqlParameter("@DeviceId", pendingAttachment.DeviceId),
            new SqlParameter("@Username", pendingAttachment.UserName),
        };
        try
        {           
            RequestHeaders headers = new RequestHeaders();
            var query = "SELECT count(*) FROM PendingAttachments WHERE DeviceId = @DeviceId AND UserName = @UserName";
            using (var onbaseConnection = MobileCompleteServer.Helpers.OnbaseAuth.Connect(headers))
            {
                var connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];

                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand comm = new SqlCommand(query, sqlConnection))
                    {
                        comm.CommandType = System.Data.CommandType.Text;
                        foreach(PendingAttachment attachment in pendingAttachmentRequest)
                        {
                            
                            comm.Parameters.AddRange(sqlParameters.ToArray());
                            comm.Parameters.Clear();
                        }
                        int rowCount = (int)comm.ExecuteScalar();   //FAILS HERE
                        if (rowCount > 0)
                        {
                            using (SqlCommand sqlComm = new SqlCommand("sp_UpdatePendingAttachments", sqlConnection))
                            {
                                sqlComm.CommandType = System.Data.CommandType.StoredProcedure;
                                foreach (PendingAttachment attachment in pendingAttachmentRequest)
                                {
                                    
                                    sqlComm.Parameters.AddRange(sqlParameters.ToArray());
                                    sqlComm.Parameters.Clear();
                                }
                                sqlComm.ExecuteNonQuery();
                            }
                        }
                        else
                        {
                            using (SqlCommand sqlCommand = new SqlCommand("sp_InsertPendingAttachments", sqlConnection))
                            {
                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                foreach(PendingAttachment attachment in pendingAttachmentRequest)
                                {
                                    
                                    sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
                                    sqlCommand.Parameters.Clear();
                                }
                                sqlCommand.ExecuteNonQuery();
                            }
                        }
                    }
                }
                return new BaseResponse();
            }
        }
        catch (Exception ex)
        {
                return new BaseResponse
                {
                    Exception = ErrorCodes.Get(ex, ErrorCodes.PendingAttachmentError),
                    ExceptionStackTrace = ex.ToString()
                };
            

        }
    }
}

标签： .netsqlparameterexecutescalar