In .NET, Streaming data from SQL to a file

Question

I have to get a DataTable from SQL, and then convert it to XML (I can't use SQL XML) and save to a file. The problem is that the file is going to be 5 GB, and I don't have enough memory to download it all and convert it to XML all at once in memory. I know I should be able to use a DataReader to convert and push the data to the file stream in chunks, but I don't know how to really do this. How can I do the following?

1. Connect to the SQL server asynchronously
2. Call the stored procedure asynchronously
3. Read data from the DataReader asynchronously
4. Convert data from the DataReader to XML and save to file asynchronously

This all has to be asynchronous because it is on a server processing multiple requests and we can't have a thread blocking for each one.

Answer 1

What you need is an XmlWriter

HTH
Kev

Answer 2

Why do you need to do anything asynchronously? That may perform better, but will be a lot more complicated.

The simple way of doing it:

• Execute the query to get a DataReader
• Create an XmlWriter writing to the file
• Try to read a row
  • If there are no more rows left, you're done. (Make sure you close the writer/connection etc)
  • If you do get a row, write it to the XmlWriter (add an element or whatever you need to do).
• Go back to the previous step.