Replacing DataTable with disk-backed data source in .NET

Question

I have an application that makes an HTTP request that returns a ton of data in JSON format. Upon receiving the response the application deserializes it into JSON and then turns that into a DataTable object, which is then assigned as the DataSource for a DevExpress.XtraGrid.GridControl object. The problem with this approach is that everything is done in memory and if a very large response is returned lots of memory will be consumed (sometimes >1GB). Therefore, I'm trying to reduce memory consumption.

Reducing memory usage when handling the HTTP response and JSON deserialization seems straightforward—I'll just need to switch to parse the data using streaming rather than consuming giant strings. What I'm not sure about is turning the JSON stream into something that can be consumed by GridControl without it being loaded all into memory.

I'm envisioning storing the data in some temporary file that backs an object I can assign to that DataSource. Our users will be scrolling and filtering through this data rapidly so I want to performance to still be very snappy. In other words, I need to store it in some binary structure that allows for quick reads/scans through the table without having to do much parsing. Something like SQLite could work, except I'd like something simpler. I'm more familiar with *nix than .NET so I don't have a good sense of what .NET options are out there.

What would be a good library/component to use for something like this? According to XtraGrid's data binding methods we have quite a bit of flexibility on what objects we can use for DataSource.

It would be especially nice if the component starts off in-memory and only spills to disk if memory exceeds a threshold.

Answer 1

I think your idea of using SQLite is probably the most efficient way of doing it, but I would suggest using Entity Framework (easy), or a Micro ORM like dapper (fast) to populate it, don't waste your time fiddling with connections.