WPF C# Application Took too long to get data from SQL Server

Question

I have a WPF application connected to SQL Server; while I load up to 10 records into my DataGrid, my application works fine and response is too fast, but when I load all rows (which is almost 1000), my application took around 15 seconds to load and freezes the entire UI.

But when I execute the same query in SQL Server, it only took around 00:00:00.490 seconds to load those 1000 rows which is too fast. What I already have done is as below to avoid UI freezing and query execution fast. What I am doing wrong? Please guide with code snippets as I am new to C# world.

// Calling function to load data into DataGrid in a new thread,
// to make UI responsive.
String qry = "select * from institutes_tbl"
DataGrid dg = MainDataGrid;
Thread thread = new Thread(() => FunDataGrid_DataView(dg, qry));
thread.IsBackground = true;
thread.Start();

But unfortunately my UI shows message "Not Responding". Below is the function definition:

public void FunDataGrid_DataView(DataGrid dg, string qry)
{
    Application.Current.Dispatcher.BeginInvoke
    (
        DispatcherPriority.Background,
        new Action(() =>
        {                       
            try
            {
                con = new SqlConnection(con_string);
                cmd = new SqlCommand(qry, con);
                cmd.CommandTimeout = 12 * 3600;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                dg.ItemsSource = dt.DefaultView;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error!",
                    MessageBoxButton.OK, MessageBoxImage.Warning);
            }                                                                      
        }
    ));                            
}

Here is my XAML:

<DataGrid x:Name="DataGrid_View"
          MouseLeftButtonUp="DataGrid_View_MouseLeftButtonUp"
          ItemsSource="{Binding DATA_TBL}"
          LoadingRow="DataGrid_View_LoadingRow" Grid.Row="2"
          Grid.Column="0" ScrollViewer.CanContentScroll="False"
          AutoGenerateColumns="False" CanUserAddRows="False"
          Background="#7F179DB2" CellStyle="{StaticResource CellStyle}">
    <DataGrid.Columns>
        <DataGridTextColumn Header="Name"
                            Binding="{Binding NAME}" Width="5*"/>
        <DataGridTextColumn Header="Father Name"
                            Binding="{Binding F_NAME}" Width="5*"/>
        <DataGridTextColumn Header="CNIC"
                            Binding="{Binding CNIC}" Width="5*"/>
    </DataGrid.Columns>
</DataGrid>

I want my UI to be responsive and data to load fast. I am working on local host right now.

do you need all the columns ?, if not then mention only those columns in the select clause. Also assign the datagrid without threading — Sats, Nov 18 '18 at 15:23
@S.Akbari As I am also using Dispatcher and New Thread What I am doing wrong ? Could you please guide a bit more ? I have went through the link you provided. — asim.ali314, Nov 18 '18 at 15:24
Yees, you are spawning a new thread but then executing the actual database access on the UI thread again (Application.Current.Dispatcher) — Klaus Gütter, Nov 18 '18 at 15:27
@KlausGütter but if I dont use dispatcher it says "The Calling thread cannot access this object because a different thread owns it" What should I do for it ? — asim.ali314, Nov 18 '18 at 15:30
`Application.Current`... hmm.... that's the main UI thread that you're dispatching into. Probably explains why the UI is freezing up: the thread that manages the UI responses to user actions, is busy doing the SQL work. Any reason for that? You generally want to do an invoke to the main UI thread only to force a context refresh, and using MVVM concepts, that means you do what's called an `INPC` (INotifyPropertyChanged) call. Use that thread to update the viewmodel class which implements INPC, and you should be back on the WPF "happy path". — code4life, Nov 18 '18 at 18:06
Unrelated tips: SqlConnection SqlCommand and SqlDataAdapter are all IDisposable so each should be in a `using` block. — Richardissimo, Nov 18 '18 at 20:30
@code4life I did'nt implemented MVVM yet its a simple application — asim.ali314, Nov 19 '18 at 07:32
Make sure you haven't somehow disabled the UI virtualization. Please post your XAML for further help. — mm8, Nov 19 '18 at 13:22
@mm8 I have update my Question and Posted my XAML as well please have a look. — asim.ali314, Nov 19 '18 at 16:35
Try giving the Datagrid an actual height. The problem with here seems to be that you give your datagrid enough space to render every item without virtualizing anything. So try putting it for example in a grid with a rowdefinition of size 400 and check if the UI loads faster. — Brezelmann, Sep 15 '21 at 12:29

score 0 · Answer 1 · edited Sep 15 '21 at 17:47

Try this:

public void FunDataGrid_DataView(DataGrid dg, string qry)
{
    con = new SqlConnection(con_string);
    cmd = new SqlCommand(qry, con);
    cmd.CommandTimeout = 12 * 3600;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    Application.Current.Dispatcher.BeginInvoke(
      DispatcherPriority.Background,
      new Action(() =>
      {                       
          try
          {
              dg.ItemsSource = dt.DefaultView;
          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message, "Error!",
                  MessageBoxButton.OK, MessageBoxImage.Warning);
          }
    }));                            
}

Hope it helps!

Unfortunately It does'nt worked for me! The issue is still there my UI Freezes and stop responding until the data is loaded into DataGrid After that all went to smooth state like normal. — asim.ali314, Nov 18 '18 at 15:45

score 0 · Answer 2 · edited Sep 15 '21 at 11:41

0

Start the new background thread to query the data from table as shown below:

Task.Factory.StartNew(() =>
{
    try
    {
        con = new SqlConnection(con_string);
        cmd = new SqlCommand(qry, con);
        cmd.CommandTimeout = 12 * 3600;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        Application.Current.Dispatcher.BeginInvoke
        (
            DispatcherPriority.Background,
            new Action(() => dg.ItemsSource = dt.DefaultView)
        );
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error!", MessageBoxButton.OK,
            MessageBoxImage.Warning);
    }
});

edited Sep 15 '21 at 11:41

Toni

1,555
4
15
23

answered Nov 18 '18 at 17:40

Cinchoo

6,088
2
19
34

Thanks for the response @RajN but unfortunately this solution also not working my UI still Freezes I have identified the issue which is in this line dg.ItemsSource = dt.DefaultView, if I comment it all works fine so the reason is while loading data from data table to DataGrid freezes my UI, I dont know how to handle it. – asim.ali314 Nov 19 '18 at 07:30
@asim.ali314 Please provide a sample of a single row of data. I feel that this answer should do the trick. – Michael Puckett II Nov 19 '18 at 16:38
I have 25 Columns in a table each table is of type VARCHAR(250) to VARCHAR(1000) as data contains much details. May be this can help you ? Moreover my data contains only Text. – asim.ali314 Nov 20 '18 at 14:45

WPF C# Application Took too long to get data from SQL Server

2 Answers2