none
Datatable Out of Memory Error for Bulk Records in c# RRS feed

  • Question

  • hi

    i m using winform 2008 with c# and mysql server. i have 1500000 records in table. i want export each 65000 to Excel Export

     using (MySqlCommand cmmd = new MySqlCommand("select * from Z_Table", Ncon))
                                    {
                                        using (MySqlDataReader drr = cmmd.ExecuteReader())
                                        {
                                            NewTable.Load(drr);
                                        }
                                    }


    dataGridView1.DataSource = null;
                                dataGridView1.DataSource = NewTable;

    Successfully display in grid.

    Export Excel

    List<DataTable> tables = new List<DataTable>();
                                    int count = 0;
                                    DataTable copyTable = null;
                                    foreach (DataRow dr in ExportTable.Rows)
                                    {
                                        if ((count++ % 65000) == 0)
                                        {
                                            copyTable = new DataTable();
                                            copyTable = ExportTable.Clone();
                                            tables.Add(copyTable);
                                        }
                                        copyTable.ImportRow(dr); // Out of memory Exception 
                                    }

    out of memory error only for bulk records. i tried many ways but all failed. i spent days but not get answer.

    Plantform : x86

    anyone tell me the solution as soon as possible




    Thanks & Regards,

    Wednesday, May 3, 2017 6:28 PM

All replies

  • Hi Brinda,

    I guess you did not paginate when you exported to excel, please do it.

    And try to reference to the below link:

    http://stackoverflow.com/questions/9175741/out-of-memory-exception-when-pulling-huge-data-from-db

    Hope it helps!

    Best Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 5, 2017 8:14 AM
  • Thank you for reply,

    can u tell me the example...

    its working without error while we select x64 plant form . but my client system 32 bit. so can u give some example and give the solution as soon as possible


    Thanks & Regards,


    Monday, May 8, 2017 2:38 PM
  • Hi Brinda Arumugam,

    Please try to use CopyToDataTable method and check if it works for you. like this:

    List<DataTable> list = new List<DataTable>();
    DataTable temp = null;
    
                    for (int i = 0; i < dt.Rows.Count; i += 6500)
                    {
                        temp = new DataTable();
                        temp = dt.AsEnumerable().Skip(i).Take(6500).CopyToDataTable();
                        list.Add(temp);
                    }

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 9, 2017 9:31 AM
    Moderator
  • thanks for reply,

    i did above example. same error showing.


    Thanks & Regards,


    Tuesday, May 9, 2017 1:42 PM
  • You want to work with streaming APIs when dealing with large volumes of data. In the case of ADO.NET, this would mean the ExecuteReader() method on the DbCommand; with that, you have a fast row-based streaming API that you can work with. Do not use a DataTable for this operation.

    Write each record to the csv / excel file inside the loop. The .NET framework and operating system will take care of flushing the buffers to disk in an efficient way.

    The following post goes over how to do this via an extension off the reader.

    http://www.extensionmethod.net/2085/csharp/list-string/datareader-to-csv


    william xifaras


    Tuesday, May 9, 2017 2:48 PM
  • I liked that SqlDataReader Extension method, and I was curious what the speed difference would be between that or using a DataTable. BTW, that extension method needed modifying so that it would *not* ignore DBNull.Value (you still need to have a comma for an empty data value, which that extension didn't provide ). I already had my own extension method for creating .csv files from a DataTable (and it uses similar parsing). Anyway to benchmark the speed of the two methods, I compared by also using a SqlDataReader to load the data into a DataTable.

    I had a query that returned about 330,000 records. The SqlDataReader extension method was anywhere from 5 to 10 seconds faster than the DataTable extension method. The SqlDataReader averaged about 42 seconds and the DataTable averaged about 49 seconds.

    In case anyone else is interested ...

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, May 11, 2017 5:37 PM