Is this a good way to limit the occurrence of OutOfMemoryException?

Question

I have a class that uses the SqlBulkCopy to bulk insert data into a SQL Server database. My original implementation just passed the m_buffer (which is a reference to a class that implements IDataReader) directly into the WriteToServer. Now a new requirement has been added that, upon failure, the process should retry the Bulk Insert command. Why you ask? Because one of our clients get intermittent SQL exceptions because they have crappy hardware.

Okay, I digress, since IDataReader is read-once I needed to come up with something different. I chose to load the IDataReader into a DataTable first and then pass the DataTable into the WriteToServer method. Now my problem is that I will be using more memory and since I do not know anything about the data (the number of columns nor the amount of data in each row) I wanted to attempt to make the code safe from an OutOfMemoryException.

Unfortunately I could not find a good way to determine the amount of available memory, so instead I chose to dump the data every 50,000 rows or when the memory allocation reaches an arbitrary number (1 gig), whichever comes first.

Does this sound like a sound approach or is there a better way?

// m_buffer is a read-once cache (implements IDataReader) that pulls 
// data from an external source as needed so it uses very little memory.
// My original implementation just used m_buffer as the parameter of 
// WriteToServer but now I have to add retry logic into the process.
// The retry logic is in the PutDataIntoDatabase method.

const long MAX_MEMORY_TO_USE = 1073741824;  // 1 gig

DataTable dataTable = new DataTable(m_tableName);
foreach (DataField d in m_buffer.GetColumns())
    dataTable.Columns.Add(new DataColumn(d.FieldName, d.FieldType));

while (m_buffer.Read())
{
    DataRow row = dataTable.NewRow();
    for (int i = 0; i < m_buffer.FieldCount; i++)
        row[i] = m_buffer.GetValue(i);

    dataTable.Rows.Add(row);

    long totalMemory = GC.GetTotalMemory(false);
    if (rowCount++ > 50000 || totalMemory > MAX_MEMORY_TO_USE)
    {
        PutDataIntoDatabase(dataTable);
        dataTable.Clear();
        rowCount = 0;
    }
}

if (dataTable.Rows.Count > 0)
    PutDataIntoDatabase(dataTable);

Answer 1

Based on your comments it looks like your code is actually some sort of library used by different clients in different environments. If that's the case I would rather let clients define the batch size they want rather than writing tricks around memory management on your own.

If you do need to manage the memory - I would suggest to reduce the frequency of GC.GetTotalMemory calls, e.g. check it once per 1000 rows or so. Bear in mind that since your component is not the only one in the application it may turn out that application has already consumed 1GB of RAM thus causing you to reduce the batch size to minimum.