Updating multiple records in the database

I came to a small problem when I had to update multiple records in the database. Offcourse I can just make a for loop and go to database and update the records. But think about it what if you have 100000 records. Each time going to the database is not a good idea. I came to a solution which only goes to the database once and updates the records. This is not the best solution but it works.

Here is the snippet of the code:

updateTasks is an arraylist which contains the primary keys which are to be updated.

StringBuilder command = new StringBuilder();

for(int i=0;i<=updateTasks.Count-1;i++) { command.Append("UPDATE Tasks SET STATUS = 0 WHERE TaskID="+updateTasks[i]); } the command object is later executed using the SqlCommand instance. I will be posting different methods of performing the same feature in my blog. One other method that I used is using the User Defined Function. You can view the article about using this approach at the following Url: http://www.4guysfromrolla.com/webtech/031004-1.shtml


And the final method that I found was making the use of the XML document. This is the most effiecient method of doing the mutiple inserts, updates and deletes.

Here is the link to the code sample:

http://weblogs.sqlteam.com/travisl/archive/2005/01/04/3931.aspx