0

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?

Robert Claypool
  • 4,262
  • 9
  • 50
  • 61
  • Thanks everyone for the non-code suggestions too. My "flat file" is actually an ESRI Shapefile (so code is needed for that part), but BCP and SSIS are good to know about. – Robert Claypool Oct 02 '09 at 05:44

4 Answers4

4

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.

Dave Van den Eynde
  • 17,020
  • 7
  • 59
  • 90
1

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!

Community
  • 1
  • 1
adric
  • 161
  • 4
0

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.

Steven Robbins
  • 26,441
  • 7
  • 76
  • 90
0

Alternatively, you could avoid writing code and use BCP.exe: http://msdn.microsoft.com/en-us/library/ms162802(SQL.90).aspx

Brannon
  • 25,687
  • 5
  • 39
  • 44