What the best way to remove records in bulk via .net

What criteria would you use to delete rows and where would that criteria come from?

I'm doing a bulk remove feature - so i give my code a csv of ids to remove - and then code deletes them in turn or in bulk?

if your application uses a TableAdapter (or DataAdapter), the adapter's Update method will delete rows in the database that have a RowState of Deleted. call delete method of data row to be deleted and once you have all the rows deleted just call update() on the adapter. this will delete the rows from server  

you can write a program which will read the record Ids from the CSV and mark the records as deleted in the adapter then run update()

one more dirty way is to contruct a SQL command like 'Delete from ABC where Id in ['a','b','c',.......] and sending the same to the SQL server but this will be slow if your table contains large amoutn of records and no index on ID.

Yet another not so perfect method is to loop through the IDs and keep executing SQLs in the loop.

Thanks udayakumarlm and CodeCruiser: - however adding the the where clause wouldnt work for me sorry as were talking about 100s of thousands, and i'm worried about the overhead on using the adapter approach - as this is fine for small numbers but not large.

So one possiblility is that i do it in batches, or another is yes like CodeCruiser said i do it one line at a time. So far my plan b was always to do it one at a time e.g. 500,000 individual deletes - however there must be a better way that this - as im worried about performance for all the above?

one more solution may be to store all your IDs in a Table on the Server and deleting records from the source server like

delete from MainTable
where id not in (select id from temptable)

this will be faster

For deletes you obviously need to specify some criteria unlike inserts. This criteria would keep changing for each row in its based on ID. If the ID column on the server is Indexed then it should not be a big overhead.
The approach mentioned of creating another table and using that would add overhead of its own when you first insert all the IDs to the database and then delete based on them.

ok.. the simplest way of doing this with the most minial amount of code is to create a temp table on the SQL side of things..

First, do a bulk load into the temp table with all of the records you want to delete (basically an import).  Then use the following command to delete all from the main table.

"delete from tbl_Main where id in (select id from tbl_Temp)"

then delete all records in the temp table..

"truncate table tbl_Temp"

Hope this helps,

G.

Can i do this by using a temp table as a variable in stored procedure say, Or will i literally have to create a new table in db?