Xml string throwing outofmemory exception RRS feed

  • Question

  • Hello,

    I need to pass a datatable to a stored procedure. I'm using Datatable.writexml to convert the table into an xml using a StringBuilder and then converting the stringbuilder into a string using StringBuilder.ToString(), which is throwing out of memory exception when datatable exceeds certain number of datarows. 

    Please suggest ways to handle this memory exception. 

    If string cannot handle a large datatable's xml, is there any other way to pass datatable into a stored procedure?



    Raj Muthusamy

    Wednesday, November 22, 2017 9:10 PM

All replies

  • Hello,

     Please provide more details about the issue. Information such as, exact error message and

    the source code where the error occurred.

     Thanks :)

    Thursday, November 23, 2017 3:43 AM
  • A datatable uses 6 times more memory than a List. Therefore, you should use a List of custom objects.

    • Proposed as answer by Fei HuModerator Tuesday, November 28, 2017 11:01 AM
    Thursday, November 23, 2017 4:26 AM
  • Hello,

     That's to be expected, List<T> are simple container compared to DataSets, DataTables

    and DataViews that complex structures.




     However, the OP has an issue with WriterXML error.   Perhaps the following link will assist in problem solution;



     Hope this helps :)

    Thursday, November 23, 2017 4:56 AM
  • I think the OP has unknowingly stated the real problem. 


    which is throwing out of memory exception when datatable exceeds certain number of datarows. 


    I think the culprit here is the datatable that has sucked-up all the memory, and it is the datatable that is at fault here and not WriterXML

    Thursday, November 23, 2017 10:58 AM
  • Hello,

     Agreed... However, 16 million rows should be plenty unless OP has

    16 million colsumn??? :/  Anyways, maybe my suggestion to OP was not

    clear. OP should call WriterXML in batches instead of the entire set.

     Thanks :)

    Thursday, November 23, 2017 12:17 PM
  • Yes, it's coming from the DataTable itself. Here is the exception stack trace.


    Type : System.OutOfMemoryException, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089
    Message : Exception of type 'System.OutOfMemoryException' was thrown.
    Exception Data :
    Source : mscorlib
    Help link : 
    Data : System.Collections.ListDictionaryInternal
    TargetSite : System.String CreateStringFromEncoding(Byte*, Int32, System.Text.Encoding)
    HResult : -2147024882
    Stack Trace :    at System.String.CreateStringFromEncoding(Byte* bytes, Int32 byteLength, Encoding encoding)
       at System.Text.EncodingNLS.GetString(Byte[] bytes, Int32 index, Int32 count)
       at System.Data.SqlClient.TdsParserStateObject.TryReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp, String& value)
       at System.Data.SqlClient.TdsParser.TryReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj, SqlCommandColumnEncryptionSetting columnEncryptionOverride, String columnName)
       at System.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i, Boolean readHeaderOnly)
       at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn)
       at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
       at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
       at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
       at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
       at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
       at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames) in line 517
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames) in line 1383
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName) in line 1336
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command) 


    Raj Muthusamy

    Tuesday, November 28, 2017 12:04 AM
  • Just little more info.

    We're executing a stored procedure, which fetches about 54,000 records, each has 9 columns with few int and varchar types with max size 100 characters in two columns.


    Raj Muthusamy

    Tuesday, November 28, 2017 12:39 AM
  • The exception is thrown at DbDataAdapter.Fill() stage, you result set is too large.

    You probably want to use SqlDataReader with SqlCommand.ExecuteReader() set to SequentialAccess to convert the result into List of custom object, as DA924x suggested.

    Tuesday, November 28, 2017 2:13 AM