System.outofmemoryexception while displaying data from datatable to datagrid

Question

private void newBtn_Click(object sender, RoutedEventArgs e)
{
    try
    {
        if (txtDayfind.Text == "" || txtDatefind.Text == "" || 
            txtTimefind.Text == "" || txtLatfind.Text == "" || 
            txtLongfind.Text == "" || txtAddressfind.Text == "" || 
            txtaccuracy.Text == "" || txtTypefind.Text == "")
        {
            Button button = sender as Button;
            string content = button.Content.ToString();
            foreach (DataTable table in dsr.Tables)
            {
                if (table.TableName == content)
                {
                    dataGrid1.ItemsSource = table.DefaultView;
                    dtselect = table;
                }
            }
        }
        else
        {
            dataGrid1.ItemsSource = dtselect.DefaultView;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

I am trying to display data from DataTable to Datagrid but DataTable contains more than 0.2 Million rows so it gives System.OutOfMemoryException. And I am not able to display data and application stop working.

Please help me to resolve System.OutOfMemoryException this issue. I have one dataset which contains multiple tables when DataTable has small data it works fine, but when DataTable have big data it consumes more space and give System.outofmemoryexception.

Don't load all the data at once, only retrieve the data shown on screen and use paging too load the rest — 3dd
– 3dd, Commented Dec 6, 2018 at 7:16
it is windows application and I am not done paging before can you help me with my code, please. — user10753256
– user10753256, Commented Dec 6, 2018 at 7:18
AFAIU you are in 32bit mode, you can try to switch to 64bit to allow your app using more memory, but anyways i agree with others that paging is needed here. — Ilia Maskov
– Ilia Maskov, Commented Dec 6, 2018 at 7:35

Дмитро Іванов · Accepted Answer · 2018-12-07 12:46:43Z

1

If error fires when you read data from database, then pagination is your solution.

But if you get an error when WPF binds your collection to DataGrid and renders it, you can try set DataGrid EnableRowVirtualization property to true. If you do so, only rows, currently displayed on screen will be rendered.

UPDATE: I think, you don't quite understand, what paging is. Paging is loading data from the table in chunks (pages). Something like this (Note: I understand, that plain string non-parametrized queries are evil, but I'll use it for simplicity).

In your view-model class or in Code Behind you define two variables:

int pageSize;

and

int currentPage = 0;

and when you define a query string for loading data you don't just use

string query = "SELECT * FROM [your_table_name]";

but

string query = $"SELECT * FROM [your_table_name] OFFSET {pageSize*currentPage} ROWS FETCH NEXT {pageSize} ROWS ONLY";

When you execute this query it will return you not all records from the table, but pageSize rows, starting from currentPage

And in your interface you should put two buttons, which will increase or decrease currentPage and load next page. Or a possibility to move to a certain page manually by page number. It's up to your and your application design.

edited Dec 7, 2018 at 12:46

answered Dec 6, 2018 at 8:03

Дмитро Іванов

1,0316 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user10753256 Over a year ago

Paging I cant do so I want to display data using a horizontal scrollbar.

user10753256 · Accepted Answer · 2019-01-02 07:25:26Z

<Viewbox Grid.Row="3" Stretch="Fill">
                <DataGrid x:Name="dataGrid1" Grid.Row="3" VirtualizingPanel.VirtualizationMode="Recycling" BorderThickness="0" VirtualizingPanel.IsVirtualizing="True" CanUserSortColumns="True" EnableRowVirtualization="True" IsReadOnly="True" CanUserResizeColumns="True" CanUserAddRows="False"  VerticalAlignment="Bottom"  HorizontalAlignment="Stretch" VerticalScrollBarVisibility="Auto" 
                HorizontalScrollBarVisibility="Auto" ScrollViewer.CanContentScroll="True" Height="600" Width="auto" ItemsSource="{Binding}" AutoGenerateColumns="False" ScrollViewer.VerticalScrollBarVisibility="Auto"   Background="#b5d2fc"  ClipboardCopyMode="IncludeHeader" SelectionMode="Extended" CanUserDeleteRows="False">
                    <DataGrid.Columns>
                        <DataGridTextColumn Width="200" Header="Day"  Binding="{Binding Day}"/>
                        <DataGridTextColumn Width="200" Header="Date" Binding="{Binding Date}"/>
                        <DataGridTextColumn Width="200" Header="Time" Binding="{Binding Time}"/>
                        <DataGridTextColumn Width="200" Header="Lat" Binding="{Binding Lat}"/>
                        <DataGridTextColumn Width="200" Header="Long" Binding="{Binding Long}"/>
                        <DataGridTextColumn Width="400" Header="Address" Binding="{Binding Address}"/>
                        <DataGridTextColumn Width="200" Header="Accuracy" Binding="{Binding Accuracy}"/>
                        <DataGridTextColumn Width="200" Header="Type" Binding="{Binding Type}"/>
                    </DataGrid.Columns>
                </DataGrid>
                </Viewbox>

I simply add Viewbox and my code works fine if I load more than two files which have huge data then also it consume less memory

Collectives™ on Stack Overflow

System.outofmemoryexception while displaying data from datatable to datagrid

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related