c# - 多行的 SQL ExecuteNonQuery

Question

我有一个包含以下列的表(“Product_Location”):

ProductID (PK), LocationID (PK), Quantity

我想根据数据表中的行更新数据库中的表。如果行已经存在，则更新数量，否则插入新行。

我有以下更新表中数量的方法，如果 productID 和 LocationID 的组合存在，它只会更新，否则会为该组合插入新行。代码:

 public bool UpdateLocationQuantity(DataSet productLocationData,
                                   SqlTransaction sqlTransaction)
        {
            try
            {
                bool result = true;

                SqlCommand command = new SqlCommand();

                //get the Transaction table which contains rows to update from dataset 
                DataTable table = productLocationData.Tables["Inventory_Transactions"];

                //Create Command Text
                string commandText = @" IF Exists (SELECT * FROM Product_Location PL 
                WHERE ProductID = @ProductID AND LocationID =  @LocationID)
                UPDATE Product_Location SET Quantity = Quantity + @Quantity 
                WHERE ProductID = @ProductID AND LocationID = @LocationID
                ELSE
                INSERT INTO Product_Location (ProductID,LocationID,Quantity) 
                VALUES(@ProductID,@LocationID,@quantity)";
                
                command = new SqlCommand(commandText, this.CurrentConnection);
                command.CommandType = CommandType.Text;
                command.Transaction = sqlTransaction;

                SqlParameterCollection paramCols = command.Parameters;

                //this loop will do the update or insert for all rows in the table
                // How can we optimize to only ONE CALL to database?
                foreach (DataRow row in table.Rows)
                {
                    paramCols.Clear();
                    paramCols.AddWithValue("@ProductID",row["ProductID"]);
                    paramCols.AddWithValue("@LocationID", row["LocationID"]);
                    paramCols.AddWithValue("@Quantity", row["Quantity"]);

                    result &= command.ExecuteNonQuery()>= 0;
                }


                return result;
            }
            catch
            {
                throw;
            }
        }

**我的问题是我们如何优化代码以便只调用一次 ExecuteNonQuery 来更新数据库而不是让它循环？请注意，我们没有使用 StoredProcedure，所有这些都应该来自 C# 和 SQL 查询或事务。

如果只是更新行，我们可以调用 command.Update 并提供源表，它可以轻松更新所有行而无需使用行。但由于我使用的是“IF Exists”，因此我们被迫使用不接受源表作为参数的 ExecuteNonQuery。

谢谢

Answer 1

除了使用 ParameterCollection，您还可以这样做:

 command.Parameters.Add(new SqlParameter("@ProductID", ProductData.PRODUCTID_FIELD));

或

 command.Parameters.AddWithValue("@ProductID", ProductData.PRODUCTID_FIELD);

等等。您实际上不必指定类型。

然后调用:

 int numOfRowsAffected = command.ExecuteNonQuery();

没有要返回的数据集，只有受影响的行数，因为这是一个非查询。

像您一样制作 ParameterCollection 的问题是您需要设置 command.Parameters = paramCols; 但是 command.Parameters 是只读的，所以您不能。也就是说，就分配而言，它是只读的。您只能通过方法 Add 和 AddWithValue 向其添加参数。