Databases
StackOverflow2013
Postsdbo
POC# DataTable (DataRowCollection) stored in a temporary file, not memory?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POC# DataTable (DataRowCollection) stored in a temporary file, not memory?
    primarykey
    data
    text
    <p>I would like to replace a DataTable with a custom class that implements DataRowCollection by storing the rows in a temporary data file instead of keeping them in memory.</p> <p>I understand that this will be slow compared to in-memory tables, but I occasionally need to work with tables that simply will not fit in ram (> 4GB of data). I will discard the table and delete the temporary file at the end of the run.</p> <p>The table data is coming from a database query. I know that I can change queries to reduce the size of the data set I get back. That is not the point. The point is there will always be some limit on memory and I would like to have the option of using a slow temporary file rather than just saying "you can't do that".</p> <p>Is there a pre-written class or method of doing this? It seems like I am reinventing the wheel here...</p> <p>Here is my skeletal start:</p> <pre><code>/// &lt;summary&gt; /// like DataTable, but storing data in a file instead of memory /// &lt;/summary&gt; public class FileBackedDataTable : DataTable, IIntegrationTest { new public FileBackedDataRowCollection Rows = null; // Summary: // Initializes a new instance of the System.Data.DataTable class with no arguments. public FileBackedDataTable() { Rows = new FileBackedDataRowCollection(this); } } /// &lt;summary&gt; /// like a DataRowCollection but data is stored in a file, not in memory /// &lt;/summary&gt; public class FileBackedDataRowCollection : ICollection, IEnumerable, IDisposable { /// &lt;summary&gt; /// internally track each file record /// &lt;/summary&gt; class recordInfo { public long recordPosition; public int recordLength; public int recordMaxLength; public long hash; } DataTable table; ArrayList rows = new ArrayList(); public FileBackedDataRowCollection(DataTable table) { this.table = table; openBackingFile(table); } public int Count { get { return rows.Count; } } public void Clear() { rows.Clear(); truncateBackingFile(); } public DataRow this[int index] { get { recordInfo info = (recordInfo)rows[index]; return readRow(info); } set { writeRow(index, value); } } private void writeRow(int index, DataRow value) { byte[] bytes = rowToBytes(value); recordInfo info = (recordInfo)rows[index]; if (bytes.Length &lt;= info.recordMaxLength) { info.recordLength = bytes.Length; info.hash = value.GetHashCode(); writeBytes(info.recordPosition, bytes); } else { rows[index] = appendRow(bytes, value.GetHashCode()); } } private DataRow readRow(recordInfo recordInfo) { byte[] bytes = readBytes(recordInfo.recordPosition, recordInfo.recordLength); DataRow row = bytesToRow(bytes); return row; } public void Add(DataRow r) { byte[] bytes = rowToBytes(r); recordInfo info = appendRow(bytes, r.GetHashCode()); rows.Add(info); } private recordInfo appendRow(byte[] bytes, long hash) { recordInfo info = new recordInfo(); info.recordLength = bytes.Length; info.recordMaxLength = info.recordLength; info.recordPosition = appendBytes(bytes); info.hash = hash; return info; } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. UShsmiths
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POC# DataTable (DataRowCollection) stored in a temporary file, not memory?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POC# DataTable (DataRowCollection) stored in a temporary file, not memory?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POC# DataTable (DataRowCollection) stored in a temporary file, not memory?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI have no idea what you're doing but I really think `PAGING` is the solution to your problem
      singulars
      1. POC# DataTable (DataRowCollection) stored in a temporary file, not memory?
      1. USChris Marisic
    2. COAre you doing something with the DataTable that you can do in the database? It is also possible to query the database for a specific range of rows so you don't need to keep the whole table in memory.
      singulars
      1. POC# DataTable (DataRowCollection) stored in a temporary file, not memory?
      1. USMagnus
    3. CODo you need to process the rows one at a time, or do they affect each other? If you can process each one by itself, use a DataReader to get each row one at a time - very fast, very little memory footprint.
      singulars
      1. POC# DataTable (DataRowCollection) stored in a temporary file, not memory?
      1. USdavisoa
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload