CODEWITHSUNDEEP
c#sqlif-statementexistssqlcommand
user2219524
user2219524

Reputation: 21

SQL ExecuteNonQuery for Multiple rows

I have a table("Product_Location") with the following columns:

ProductID (PK), LocationID (PK), Quantity

i would like to update the table in the database from rows in a datatable. if row already exists then Update quantity otherwise Insert new row.

i have the following method which update the quantity in the table, if the combination of productID and LocationID exists, it just update otherwise insert new row for that combination. code:

 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;
            }
        }

**My question is how we can optimize the code so only one call to ExecuteNonQuery to update the database instead of having it in a loop? Please note that we are not using StoredProcedure and all should be from C# and SQL Queries or Transactions.

if it was just Update the rows, we could call command.Update with providing the source table and it easily update all the rows without using rows. but since i am using 'IF Exists' then we are forced to use ExecuteNonQuery which is not accepting source table as parameter.

Thank You

Upvotes: 2

Views: 2865

Answers (2)

Halim Bezek
Halim Bezek

Reputation: 1043

for multiple rows , add command in loop

 foreach (DataRow row in table.Rows)
 {
   SqlCommand command = new SqlCommand();
   .
   .
   .
}

Upvotes: 0

developerwjk
developerwjk

Reputation: 8659

Instead of using a ParameterCollection you could do:

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

or

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

and so on. You don't actually have to specify the type.

Then call:

 int numOfRowsAffected = command.ExecuteNonQuery();

There is no dataset to be returned, only the number of rows affected, since this is a non-query.

The problem with making a ParameterCollection like you are doing is you then need to set command.Parameters = paramCols; but command.Parameters is Read-Only, so you can't. That is, its read-only as far as assignment goes. You can only add parameters to it through the methods Add and AddWithValue.

Upvotes: 1

Related Questions