It is possible to stream a large SQL Server database result set using Dapper?

c# dapper sql-server stream

Question

I have about 500K rows I need to return from my database (please don't ask why :/ ).

I will then need to save these results as XML (more URGH :( ) and the ftp this file to somewhere magical.

I also need to transform the each row in the result set.

Right now, this is what I'm doing with say .. TOP 100 results:

  • using Dapper's Query<T> method, which throws the entire result set into memory
  • I then use AutoMapper to convert the database POCO to my FileResult POCO
  • Convert to XML
  • Then save this collection to the file system
  • Then FTP

This works fine for 100 rows, but I get an Out Of Memory exception with AutoMapper when trying to convert the 500K results to a new collection.

So, I was wondering if I could do this...

  • Stream data from DB using Dapper
  • For each row, automapper it
  • Convert to XML
  • Stream result to disk
  • <repeat for each row>
  • Now ftp that file to magic-land

I'm trying to stop throwing everything into RAM. My thinking is that if I can stream stuff, it's more memory efficient as I only work on a single result set of data.

Can anyone help?

Expert Answer

using Dapper's Query<T> method, which throws the entire result set into memory

It is a good job, then, that one of the optional parameters is a bool that lets you choose whether to buffer or not ;p

Just add , buffer: false to your existing call to Query<T>.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why