Read large strings from the database without fragmenting large heaps of objects


I have data database containing some rather large strings, each of which holds a serialized hierarchical data collection (data is stored as strings rather than as a binary stream to allow interaction with VB6). From what I can tell, any database query that returns a string over 85,000 bytes will immediately throw that string onto the Large Object Heap. If the strings are immediately going to be split into smaller pieces, and thus the large objects will be short-lived, is there any way to avoid having those objects go on the Large Object Heap and remain there uselessly until the next LOH collection? I keep reading that LOH objects should be reused, but I have no idea how I would go about doing that in this context.

EDIT--I'm using the SqlClient object with a DataReader.

You can't reuse strings, because they are immutable.

What you can do is either stream the data from the database (example for ADO), which means you can avoid LOH completely.

Or you can load your column into a char array (or a byte array), which can be reused, as long as it is big enough.

This all depends on what database provider or ORM are you using, though.