whats the best way to load a large amount of SQL data in .net and generate a CSV file?

i have a sql proc that returns 20,000 + records and want to get this data into a CSV for a SQL2005 bulk load operation.

i think using a data set is overkill since i need only forward only read access to the data.

now i have a data reader but dont think iterating the data-reader is a good idea cause it will lock the oracle DB i am getting the 20,000 records from for some time while its done doing its thing.

logically i am thinking to create a disconnected snapshot of the data in a data table maybe and use that to generate my csv file.

i dont often develop su开发者_如何学Pythonch ETL apps so i wanted to know whats the gold standard on this type of operation.

thoughts?

also, allow me to mention that this needs to be a console app since CORP rules wont allow linked servers and anything cool - so that means SSIS is out.

Since you are worried about doing the iteration of the datareader yourself i could recommend using SqlBulkCopy class.

It lets you load data into an sql server database from any source than can be read with an IDataReader instance

Might solve your potential locking issue.