SQL ExecuteNonQuery for Multiple rows

Question

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

It's s a little bit more complicated than that. You would need an Stored Procedure who receives a Table as a parameter, and in there do the logic, or the othwr way shoudl be iterate through your rows in c# and send them one by one. It's possible to do bulk inserts, but not bulk insert/update distinctive — Hector Sanchez, Mar 20 '14 at 23:56
we are not using Stored Procedure, I'm looking for a code from C#. — user2219524, Mar 21 '14 at 08:03
Since is query in the application then transform your query... make it a big query who does all the inserts. instead of making execute nonquery multiple times, generate the query with all the Inserts that it will generTe and send it all over to the execute non query — Hector Sanchez, Mar 21 '14 at 15:21
thats a good idea better than calling in loop I think. however any better solution?? — user2219524, Mar 22 '14 at 09:14

developerwjk · Answer 1 · 2014-03-20T23:57:48.800

1

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.

edited Mar 20 '14 at 23:57

answered Mar 20 '14 at 23:45

developerwjk

8,619
2
17
33

I think my question was not clear, i have updated the question please check again. – user2219524 Mar 21 '14 at 08:03
To avoid using a loop, send an XML to a stored procedure, and parse the XML in the stored proc to insert. – developerwjk Mar 21 '14 at 17:52

score 0 · Answer 2 · answered Oct 07 '21 at 09:06

0

for multiple rows , add command in loop

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

answered Oct 07 '21 at 09:06

Halim Bezek

331
3
5

SQL ExecuteNonQuery for Multiple rows

2 Answers2