none
Binding large data to a data table using SQL Server RRS feed

  • Question

  • Good Day,

    I have used textFieldParser to import 4.5 million data from a text file to a Sql data table using vb.net. I have performed several SQL query to update the datatable. All went fine.  But when i wanted to populate the dataTableAdapter with the table data  in order to display the data to a datagridview,  "System.OutOfMemoryException" was thrown. 

    Error was thrown at this command: 

    "Sim_VehRecordTableAdapter.Fill(Main_DataDataSet.Sim_VehRecord)"

    Whats can be the probable solution.? I have disposed all sqlcommand and sqlconnection before i fill the TableAdapter.

    Thanks

    Tariq 



    • Edited by Tariq Hasan Friday, December 13, 2019 11:14 AM
    Friday, December 13, 2019 11:12 AM

Answers

  • Hello,

    You have other options.

    If the data needs no conversion use BULK INSERT directly into the table or if the data needs conversions still use BULK INSERT into a temp table or a intermediate table. There is a very simply example of BULK INSERT here. See more here.

    Regarding the out of memory for the DataGridView, if you need this then look at virtual mode (see a walkthrough here) and another term is pagination so that the DataGridView only displays x amount of records at any given time.

    Now there is a slim chance for the following for the DataGridView which is why this is listed last.

    dataGridView1.RowHeadersVisible = false

    Make sure there are no events setup for the DataGridView


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Tariq Hasan Sunday, December 15, 2019 3:41 AM
    • Unmarked as answer by Tariq Hasan Wednesday, December 18, 2019 10:10 AM
    • Marked as answer by Tariq Hasan Wednesday, December 18, 2019 10:10 AM
    Saturday, December 14, 2019 1:19 AM
    Moderator