How to bind multiple datagrids to a single SQLite database using MVVM pattern in WPF c#?

Question

I've created a SQLite database Employees.db, added a table tblEmployees and included the database in my project.

The WPF app has a TabControl with multiple TabItem's.

What I'm trying to do is when I launch the app the DataGrid in the first TabItem i.e. filteredGrid should show some filtered data from table tblEmployees and the DataGrid in the second TabItem i.e.dataGrid1 should show all the data and when I click on any of the rows of the DataGrid the textbox's in the second TabItem would get populated with the related column data and when I change any of the data from the textbox's and hit the button then the data should be saved to the database and also immediately reflected on both the DataGrid's on both the TabItem's.

I'm completely new to MVVM pattern and even after reading multiple posts/articles and watching multiple videos regarding how to implement MVVM pattern on the internet I'm still struggling to get a grasp on the subject and can't seem to figure out how to implement it in my simple app.

Anyways, my project structure looks like this

Window1.xaml

<Window x:Class="SQLiteMVVM.View.Window1"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:local="clr-namespace:SQLiteMVVM.View"
        Title="SQLiteMVVM.View" Height="500" Width="800"
        WindowState="Maximized">
    <Window.Resources>
        <local:DateTimeConverter
            x:Key="converter" />
        <Style
            x:Key="myStyle"
            TargetType="Button">
            <Setter
                Property="Foreground"
                Value="#B9F6CA" />
            <Setter
                Property="Background"
                Value="#01579B" />
            <Style.Triggers>
                <Trigger
                    Property="IsMouseOver"
                    Value="True">
                    <Setter
                        Property="Foreground"
                        Value="#E65100" />
                </Trigger>
            </Style.Triggers>
        </Style>
    </Window.Resources>
    <Grid>
        <TabControl>
            <TabItem Header="General">
                <DataGrid ItemsSource="{Binding employees}"
                          Margin="15"
                          HorizontalAlignment="Center"
                          VerticalAlignment="Top"
                          Grid.Row="0"
                          x:Name="filteredGrid"
                          Width="700"
                          Height="400"
                          AutoGenerateColumns="False"
                          CanUserAddRows="False"
                          IsReadOnly="True">
                    <DataGrid.Columns>
                        <DataGridTextColumn Header="Employee Name" Binding="{Binding Path=empname}" IsReadOnly="True"/>
                        <DataGridTextColumn Header="Age" Binding="{Binding Path=age}" IsReadOnly="True" />
                        <DataGridTextColumn Header="Date of Birth" Binding="{Binding Path=bdate, Converter={StaticResource converter}, StringFormat=dd-MM-yyyy}" IsReadOnly="True" />
                        <DataGridTextColumn Header="Salary" Binding="{Binding Path=salary, StringFormat=N2}" IsReadOnly="True" />
                        <DataGridTextColumn Header="Comments" Binding="{Binding Path=remarks}" IsReadOnly="True" />
                    </DataGrid.Columns>
                </DataGrid>
            </TabItem>
            <TabItem Header="CRUD">
                <Grid>
                    <Grid.RowDefinitions>
                        <RowDefinition
                            Height="Auto" />
                        <RowDefinition />
                    </Grid.RowDefinitions>
                    <DataGrid
                        Margin="15"
                        HorizontalAlignment="Left"
                        Grid.Row="0"
                        x:Name="dataGrid1"
                        Width="700"
                        Height="400"
                        AutoGenerateColumns="False"
                        CanUserAddRows="False"
                        IsReadOnly="True"
                        SelectionMode="Single"
                        SelectionUnit="FullRow">
                        
                        
                        <DataGrid.Resources>
                            <Style TargetType="{x:Type DataGridCell}">
                                <EventSetter Event="PreviewMouseLeftButtonDown" Handler="DoSelectedRow"/>
                            </Style>
                        </DataGrid.Resources>
                        
                        <DataGrid.Columns>
                            <DataGridTextColumn x:Name="col1" Header="Employee Name" Binding="{Binding Path=empname}" IsReadOnly="True"/>
                            <DataGridTextColumn x:Name="col2" Header="Age" Binding="{Binding Path=age}" IsReadOnly="True" />
                            <DataGridTextColumn x:Name="col3" Header="Date of Birth" Binding="{Binding Path=bdate, Converter={StaticResource converter}, StringFormat=dd-MM-yyyy}" IsReadOnly="True" />
                            <DataGridTextColumn x:Name="col4" Header="Salary" Binding="{Binding Path=salary, StringFormat=N2}" IsReadOnly="True" />
                            <DataGridTextColumn x:Name="col5" Header="Comments" Binding="{Binding Path=remarks}" IsReadOnly="True" />
                        </DataGrid.Columns>
                    </DataGrid>
                    <StackPanel
                        Grid.Row="1">
                        <Label x:Name="lblempname"
                               Content="Name :" />
                        <TextBox
                            HorizontalAlignment="Left"
                            Margin="5"
                            x:Name="empname"
                            Height="25"
                            Width="280" />
                        <Label x:Name="lblage"
                               Content="Age :" />
                        <TextBox
                            HorizontalAlignment="Left"
                            Margin="5"
                            x:Name="age"
                            Height="25"
                            Width="200" />
                        <Label x:Name="lblbdate"
                               Content="Date of Birth :" />
                        <TextBox
                            HorizontalAlignment="Left"
                            Margin="5"
                            x:Name="bdate"
                            Height="25"
                            Width="80" />
                        <Label x:Name="lblsalary"
                               Content="Salary :" />
                        <TextBox
                            HorizontalAlignment="Left"
                            Margin="5"
                            x:Name="salary"
                            Height="25"
                            Width="80" />
                        <Label x:Name="lblremarks"
                               Content="Comments :" />
                        <TextBox
                            HorizontalAlignment="Left"
                            Margin="5"
                            x:Name="remarks"
                            Height="25"
                            Width="80" />
                        <Button
                            x:Name="update"
                            Width="55"
                            Height="25"
                            Content="Update"
                            Style="{StaticResource myStyle}" />
                    </StackPanel>
                </Grid>
            </TabItem>
            <TabItem Header="Details" />
        </TabControl>
    </Grid>
</Window>

Window1.xaml.cs

using System;
using System.Data;
using System.Data.SQLite;
using System.Globalization;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Input;
using System.Windows.Media;
using SQLiteMVVM.ViewModel;

namespace SQLiteMVVM.View
{
    [ValueConversion(typeof(DateTime), typeof(String))]
    public class DateTimeConverter : IValueConverter
    {
        public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            return DateTime.ParseExact(value.ToString(), "yyyy-MM-dd", CultureInfo.InvariantCulture);
        }
        public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            throw new NotImplementedException();
        }
    }

    
    public partial class Window1 : Window
    {
        EmployeesViewModel co;
        
        public Window1()
        {
            InitializeComponent();
            FillFilterData();

            lblempname.Visibility=Visibility.Hidden;
            lblage.Visibility=Visibility.Hidden;
            lblbdate.Visibility=Visibility.Hidden;
            lblsalary.Visibility=Visibility.Hidden;
            lblremarks.Visibility=Visibility.Hidden;

            empname.Visibility=Visibility.Hidden;
            age.Visibility=Visibility.Hidden;
            bdate.Visibility=Visibility.Hidden;
            salary.Visibility=Visibility.Hidden;
            remarks.Visibility=Visibility.Hidden;

            update.Visibility=Visibility.Hidden;

            co = new EmployeesViewModel();
            base.DataContext = co;
        }
        
        public void DoSelectedRow(object sender, MouseButtonEventArgs e)
        {
            DataGridCell cell = sender as DataGridCell;
            if (cell != null && !cell.IsEditing)
            {
                DataGridRow row = FindVisualParent<DataGridRow>(cell);
                if (row != null)
                {
                    row.IsSelected = !row.IsSelected;
                    e.Handled = true;
                }
            }
        }
        public static Parent FindVisualParent<Parent>(DependencyObject child)   where Parent : DependencyObject
        {
            DependencyObject parentObject = child;

            while (!((parentObject is System.Windows.Media.Visual)
                     || (parentObject is System.Windows.Media.Media3D.Visual3D)))
            {
                if (parentObject is Parent || parentObject == null)
                {
                    return parentObject as Parent;
                }
                else
                {
                    parentObject = (parentObject as FrameworkContentElement).Parent;
                }
            }
            parentObject = VisualTreeHelper.GetParent(parentObject);
            if (parentObject is Parent || parentObject == null)
            {
                return parentObject as Parent;
            }
            else
            {
                return FindVisualParent<Parent>(parentObject);
            }
        }

        public void FillFilterData()
        {

            using(SQLiteConnection conn= new SQLiteConnection(@"Data Source=C:\Users\Tamal Banerjee\source\repos\WpfMVVM\WpfMVVM\bin\Debug\Employees.db;"))
            {
                conn.Open();

                SQLiteCommand command = new SQLiteCommand("SELECT * FROM tblEmployees WHERE Remarks = ''", conn);
                command.ExecuteNonQuery();

                SQLiteDataAdapter adap = new SQLiteDataAdapter(command);

                DataTable dt = new DataTable("tblEmployees");
                adap.Fill(dt);

                dataGrid1.ItemsSource=dt.DefaultView;

                conn.Close();
            }

        }

        public static string FormattedDate(string init)
        {
            string[] tempArray = init.Split('-');
            string newFormat = tempArray[2].ToString()+"-"+tempArray[1].ToString()+"-"+tempArray[0].ToString();
            return newFormat;
        }

        void dataGrid1_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            DataGrid grid = (DataGrid)sender;
            dynamic selected_row = grid.SelectedItem;

            if (selected_row == null)
            {
                empname.Text = "";
                age.Text = "";
                bdate.Text = "";
                salary.Text = "";
                remarks.Text = "";

                lblempname.Visibility=Visibility.Hidden;
                lblage.Visibility=Visibility.Hidden;
                lblbdate.Visibility=Visibility.Hidden;
                lblsalary.Visibility=Visibility.Hidden;
                lblremarks.Visibility=Visibility.Hidden;

                empname.Visibility=Visibility.Hidden;
                age.Visibility=Visibility.Hidden;
                bdate.Visibility=Visibility.Hidden;
                salary.Visibility=Visibility.Hidden;
                remarks.Visibility=Visibility.Hidden;


                update.Visibility=Visibility.Hidden;
            }
            else
            {
                lblempname.Visibility=Visibility.Visible;
                lblage.Visibility=Visibility.Visible;
                lblbdate.Visibility=Visibility.Visible;
                lblsalary.Visibility=Visibility.Visible;
                lblremarks.Visibility=Visibility.Visible;

                empname.Visibility=Visibility.Visible;
                age.Visibility=Visibility.Visible;
                bdate.Visibility=Visibility.Visible;
                salary.Visibility=Visibility.Visible;
                remarks.Visibility=Visibility.Visible;

                update.Visibility=Visibility.Visible;

                empname.Text = selected_row["col1"].ToString();
                age.Text = selected_row["col2"].ToString();
                bdate.Text = FormattedDate(selected_row["col3"].ToString());
                salary.Text = selected_row["col4"].ToString();
                remarks.Text = FormattedDate(selected_row["col5"].ToString());
            }

        }

    }
}

Employees.cs

using System;

namespace SQLiteMVVM.Model
{
    public class Employees
    {
        public string empname { get; set; }
        public int age { get; set; }
        public string bdate { get; set; }
        public float salary { get; set; }
        public string remarks { get; set; }
    }
}

EmployeesViewModel.cs

using System;
using System.Windows;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using SQLiteMVVM.Model;

namespace SQLiteMVVM.ViewModel
{
    public class EmployeesViewModel : INotifyPropertyChanged
    {
        static String connectionString = @"Data Source=C:\Users\Tamal Banerjee\source\repos\WpfMVVM\WpfMVVM\bin\Debug\Employees.db;";
        SQLiteConnection con;
        SQLiteCommand cmd;
        SQLiteDataAdapter adapter;
        DataSet ds;
        public ObservableCollection<Employees> employees { get; set; }
        public EmployeesViewModel()
        {
            FillDG();
        }

        public void FillDG()
        {
            try
            {
                con = new SQLiteConnection(connectionString);
                con.Open();
                cmd = new SQLiteCommand("select * from tblEmployees", con);
                adapter = new SQLiteDataAdapter(cmd);
                ds = new DataSet();
                adapter.Fill(ds, "tblEmployees");

                if (employees == null)
                    employees = new ObservableCollection<Employees>();

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    employees.Add(new Employees
                                  {
                                    empname = dr[0].ToString(),
                                    age = Convert.ToInt32(dr[1].ToString()),
                                    bdate = dr[2].ToString(),
                                    salary = (float)Convert.ToDecimal(dr[3].ToString()),
                                    remarks = dr[4].ToString(),
                                  });
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error");
            }
            finally
            {
                ds = null;
                adapter.Dispose();
                con.Close();
                con.Dispose();
            }
        }
        
        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged;

        private void OnPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }
        #endregion
    }
}

When I run the app the Datagrid in the first tabitem shows all the data instead of filtered data and the Datagrid in the second tabitem shows nothing. Please help!

BionicCode · Accepted Answer · 2022-04-05 13:59:08Z

You can simplify your code. Since this is a local SQLite database, you should expect the data not to change outside your application. Therefore, there is no need to query the database multiple times.

Instead of a filter query you should use the existing data set to apply a row filter on a dedicated DataView of the DataTable. DataTable and the DataView will automatically stay in sync unless you overwrite the source pf the DataView.
You can bind directly to a DataTable. This means you don't need to define column templates for the DataGrid. If you want to change the displayed column names, simply add a column mapping to the SQLiteDataAdapter.
And you also don't need to convert the DataTable returned by the SQLiteDataAdapter to an ObservableCollection.
In MVVM, your view should never handle data or data repositories e.g. database queries. This responsibility belongs into the model, for example a Repository class.
Don't toggle the Visibility of a dozen controls explicitly. Bind the the Visibility property of every relevant control to a (in your case) single boolean property and use the BooleanToVisibilityConverter to convert the value to a Visibility. Alternatively, wrap the controls into a common container like a StackPanel and then toggle its Visibility to hide/show its children (see example below).
Don't access arrays explicitly by hard coded indices. This doesn't scale. Always use a for loop to iterate over the elements. This way you don't have to modify your code if the length of the array changes. This applies to your FormattedDate method, which by the way can be simplified and improved using LINQ and string.Join (see in the example below).
Don't use the old ADO.NET library to handle databases! It's really old and the library is no longer maintained. Better learn the modern and superior Entity Framework Core (EF). It's really easier to use and it is completely async.
Don't close or dispose streams and other resources manually. Always delegate this to the library by using a using block.
Don't perform any complex or long-running operations, like querying a database or reading a file, in a constructor. If you use modern libraries like Entity Framework you even have an async API which you won't be able to use in a constructor.
Don't access inherited members (like DataContext in your code) using the base qualifier. Always reference the inherited properties from the derived type and use this unless you have a reason to go via the base class.
You should create a dedicated view model e.g. RowEditViewModel for the edit view. Better, introduce a SelectedEmployeeRow property to your view model and bind it to the DataGrid.SelectedItem property. Then bind the TextBox elements to it.
Prefer a TextBlock over a Label.

The following example shows the major fixes. It misses the update logic to push the changes to the database.

EmployeesViewModel.cs
You can find the implementation of the RelayCommad used in this example here: Microsoft Docs: Relaying Command Logic.

class EmployeesViewModel : INotifyPropertyChanged
{
  // TODO::Let property raise PropertyChanged event
  public DataTable Employees { get; private set; }

  // TODO::Let property raise PropertyChanged event
  public DataView FilteredEmployees { get; private set; }

  // TODO::Let property raise PropertyChanged event
  // TODO::Set property: this.IsEmployeeSelected = this.SelectedEmployeeRow != null
  public DataRowView SelectedEmployeeRow { get; set; }

  public RelayCommand UpdateEmployeesCommand { get; }

  // TODO::Let property raise PropertyChanged event
  public bool IsEmployeeSelected { get; set; }

  private Repository Repository { get; }

  public EmployeesViewModel()
  {
    this.Repository = new Repository();
    this.UpdateEmployeesCommand = new RelayCommand(ExecuteUpdateEmployeesCommand);
  }

  public void Initialize()
  {
    this.Employees = this.Repository.GetEmployees();
    this.FilteredEmployees = new DataView(this.Employees, "Comments = ' '", null, DataViewRowState.CurrentRows);
  }

  private void ExecuteUpdateEmployeesCommand(object commandParameter)
  {
    this.Repository.UpdateEmployees(this.Employees);
  }
}

Repository.cs

class Repository
{
  public DataTable GetEmployees()
  {
    using (var connection = new SQLiteConnection(@"Data Source=C:\Users\Tamal Banerjee\source\repos\WpfMVVM\WpfMVVM\bin\Debug\Employees.db;"))
    {
      using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM Users", connection))
      {
        using (var sqliteAdapter = new SQLiteDataAdapter(command))
        {
          connection.Open();
          var dataTable = new DataTable("Employees");
          DataTableMapping tableMapping = sqliteAdapter.TableMappings.Add("tblEmployees", "Employees");

          // Change column names (prettify for display/DataTable only) without breaking the database mapping.
          tableMapping.ColumnMappings.Add("EmpName", "Employee Name");
          tableMapping.ColumnMappings.Add("Birthdate", "Date of Birth");
          tableMapping.ColumnMappings.Add("Remarks", "Comments");

          sqliteAdapter.Fill(dataTable);
          return dataTable;
        }
      }
    }
  }

  public void UpdateEmployees(DataTable employees)
  {
    using (var connection = new SQLiteConnection(@"Data Source=C:\Users\Tamal Banerjee\source\repos\WpfMVVM\WpfMVVM\bin\Debug\Employees.db;"))
    {
      using (var command = new SQLiteCommand("SELECT * FROM Users", connection))
      {
        using (var sqliteAdapter = new SQLiteDataAdapter(command))
        {
          connection.Open();
          DataTableMapping tableMapping = sqliteAdapter.TableMappings.Add("tblEmployees", "Employees");

          // Use the column names (for display) to database mapping that was configured in the 'GetEmployees()'.
          tableMapping.ColumnMappings.Add("EmpName", "Employee Name");
          tableMapping.ColumnMappings.Add("Birthdate", "Date of Birth");
          tableMapping.ColumnMappings.Add("Remarks", "Comments");

          using var builder = new SQLiteCommandBuilder(sqliteAdapter);
          {
            sqliteAdapter.Update(employees);
          }
        }
      }
    }
  }
}

Window1.xaml.cs

partial class Window1 : Winbdow
{
  public Window1()
  {
    InitializeComponent();

    this.DataContext = new EmployeesViewModel();
    this.Loaded += OnLoaded;
  }

  private void OnLoaded(object sender, EventArgs e)
  {
    (this.DataContext as EmployeesViewModel).Initialize();
  }
   
  private static string FormattedDate(string init)
  {
    string[] tempArray = init.Split('-');
    string newFormat = string.Join('-', tempArray.Reverse());
    return newFormat;
  }

  private void DoSelectedRow(object sender, MouseButtonEventArgs e)
  {
    ...
  }

  private static Parent FindVisualParent<Parent>(DependencyObject child)   
    where Parent : DependencyObject
  {
    ...
  }
}

Window1.xaml

<Window>
  <Window.Resources>
    <BooleanToVisibilityConverter x:Key="BooleanToVisibilityConverter" />
    <Style TargetType="{x:Type DataGridCell}">
      <EventSetter Event="PreviewMouseLeftButtonDown" Handler="DoSelectedRow"/>
    </Style>
  </Window.Resources>

  <TabControl>
    <TabItem Header="General">
      <DataGrid x:Name="filteredGrid" 
                ItemsSource="{Binding FilteredEmployees}"
                CanUserAddRows="False"
                IsReadOnly="True" />

    </TabItem>
      <TabItem Header="CRUD">            
        <Grid>
          <Grid.RowDefinitions>
            <RowDefinition Height="Auto" />
            <RowDefinition />
          </Grid.RowDefinitions>

          <DataGrid x:Name="dataGrid1"
                    ItemsSource="{Binding Employees}"
                    SelectedItem="{Binding SelectedEmployeeRow}"
                    CanUserAddRows="False"
                    IsReadOnly="True"
                    SelectionMode="Single"
                    SelectionUnit="FullRow" />
              
          <StackPanel Grid.Row="1"
                      Visibility ="{Binding IsEmployeeSelected, Converter={StaticResource BooleanToVisibilityConverter}}" >
            <TextBlock x:Name="lblempname"
                       Content="Name :" />
            <TextBox Text={Binding SelectedItem[Employee Name] />

            <TextBlock x:Name="lblage"
                       Content="Age :" />
            <TextBox Text={Binding SelectedItem[Age] />

            <TextBlock x:Name="lblbdate"
                       Content="Date of Birth :" />
            <TextBox Text={Binding SelectedItem[Date of Birth] />

            <TextBlock x:Name="lblsalary"
                       Content="Salary :" />
            <TextBox Text={Binding SelectedItem[Salary] />

            <TextBlock x:Name="lblremarks"
                       Content="Comments :" />
            <TextBox Text={Binding SelectedItem[Comments] />

            <Button Content="Update" 
                    Command="{Binding UpdateEmployeesCommand}" />
          </StackPanel>
        </Grid>
      </TabItem>

      <TabItem Header="Details" />
  </TabControl>
</Window>

You can simply copy&paste the code. Then read my explanation and the in-code comments again and consider to use the debugger to understand what's happening. This is very simply. You must only understand that data handling is not the responsibility of the view. The view only displays data. In this example the Repository class is part of the MVVM model. The model is used by the view model to read or write data that is displayed in the view. The view model acts as a decoupling layer between the view and the model.
I already did that, the link (codeshare.io/nzJKyV) shows all the changes that I made after your suggestion. When I build I get no errors but when I run the app I'm getting System.Windows.Markup.XamlParseException: 'Set property 'System.Windows.Data.Binding.Converter' threw an exception.' Line number '38' and line position '17'. ---> System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Windows.Data.IValueConverter'.
Because the binding is wrong. It's already wrong in the example. I wrote the code here in the text editor without compiler support. That's why it slipped my attention. The fixed binding: Visibility ="{Binding IsEmployeeSelected, Converter={StaticResource BooleanToVisibilityConverter}}". The converter must be referenced using Staticresource. i have also fixed in my example.
Now it's showing System.Data.EvaluateException: Cannot find column [Remarks].
We have renamed the DataTable column to "Summary". Change the filter expression in the Initialize method and replace the"Remarks" in the string.

Collectives™ on Stack Overflow

How to bind multiple datagrids to a single SQLite database using MVVM pattern in WPF c#?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related