Updating large number of rows

I have a large number of rows in a SQL Server table. I need to select all those rows and run an update query for each of those rows. I need to know what's the best option do it from the following

1. run a select query and get a DataTable and use the following code in the application

   foreach(DataRow it开发者_开发知识库em in DataTable.Rows) { //perform update }

2. in the database level use a stored procedure, select the set of data and use SQL Server cursor to perform the update

Option 1 vs option 2 means you work with disconnected dataset vs connected data readers.

As discussed other times here in SO in fact this means more memory needs at once on the client vs a connection kept open longer and smaller chunk of data transmitted more often while looping on results. Since your main focus is on the update of data i think both options are probably similar and if you have many records i would probably go for the second one, using datareader, to do not have to load all those records at once in a dataset.

As others already pointed out the best performances would be achieved having a set based update stored procedure to which you would pass certain parameters and all records are atomically updated at once. Or also have a look at SqlBulk updates.

i have also one suggestion

foreach(DataRow item in DataTable.Rows) { 
    //perform update 
} 

here you can use LINQ .so it can give fast response

Like Marc already commented, go for option 3, and do a set based update if at all possible (and usually it is). If you think it isn't, maybe you could ask a separate question on how to do that. The more specifics you will give, the better the proposed solutions will fit your situation