.Net System.OutOfMemoryException populates data

Question

I need to extract data from a .dbf file and convert it to xml. I wrote a routine that does this just fine. However, now we are faced with very large .dbf files - like 2GB +. And this code throws an OutOfMemoryException exception for these files.

Public Function GetData() As DataTable
    Dim dt As New DataTable(Name)
    Dim sqlcommand As String= "Select * From MyTable"
    Dim cn As New OleDbConnection(myconnectionstring)

    Try
        cn.Open()
        Dim cmd As New OleDbCommand(sqlcommand, cn)
        dt.Load(cmd.ExecuteReader())
    Catch ex As Exception
        Throw ex
    Finally
        dt.Dispose()
        cn.Close()
        cn.Dispose()
    End Try
    Return dt

The fact is that if I run the same code on my computer using Visual Studio in debug mode in the same 2 GB .dbf file, an exception will not be thrown. This is almost the same as Visual Studio managing memory differently than the application does on its own.

Are there any problems with memory? I tried using a DataAdapter with similar results. Is this behavior I see with Visual Studio expected / by design?

Answer 1

A datatable , .

SqlDataReader XmlWriter XML .

- ( )

Public Sub WriteToXml(Dim xmlFileName As String, Dim connectionString)
    Dim writer As XmlWriter
    writer = XmlWriter.Create(xmlFileName)
    Dim commandText As String= "Select * From MyTable"
    Dim connection As New OleDbConnection(connectionString)

    Try
        connection.Open()
        Dim command As New OleDbCommand(commandText, connection)
        Dim reader As SqlDataReader
        reader = myCommand.ExecuteReader()

        While reader.Read()             
            write.WriteRaw("xml")
        End While
    Catch ex As Exception
        Throw ex
    Finally        
        connection.Close()
        connection.Dispose()
    End Try
End Sub

Answer 2

2 .

, , , TOP ROWNUM SELECT. . SQL Server.

Answer 3

, DataReader DataSet; , . SqlDataAdapter.Fill(), .

Dim conn As New SqlConnection(connection)
Dim adapter As New SqlDataAdapter()
adapter.SelectCommand = new SqlCommand(query, conn)
adapter.Fill(dataset)
adapter.Dispose()
conn.Dispose()

You really don't need to call .Close () to connect, as it is called when .Dispose () is called.

PS: You do not close the Reader, probably why. Yes, VS.NET will close it faster than GC.

Answer 4

Why not do something simple:

using (var writer = CreateXmlWriter(fileName))
{
  while (reader.Read()) 
  {
    var value = new ObjectFromDatabaseReader(reader);
    value.WriteXml(writer);
  }
}

It will just pass the data in a row at a time, convert to an object and then save as xml. This is unlikely to use any memory and should be very fast.