System.OutOfMemoryException when writing a csv file

[K Safvi]

Dear All, In download data module of my application(with asp.net2.0/sqlserver2005)one of the function is returning a datareader object having 7 hundererd thousands plus record. It returns the datareader without an exception but when i am iterating through the datareader to take its values to a string bulider type varible to write a csv file, it throws an 'System.OutOfMemoryException' due to huge records. I can not filter the record as it is required to write a csv file of the whole data. how can i avoide this error and improve the efficency of the system so that it takes less resources. The compilation debug is set to false. Also when there a few hundered thousand records the download is working ok. Thanks in advance for the help form any esteemed member. Thanks, Safvi

[K Safvi]

public static string WriteCSVFilebyDataReader(SqlDataReader SqlDataReaderObject) { StringBuilder strResult = new StringBuilder(); try { for (int i = 0; i < SqlDataReaderObject.FieldCount; i++) { strResult.Append("\" " + SqlDataReaderObject.GetName(i) + " \","); } strResult.Append("\n"); if (SqlDataReaderObject.HasRows) { while (SqlDataReaderObject.Read()) { for (int j = 0; j < SqlDataReaderObject.FieldCount; j++) { strResult.Append("\"" +SqlDataReaderObject.GetValue(j).ToString() + "\","); } strResult.Append("\n"); //SqlDataReaderObject.NextResult(); } } } catch (Exception ex) { throw ex; } return strResult.ToString(); }

[Lost User]

When you use a datareader, data is not fetched from the database until you use the Read() method to read the next row, therefore you may not receive an error when you open the datareader. The problem seems to be with StringBuilder. You can split the result into batches of say 10000 rows and then write them. For example, open the csv file write 10000 rows, close it, then append the next 10000 rows and so on. You could do this using a loop counter. Each batch would use a new StringBuilder instance to write to the csv file.