WPF Datagrid / Datatable: Large number of rows

Question

I have a Datagrid connected to Datatable, which needs to load a very large amount of rows.

To speed things up, I load 10% of the rows, and display the form. Most of the time the user only needs those 10% (they are the most recent entries). In a background thread I load the remaining 90% of the rows into another datatable (SecondData). Then I merge both datatables:

FirstData.BeginLoadData()
FirstData.Merge(SecondData, False)
FirstData.EndLoadData()

This works fine, but the Merge operation takes a very long time. If I reverse the operation (merging SecondData with FirstData), it takes much less time. But then I have to re-assign an itemsource (SecondData) to the Datagrid, and the user looses the current scrolling position, selected row, etc.

I also tried adding the rows directly to FirstData from the background thread, and it appears to work just fine. But when I scroll the Datagrid after that, I get freezes, and "DataTable internal index is corrupted", after that.

What would be the correct way of doing this?

Answer 1

If you you use the BeginInvoke method of a window or control’s Dispatcher property, it adds the delegate to the Dispatcher’s event queue; however, you get the opportunity to specify a lower priority for it. By executing a method that loads just one item at a time, the window is given the opportunity to execute any other higher-priority events in between items. This allows the control or window to be displayed and rendered immediately and loads each item one at a time.

Here is a some sample code that loads a ListBox.
You can adapt this to your DataGrid.
In this example I used a ViewModel that contains an ObservableCollection that contains a object.
If you have trouble converting to your DataGrid I'll rework.

Here is Window XAML:

<Window x:Class="ListBoxDragDrop.Views.MainView"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:Models="clr-namespace:ListBoxDragDrop.Models" 
    Loaded="Window_Loaded"
    Title="Main Window" Height="400" Width="800">
  <DockPanel>
    <Grid>
        <Grid.ColumnDefinitions>
            <ColumnDefinition/>
        </Grid.ColumnDefinitions>
        <ListBox Grid.Column="0" ItemsSource="{Binding Path=MyData}">
            <ListBox.ItemTemplate>
                <DataTemplate DataType="{x:Type Models:Person}">
                    <StackPanel>
                        <TextBlock Text="{Binding Name}" ></TextBlock>
                        <TextBlock Text="{Binding Description}" ></TextBlock>
                    </StackPanel>
                </DataTemplate>
            </ListBox.ItemTemplate>
        </ListBox>
    </Grid>
  </DockPanel>
</Window>

Here is the Window code-behind with the Loaded event:

public partial class MainView : Window
{
  MainViewModel _mwvm = new ViewModels.MainViewModel();
  ObservableCollection<Person> _myData = new ObservableCollection<Person>();

  public MainView()
  {
     InitializeComponent();
     this.DataContext = _mwvm;
  }

  private void Window_Loaded(object sender, RoutedEventArgs e)
  {
     // Execute a delegate to load
     // the first number on the UI thread, with
     // a priority of Background.
     this.Dispatcher.BeginInvoke(DispatcherPriority.Background, new LoadNumberDelegate(LoadNumber), 1);
  }

  // Declare a delegate to wrap the LoadNumber method
  private delegate void LoadNumberDelegate(int number);
  private void LoadNumber(int number)
  {
     // Add the number to the observable collection
     // bound to the ListBox
     Person p = new Person { Name = "Jeff - " + number.ToString(), Description = "not used for now"};
     _mwvm.MyData.Add(p);
     if (number < 10000)
     {
        // Load the next number, by executing this method
        // recursively on the dispatcher queue, with
        // a priority of Background.
        //
        this.Dispatcher.BeginInvoke(
        DispatcherPriority.Background,
        new LoadNumberDelegate(LoadNumber), ++number);
     }
  }
}

Here is the ViewModel:

public class MainViewModel : ViewModelBase
{
  public MainViewModel()
  {
  }

  private ObservableCollection<Person> _myData = new ObservableCollection<Person>();
  public ObservableCollection<Person> MyData
  {
     get
     {
        return _myData;
     }
     set
     {
        _myData = value;
        OnPropertyChanged("MyData");
     }
  }
}

And the defintion of Person for completness:

public class Person
{
  public string Name { get; set; }
  public string Description { get; set; }
}

Answer 2

Here is a somewhat hacked additional version that shows how to load a DataGrid when binding to a DataView using still BeginInvoke. The code still loads one row at a time into the DataGrid. You'll need to modify as needed; I am loading from the AdventureWorks sample using the Loaded event.

Here is how the ViewModel works:

1. First load the columns using a SQL statement with a Where clause of 1=0
2. Call Dispatcher.BeginInvoke to first load a subset of data
3. Then call Dispatcher.BeginInvoke again to load the remaining data

Here is the window:

<Window x:Class="DatagridBackgroundWorker.Views.MainView"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:WpfToolkit="clr-namespace:Microsoft.Windows.Controls;assembly=WPFToolkit" 
    Loaded="Window_Loaded"
    Title="Main Window" Height="400" Width="800">
  <DockPanel>
    <Grid>
        <WpfToolkit:DataGrid  
            Grid.Column="1"
            SelectedItem="{Binding Path=SelectedGroup, Mode=TwoWay}"
            ItemsSource="{Binding Path=GridData, Mode=OneWay}" >
        </WpfToolkit:DataGrid>
    </Grid>
  </DockPanel>
</Window>

Here is the Window code-behind with the Loaded event:

public partial class MainView : Window
{
  ViewModels.MainViewModel _mvm = new MainViewModel();

  public MainView()
  {
     InitializeComponent();
     this.DataContext = _mvm;
  }

  private void Window_Loaded(object sender, RoutedEventArgs e)
  {
     Dispatcher d = this.Dispatcher;
     _mvm.LoadData(d);
  }
}

Here the ViewModel:

public class MainViewModel : ViewModelBase
{
  public MainViewModel()
  {
     // load the connection string from the configuration files
     _connectionString = ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;

     using (SqlConnection conn = new SqlConnection(ConnectionString))
     {
        conn.Open();

        // load no data 1=0, but get the columns...
        string query =
           "SELECT [BusinessEntityID],[Name],[SalesPersonID],[Demographics],[rowguid],[ModifiedDate] FROM [Sales].[Store] Where 1=0";
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = query;

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(_ds);
     }
  }

  // only show grid data after button pressed...
  private DataSet _ds = new DataSet("MyDataSet");
  public DataView GridData
  {
     get
     {
        return _ds.Tables[0].DefaultView;
     }
  }

  private void AddRow(SqlDataReader reader)
  {
     DataRow row = _ds.Tables[0].NewRow();
     for (int i = 0; i < reader.FieldCount; i++)
     {
        row[i] = reader[i];
     }
     _ds.Tables[0].Rows.Add(row);
  }

  public void LoadData(Dispatcher dispatcher)
  {
     // Execute a delegate to load the first number on the UI thread, with a priority of Background.
     dispatcher.BeginInvoke(DispatcherPriority.Background, new LoadNumberDelegate(LoadNumber), dispatcher, true, 1);
  }

  // Declare a delegate to wrap the LoadNumber method
  private delegate void LoadNumberDelegate(Dispatcher dispatcher, bool first, int id); 
  private void LoadNumber(Dispatcher dispatcher, bool first, int id)
  {
     try
     {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
           conn.Open();

           // load first 10 rows...
           String query = string.Empty;
           if (first)
           {
              // load first 10 rows
              query =
                 "SELECT TOP 10 [BusinessEntityID],[Name],[SalesPersonID],[Demographics],[rowguid],[ModifiedDate] FROM [AdventureWorks2008].[Sales].[Store] ORDER By [BusinessEntityID]";
              SqlCommand cmd = conn.CreateCommand();
              cmd.CommandType = CommandType.Text;
              cmd.CommandText = query;
              int lastId = -1;
              SqlDataReader reader = cmd.ExecuteReader();
              if (reader != null)
              {
                 if (reader.HasRows)
                 {
                    while (reader.Read())
                    {
                       lastId = (int)reader["BusinessEntityID"];
                       AddRow(reader);
                    }
                 }
                 reader.Close();
              }

              // Load the remaining, by executing this method recursively on 
              // the dispatcher queue, with a priority of Background.
              dispatcher.BeginInvoke(DispatcherPriority.Background,
                 new LoadNumberDelegate(LoadNumber), dispatcher, false, lastId);
           }
           else
           {
              // load the remaining rows...

              // SIMULATE DELAY....
              Thread.Sleep(5000);

              query = string.Format(
                    "SELECT [BusinessEntityID],[Name],[SalesPersonID],[Demographics],[rowguid],[ModifiedDate] FROM [Sales].[Store] Where [BusinessEntityID] > {0} ORDER By [BusinessEntityID]",
                    id);
              SqlCommand cmd = conn.CreateCommand();
              cmd.CommandType = CommandType.Text;
              cmd.CommandText = query;
              SqlDataReader reader = cmd.ExecuteReader();
              if (reader != null)
              {
                 if (reader.HasRows)
                 {
                    while (reader.Read())
                    {
                       AddRow(reader);
                    }
                 }
                 reader.Close();
              }
           }
        }
     }
     catch (SqlException ex)
     {
     }
  }

  private string _connectionString = string.Empty;
  public string ConnectionString
  {
     get { return _connectionString; }
     set
     {
        _connectionString = value;
        OnPropertyChanged("ConnectionString");
     }
  }
}