System.OutOfMemoryException, DataTable.WriteXml(TextWriter writer, Boolean writeHierarchy) RRS feed

  • Question

  • Hi the below code throws an System.OutOfMemoryException.I think its because of the string writer
    The method BulkUpload is being called in a loop from another method.

     Private Sub BulkUpload(ByVal dtbInput As DataTable, ByVal szSpInput As String, ByVal szSpName As String, ByVal bFilterTimeZone As Boolean)
                Dim oSqlParameter() As SqlParameter
                Dim szInput As String
                Dim swInput As System.IO.StringWriter
                Dim oDataBaseWrapper As DatabaseWrapper
                    swInput = New System.IO.StringWriter
                    oDataBaseWrapper = New DatabaseWrapper
                    oSqlParameter = New SqlParameter(0) {}
                    oSqlParameter(0) = New SqlParameter(szSpInput, SqlDbType.Text)
                    swInput.GetStringBuilder.Remove(0, swInput.GetStringBuilder.Length)
                    dtbInput.WriteXml(swInput, False)
                    szInput = swInput.ToString()
                    If bFilterTimeZone Then
                        szInput = Hess.Common.ReusableFunctions.FilterTimeZone(szInput, CommonConstants.CONNECTION_STRING)
                    End If
                    oSqlParameter(0).Value = szInput
                    oDataBaseWrapper.ExecuteNonQueryByParams(CommonConstants.CONNECTION_STRING, CommandType.StoredProcedure, szSpName, oSqlParameter)
                    oDataBaseWrapper = Nothing
                    oSqlParameter = Nothing
                    swInput = Nothing
                End Try

    Below is the exception message:

    | Exception of type 'System.OutOfMemoryException' was thrown.
    |    at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)  
      at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)   
     at System.Text.StringBuilder.Append(String value)   
     at System.IO.StringWriter.Write(String value)   
     at System.Xml.XmlTextWriter.WriteStartElement(String prefix, String localName, String ns)   
     at System.Data.DataTextWriter.WriteStartElement(String prefix, String localName, String ns)    
    at System.Data.XmlDataTreeWriter.XmlDataRowWriter(DataRow row, String encodedTableName)   
     at System.Data.XmlDataTreeWriter.Save(XmlWriter xw, Boolean writeSchema)   
     at System.Data.DataTable.WriteXml(XmlWriter writer, XmlWriteMode mode, Boolean writeHierarchy)  
      at System.Data.DataTable.WriteXml(TextWriter writer, XmlWriteMode mode, Boolean writeHierarchy)
        at System.Data.DataTable.WriteXml(TextWriter writer, Boolean writeHierarchy)    
    at Hess.EM.IPMS.Electricity.Business.Proposal.BulkUpload(DataTable dtbInput, String szSpInput, String szSpName, Boolean bFilterTimeZone)    

    Friday, February 13, 2009 3:08 PM

All replies

  • Perhaps before you go and write the Xml file you can write out the number of rows that are in the DataTable. You can do this by adding the following code


    From what it looks like to me, you have the DataTable, it has Size X. You then take it and write it out as a string. The size of the string will likely be as large as the DataTable in memory. So essentially you require almost double the amount of memory. This doesn't scale very well.

    One way you might be able to reduce the number of changes you have to push through the store procedure is by using the GetChanges() method

    The api is described here
    Perhaps you call GetChanges() on the DataTable. This will hopefully give you a DataTable that is smaller than the whole one. Then you can continue the processing and get the whole string from by calling WriteXml from this DataTable instead of the one you passed in.

    Also there is full documentation of particular options of how to call a stored procedure in this msdn article

    As a hybrid approach you can change the stored procedure to take one call at a time and then simply iterate over the changed rows and create Sql commands that would send the data to Sql Server.  

    Let me know if any of these suggestions helped.

    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 13, 2009 5:27 PM
  •    well the datatable row count should be 500  most of the time 
     it could also be less than 500 at times.So we have some kind of constants count.Is it because i am not flushing the string writer in the bulupload method
    Also if the loop is from  0 to 8760 say then bulk upload should be called ariund 17 times ,so we declare new string writers around 17 times .PLease advice

    PLease find below an explanation of the code

     The BulkUpload method is being called from the method x as below
     when the record count in the loop reaches 500 i call the bulk upload method and clear the existing rows, i againg call the bulk upload methon on exiting the loop incase the record count is less than 500

     Private Sub x()
    'loop in private method (multiples of the number 8760)

    x=8760* multiple
    ''where multiple is any number from 1 to  5
    For i as integer =0 to x
    ''add some rows to the data table

    ''check row count is 500

        If dtbComponentHourlyCost.Rows.Count = 500 Then

        BulkUpload(dtbComponentHourlyCost, "@IN_CALCULATED_PRICE", SP_INSERT_COMPONENT_HOURLY_COST, False)

        'clear the existing records that were inserted


    End If


    'check if the row count that was less than 500 and not inserted

    If dtbComponentHourlyCost.Rows.Count > 0 Then

    BulkUpload(dtbComponentHourlyCost, "@IN_CALCULATED_PRICE", SP_INSERT_COMPONENT_HOURLY_COST, False)

    'clear the existing records that were inserted


    End If

    End Sub
    Saturday, February 14, 2009 10:50 AM