CODEWITHSUNDEEP
c#asp.netasp.net-mvcasp.net-mvc-4c#-4.0
User987
User987

Reputation: 3825

SQLCommand update query in batches

I have written a code like following:

 for (int i = 0; i < ListToUpdate.Count; i = i + 500)
 {
   SqlCommand command = new SqlCommand();
   command.Connection = new SqlConnection("Data Source=.;Initial Catalog=mydb;Integrated Security=SSPI");
   command.Connection.Open();
   var batchList = ListToUpdate.Skip(i).Take(500);
   for (int j = 0; j < batchList.Count(); j++)
   {
      command.CommandText += string.Format("update mytable set column=@s_id{0} where columnid = @id{0};", j);
      command.Parameters.AddWithValue("@s_id" + j, ListToUpdate[j].QuantitySoldTotal);
      command.Parameters.AddWithValue("@id" + j, ListToUpdate[j].ItemId);
   }
   command.ExecuteNonQuery();
   command.Connection.Close();
 }

This should perform update of my table in batches, but the weird thing is the query gets executed only the first time...

If there are 1500 items in list, only the first batch get updated... And the others aren't ...

What am I doing wrong here ???

Upvotes: 3

Views: 978

Answers (3)

Sajal
Sajal

Reputation: 4401

Do you have to close and reopen connection every time you iterate? Once, you have the connection open, just create a new sql command text after each batch update using 

command.CommandText = CommandType.Text;

I'd suggest this - 

SqlCommand command = new SqlCommand();
command.Connection = new SqlConnection("Data Source=.;Initial Catalog=mydb;Integrated Security=SSPI");
command.Connection.Open();

for (int i = 0; i < ListToUpdate.Count; i = i + 500) {
    var batchList = ListToUpdate.Skip(i).Take(500);
    for (int j = 0; j < batchList.Count(); j++) {
        command.CommandText += string.Format("update mytable set column=@s_id{0} where columnid = @id{0};", j);
        command.Parameters.AddWithValue("@s_id" + j, batchList[j].QuantitySoldTotal);
        command.Parameters.AddWithValue("@id" + j, batchList[j].ItemId)
    }
    command.ExecuteNonQuery();
    command.CommandText = CommandType.Text;
}
command.Connection.Close();

Select properties from the new list generated such as batchList[j].

Upvotes: 2

Pravin Tukadiya
Pravin Tukadiya

Reputation: 487

I think you need to use batchList.

for (int i = 0; i < ListToUpdate.Count; i = i + 500)
{
  SqlCommand command = new SqlCommand();
  command.Connection = new SqlConnection("Data Source=.;Initial 
  Catalog=mydb;Integrated Security=SSPI");
  command.Connection.Open();
  var batchList = ListToUpdate.Skip(i).Take(500);
  for (int j = 0; j < batchList.Count(); j++)
  {
       command.CommandText += string.Format("update mytable set 
       column=@s_id{0} where columnid = @id{0};", j);
       command.Parameters.AddWithValue("@s_id" + j, 
       batchList[j].QuantitySoldTotal);
       command.Parameters.AddWithValue("@id" + j, batchList[j].ItemId);
       command.ExecuteNonQuery();
  }

  command.Connection.Close();
}

Upvotes: 2

Biswabid
Biswabid

Reputation: 1409

I think the placement of command.ExecuteNonQuery(); is not correct

please check the below code , if it helps

for (int i = 0; i < ListToUpdate.Count; i = i + 500)
 {
   SqlCommand command = new SqlCommand();
   command.Connection = new SqlConnection("Data Source=.;Initial Catalog=mydb;Integrated Security=SSPI");
   command.Connection.Open();
   var batchList = ListToUpdate.Skip(i).Take(500);
   for (int j = 0; j < batchList.Count(); j++)
   {
   command.CommandText += string.Format("update mytable set column=@s_id{0} where columnid = @id{0};", j);
   command.Parameters.AddWithValue("@s_id" + j, ListToUpdate[j].QuantitySoldTotal);
   command.Parameters.AddWithValue("@id" + j, ListToUpdate[j].ItemId);


   command.ExecuteNonQuery();
   }
   command.Connection.Close();
 }

Upvotes: 1

Related Questions