Easy question:
I have an app that needs to make a half dozen SELECT requests to SQL Server 2005 and write the results to a flat file. That's it.
If I could use .Net 3.5, I'd create a LINQ-To-SQL model, write the LINQ expressions and be done in an hour. What is the next best approach given that I can't use .Net 3.0 or 3.5? Are ADO.NET DataReaders/DataSets the best option, or am I forgetting something else available?
Using the SqlCommand and SqlDataReader classes are your best bet. If you need to write the results to a flat file, you should use the reader directly instead of going to a DataSet, since the latter will load the result in memory before you're able to write it out to a flat file.
The SqlDataReader allows you to read out the data in a streaming fashion, making your app a lot more scalable for this situation.
Agree with Dave Van den Eynde's answer above, but I would say that if you're pushing a large amount of data into these files, and if your app is something that can support it, then it's worth taking a look at making an SSIS package.
Could be complete overkill for this, but it's something that is often overlooked for bulk import/export.
Alternatively, you could avoid writing code and use BCP.exe: http://msdn.microsoft.com/en-us/library/ms162802%28SQL.90%29.aspx
As Nick K so helpfully answered on my SQL Server 2000 question on serverfault, the bcp utility is really handy for this.
You can write a batch file or quick script that call BCP with your queries and have it dump csv,sql direct to a text file!