DelegateCommand RefreshCommand to refresh ObservableCollection-bound DataGrid

Question

I'm trying to update my DataGrid (name: dgLicenseholder, bound to an ObservableCollection. I am using MVVMLight) when I add new rows to the database. Why isn't this working?

In my ViewModel, I have a public DelegateCommand RefreshCommand {get; private set; } and a method:

private void Refresh() {

    licenseHolders.Add(new LicenseHolders());
}

licenseHolders is a ObservableCollection list, which looks like this:

public ObservableCollection<LicenseHolders> licenseHolders { get; }
    = new ObservableCollection<LicenseHolders>();

LicenseHolders is a class in my Model, that holds the data:

public class LicenseHolders {
    public int ID { get; set; }
    public string Foretaksnavn { get; set; }
    // more like the above...
}

In the XAML, I've bound the command to a button like so;

Command="{Binding RefreshCommand}"
CommandParameter="{Binding ElementName=dgLicenseHolder}"

My method to update the database is placed in the ViewModel, and called by a click-event in the code-behind for the window holding the DataGrid.

AddToDB()

NewLicenseHolder nlh = Application.Current.Windows.OfType<NewLicenseHolder>().FirstOrDefault();

try {

   using (SqlConnection sqlCon = new(connectionString))
   using (SqlCommand sqlCmd = new(sqlString, sqlCon)) {

        sqlCon.Open();
        
        sqlCmd.Parameters.AddWithValue("@Foretaksnavn, nlh.txtForetaksnavn.Text.ToString());
        // more of the above...
        sqlCmd.ExecuteNonQuery();
    }
}

Question 1:

What's wrong with the way I've bound my RefreshCommand? My method to add to the database works fine, but I have to re-open the window for the DataGrid to "catch" the change.

Question 2:

How can I bind the method I use to update the database without putting it in a click-event?

UPDATED CODE (trying to implement solution suggested by user ChrisBD)

LicenseHolder.cs - holder class (Model)

namespace Ridel.Hub.Model {

    public class LicenseHolder {

        public int ID { get; set; }
        public string Foretaksnavn { get; set; }
        public string Foretaksnummer { get; set; }
        public string Adresse { get; set; }
        public int Postnummer { get; set; }
        public string Poststed { get; set; }
        public string BIC { get; set; }
        public string IBAN { get; set; }
        public string Kontakt { get; set; }
        public string Epost { get; set; }
        public string Tlf { get; set; }
        public string Kontonummer { get; set; }
    }
}

RidelHubMainViewModel.cs (ViewModel)

using System;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Windows;
using System.Windows.Input;
using GalaSoft.MvvmLight.CommandWpf;
using Ridel.Hub.Model;

namespace Ridel.Hub.ViewModel {

    public class RidelHubMainViewModel : INotifyPropertyChanged {

        public event PropertyChangedEventHandler PropertyChanged;

        public ICommand RefreshCommand { get; private set; }

        public ObservableCollection<LicenseHolder> LicenseHolders { get; set; }

        public RidelHubMainViewModel() {

            RefreshCommand = new RelayCommand(this.ExecuteRefreshCommand);
            LicenseHolders = new ObservableCollection<LicenseHolder>();
            FillDataGridLicenseHolders();
        }

        private void ExecuteRefreshCommand() {

            NewLicenseHolder nlh = Application.Current.Windows.OfType<NewLicenseHolder>().FirstOrDefault();

            if (LicenseHolders == null) {

                LicenseHolders = new ObservableCollection<LicenseHolder>();

            } else {

                AddToDB();
                LicenseHolders.Clear();
                LicenseHolders.Add(new LicenseHolder() { Foretaksnavn = nlh.txtForetaksnavn.Text.ToString() });
                LicenseHolders.Add(new LicenseHolder() { Foretaksnummer = nlh.txtForetaksnr.Text.ToString() });
                LicenseHolders.Add(new LicenseHolder() { Adresse = nlh.txtAdresse.Text.ToString() });
                LicenseHolders.Add(new LicenseHolder() { Postnummer = Convert.ToInt32(nlh.txtPostnummer.Text.ToString()) });
                LicenseHolders.Add(new LicenseHolder() { Poststed = nlh.txtPoststed.Text.ToString() });
                LicenseHolders.Add(new LicenseHolder() { BIC = nlh.txtBIC.Text.ToString() });
                LicenseHolders.Add(new LicenseHolder() { IBAN = nlh.txtIBAN.Text.ToString() });
                LicenseHolders.Add(new LicenseHolder() { Kontakt = nlh.txtKontakt.Text.ToString() });
                LicenseHolders.Add(new LicenseHolder() { Epost = nlh.txtEpost.Text.ToString() });
                LicenseHolders.Add(new LicenseHolder() { Tlf = nlh.txtTlf.Text.ToString() });
                LicenseHolders.Add(new LicenseHolder() { Kontonummer = nlh.txtKontonr.Text.ToString() });
            }
        }

        public void FillDataGridLicenseHolders() {

            // Initially populates my DataGrid with the Sql server table
            try {

                using (SqlConnection sqlCon = new(ConnectionString.connectionString))
                using (SqlCommand sqlCmd = new("select * from tblLicenseHolder", sqlCon))
                using (SqlDataAdapter sqlDaAd = new(sqlCmd))
                using (DataSet ds = new()) {

                    sqlCon.Open();
                    sqlDaAd.Fill(ds, "tblLicenseHolder");

                    foreach (DataRow dr in ds.Tables[0].Rows) {

                        LicenseHolders.Add(new LicenseHolder {

                            ID = Convert.ToInt32(dr[0].ToString()),
                            Foretaksnavn = dr[1].ToString(),
                            Foretaksnummer = dr[2].ToString(),
                            Adresse = dr[3].ToString(),
                            Postnummer = (int)dr[4],
                            Poststed = dr[5].ToString(),
                            BIC = dr[6].ToString(),
                            IBAN = dr[7].ToString(),
                            Kontakt = dr[8].ToString(),
                            Epost = dr[9].ToString(),
                            Tlf = dr[10].ToString(),
                            Kontonummer = dr[11].ToString()
                        });
                    }
                }

            } catch (Exception ex) {

                MessageBox.Show(ex.Message, "Message", MessageBoxButton.OK, MessageBoxImage.Information);
            }
        }

        private void OnPropertyChanged(string myLicenseHolder) {

            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(myLicenseHolder));       
        }

        private void OnLicenseHoldersPropertyChanged() {

            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs("LicenseHolders"));
        }

        #region Slett løyvehaver
// How I delete rows from the DataGrid
        private static bool RemoveFromDB(LicenseHolder myLicenseHolder) {

            string sqlString = $"Delete from tblLicenseHolder where ID = '{myLicenseHolder.ID}'";
            
            if (MessageBox.Show("Er du sikker på at du ønsker å slette valgt løyvehaver?", "Slett løyvehaver", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.Yes) {
                
                try {

                    using (SqlConnection sqlCon = new(ConnectionString.connectionString))
                    using (SqlCommand sqlCmd = new(sqlString, sqlCon)) {

                        sqlCon.Open();
                        sqlCmd.ExecuteNonQuery();
                        return true;
                    }

                } catch {

                    return false;
                }

            } else {

                return false;
            }
        }

        private void RemoveLicenseHolderExecute(LicenseHolder myLicenseHolder) {
            
            bool result = RemoveFromDB(myLicenseHolder);
            if (result)
                LicenseHolders.Remove(myLicenseHolder);
        }

        private RelayCommand<LicenseHolder> _removeLicenseHoldersCommand;

        public RelayCommand<LicenseHolder> RemoveLicenseHoldersCommand => _removeLicenseHoldersCommand
            ??= new RelayCommand<LicenseHolder>(RemoveLicenseHolderExecute, RemoveLicenseHolderCanExecute);

        private bool RemoveLicenseHolderCanExecute(LicenseHolder myLicenseHolder) {

            return LicenseHolders.Contains(myLicenseHolder);
        }

        #endregion

        public void AddToDB() {

            string sqlString = "insert into tblLicenseHolder (Foretaksnavn, Foretaksnummer, Adresse, Postnummer, Poststed, BIC, IBAN, Kontakt, Epost, Tlf, Kontonummer) " +
                "values (@Foretaksnavn, @Foretaksnummer, @Adresse, @Postnummer, @Poststed, @BIC, @IBAN, @Kontakt, @Epost, @Tlf, @Kontonummer)";

            NewLicenseHolder nlh = Application.Current.Windows.OfType<NewLicenseHolder>().FirstOrDefault();

            try {

                using (SqlConnection sqlCon = new(ConnectionString.connectionString))
                using (SqlCommand sqlCmd = new(sqlString, sqlCon)) {

                    sqlCon.Open();

                    if (string.IsNullOrEmpty(nlh.txtForetaksnavn.Text) || string.IsNullOrEmpty(nlh.txtForetaksnr.Text)
                        || string.IsNullOrEmpty(nlh.txtAdresse.Text) || string.IsNullOrEmpty(nlh.txtPostnummer.Text)
                        || string.IsNullOrEmpty(nlh.txtPoststed.Text) || string.IsNullOrEmpty(nlh.txtBIC.Text)
                        || string.IsNullOrEmpty(nlh.txtIBAN.Text) || string.IsNullOrEmpty(nlh.txtKontakt.Text)
                        || string.IsNullOrEmpty(nlh.txtEpost.Text) || string.IsNullOrEmpty(nlh.txtTlf.Text)
                        || string.IsNullOrEmpty(nlh.txtKontonr.Text)) {

                        MessageBox.Show("Vennligst fyll ut alle tekstboksene.");
                        sqlCon.Close();

                    } else {                       

                        sqlCmd.Parameters.AddWithValue("@Foretaksnavn", nlh.txtForetaksnavn.Text.ToString());
                        sqlCmd.Parameters.AddWithValue("@Foretaksnummer", nlh.txtForetaksnr.Text.ToString());
                        sqlCmd.Parameters.AddWithValue("@Adresse", nlh.txtAdresse.Text.ToString());
                        sqlCmd.Parameters.AddWithValue("@Postnummer", nlh.txtPostnummer.Text);
                        sqlCmd.Parameters.AddWithValue("@Poststed", nlh.txtPoststed.Text.ToString());
                        sqlCmd.Parameters.AddWithValue("@BIC", nlh.txtBIC.Text.ToString());
                        sqlCmd.Parameters.AddWithValue("@IBAN", nlh.txtIBAN.Text.ToString());
                        sqlCmd.Parameters.AddWithValue("@Kontakt", nlh.txtKontakt.Text.ToString());
                        sqlCmd.Parameters.AddWithValue("@Epost", nlh.txtEpost.Text.ToString());
                        sqlCmd.Parameters.AddWithValue("@Tlf", nlh.txtTlf.Text.ToString());
                        sqlCmd.Parameters.AddWithValue("@Kontonummer", nlh.txtKontonr.Text.ToString());
                        sqlCmd.ExecuteNonQuery();
                                                                  
                        MessageBox.Show("Ny løyvehaver lagret. Husk å oppdatere listen.");
                    }
                }

            } catch (Exception ex) {

                MessageBox.Show(ex.Message, "Message", MessageBoxButton.OK, MessageBoxImage.Information);
            }
        }
    }
}

View, Code-behind - Parent-window (LicenseHoldersWindow)

using System.Windows;
using System.Windows.Controls;
using Ridel.Hub.ViewModel;

namespace Ridel.Hub {

    public partial class LicenseHoldersWindow : Window {

        public LicenseHoldersWindow() {
            
             InitializeComponent();
             DataContext = new RidelHubMainViewModel();
        }

        private btnNew_Click(object sender, RoutedEventArgs e) {

             NewLicenseHolder newLicenseHolder = new();
             newLicenseHolder.ShowDialog();
        }
    }
}

View, XAML - Parent-window (LicenseHoldersWindow)

<Window
    x:Class="Ridel.Hub.LicenseHoldersWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:local="clr-namespace:Ridel.Hub"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:viewmodel="clr-namespace:Ridel.Hub.ViewModel"
    Title="License Holders"
    d:DataContext="{d:DesignInstance Type=viewmodel:RidelHubMainViewModel}"
    mc:Ignorable="d">

<!-- GUI stuff -->
<!-- also button to open new child-window "NewLicenseHolder" -->

<DataGrid
        x:Name="dgLicenseHolder"       
        AutoGenerateColumns="False"       
        IsReadOnly="True"
        ItemsSource="{Binding LicenseHolders, Mode=OneWay}"
        SelectionChanged="dgLicenseHolder_SelectionChanged"
        SelectionMode="Single">

View, code-behind- child-window (NewLicenseHolder)

using System.ComponentModel;
using System.Windows;
using Prism.Commands;
using Ridel.Hub.ViewModel;

namespace Ridel.Hub {

    public partial class NewLicenseHolder : Window, INotifyPropertyChanged {

        public NewLicenseHolder() {
            
            InitializeComponent();
            btnLogOut.Content = UserInfo.UserName;
            DataContext = new RidelHubMainViewModel();
        }

        public event PropertyChangedEventHandler PropertyChanged;

        private void btnLagre_Click(object sender, RoutedEventArgs e) {

        // Button: Save new LicenseHolder
           ((RidelHubMainViewModel)DataContext).RefreshCommand.Execute(null);
           this.Close();
        }
    }
}

View, XAML- child-window (NewLicenseHolder)

<Window
    x:Class="Ridel.Hub.NewLicenseHolder"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:viewmodel="clr-namespace:Ridel.Hub.ViewModel"
    xmlns:i="clr-namespace:System.Windows.Interactivity;assembly=System.Windows.Interactivity"
    d:DataContext="{d:DesignInstance Type=viewmodel:RidelHubMainViewModel}"
    mc:Ignorable="d">

<Button
      Name="btnLagre"
      Foreground="White"
      HorizontalContentAlignment="Center"
      Click="btnLagre_Click"
      Command="{Binding RefreshCommand}"
      Style="{DynamicResource ButtonWithRoundCornersGreen}" >
</Button>

Ok, so, if you read through all of this: God bless. As you see, I add the new row-info in a child-form, click save, and ideally, would like my DataGrid to then be automatically updated (without clicking any additional "refresh" or "update"-buttton.

What happens now is, when I click save, I get a error: System.NullReferenceException: 'Object reference not set to an instance of an object.' Local1 was null. inside my AddToDB()-method, at the if-else statement where I check that none of the textboxes are empty. So there's obviously something wrong with the way I reference the NewLicenseHolder class inside of the ViewModel:

NewLicenseHolder nlh = Application.Current.Window.OfType<NewLicenseHolder>().FirstOrDefault();

How can I reference it properly, and make sure the DataGrid is updated when I leave my child form and go back to the parent form?

Many thanks, very appreciative for any guidance to correct my thinking!

Answer 1

It would appear that I may have confused MVVM frameworks - anyway here's some working code for MVVMLite.

   public class LicenseHolder
{
    public string Name { get; set; }
}

public class ViewModel
{
    public ViewModel()
    {
        RefreshCommand = new GalaSoft.MvvmLight.CommandWpf.RelayCommand(this.ExecuteRefreshCommand);
        LicenseHolders = new ObservableCollection<LicenseHolder>();
    }
    public ICommand RefreshCommand { get; private set; }
    public ObservableCollection<LicenseHolder> LicenseHolders { get; set; }
    private void ExecuteRefreshCommand(object o)
    {
        if (LicenseHolders == null)
        {
            LicenseHolders = new ObservableCollection<LicenseHolder>();
        }
        LicenseHolders.Clear();
        //Code here for async retrieval of licence holder records from database 
        //and addition of new LicenceHolder instances to the LicenceHolders collection
        LicenseHolders.Add(new LicenseHolder(){Name ="Ted"});
        LicenseHolders.Add(new LicenseHolder(){Name = "Bill"});

    }
}

View:

<Window x:Class="WpfApp1.MainWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:local="clr-namespace:WpfApp1"
    mc:Ignorable="d"
    Title="MainWindow" Height="450" Width="800">
<Window.Resources>
    <local:ViewModel x:Key="ViewModelAsDataSource" />
</Window.Resources>

<Grid>
    <Grid.RowDefinitions>
        <RowDefinition />
        <RowDefinition />
        <RowDefinition />
        <RowDefinition />
    </Grid.RowDefinitions>
    <Grid.ColumnDefinitions>
        <ColumnDefinition Width="40"/>
        <ColumnDefinition/>
        <ColumnDefinition Width="40"/>
    </Grid.ColumnDefinitions>
    <DataGrid Grid.Column="1" Grid.Row="1" ItemsSource="{Binding LicenseHolders }" AutoGenerateColumns="false">
        <DataGrid.Columns>
            <DataGridTextColumn Header="License Holder Name" Width="Auto" MinWidth="18" Binding="{Binding Name }" />
        </DataGrid.Columns>

    </DataGrid>

    <Button Grid.Column="1" Grid.Row="2" Content="Refresh" Command="{Binding RefreshCommand}" Width="50" Height="30"/>
     <Button Grid.Column="1" Grid.Row="3" Content="Refresh" Click="ButtonBase_OnClick" Width="50" Height="30"/>

Code behind:

    public partial class MainWindow : Window
{
    public MainWindow()
    {
        InitializeComponent();
        DataContext = new ViewModel();
    }
    private void ButtonBase_OnClick(object sender, RoutedEventArgs e)
    {
        ((ViewModel)DataContext).RefreshCommand.Execute(null);
    }

}

Answer 2

Okay there's much that I don't see here on how the database code is linked to your code but this might help:

public class ViewModel
{
    public ViewModel() 
    {
        RefreshCommand = new Command(async()=> await ExecuteRefreshCommand());
        LicenceHolders = new ObservableCollection<LicenceHolder>();
    }

    public ICommand RefreshCommand { get; private set; }
    public ObservableCollection<LicenceHolder> LicenceHolders { get; set; }

    private async Task ExecuteRefreshCommand()
    {
        if (LicenceHolders == null)
        {
            LicenceHolders = new ObservableCollection<LicenceHolder>();
        }
        LicenceHolders.Clear();

        //Code here for async retrieval of licence holder records from database 
        //and addition of new LicenceHolder instances to the LicenceHolders collection
    }
}

In the View XAML

<Button Text="Refresh" Command="{Binding RefreshCommand}"/> this will trigger the RefreshCommand

View code behind with ViewModel injection

public partial class SomeView : ContentPage
{
    public SomeView(ViewModel viewModel) 
    {
        InitializeComponent();
        BindingContext = viewModel;
    }
}

otherwise

public partial class SomeView : ContentPage
    {
        public SomeView() 
        {
            InitializeComponent();
            BindingContext = new ViewModel();
        }
    }

Ideally you'll want to use Commands to trigger events or processing in your ViewModel. This can be triggered by binding in the View as above or from the code behind like so

private void Button_OnClick(object sender, OnClickEventArgs e)
{
   ((ViewModel)BindingContext).RefreshCommand.Execute();
}

Strictly we should use a flag to indicate that the asynchronous DB access is ongoing - this can be achieved by use of a public boolean (flag) in the ViewModel that is used as a CanExecute condition in the Command instance creation, which will prevent multiple calls to the same code occuring until such time as its completed. e.g.

ViewModel: bool IsBusy {get;set;} set this true before calling your asynchronous data access routine and set false when completed

in the constructor, Command instantiation now becomes RefreshCommand = new Command(async()=> await ExecuteRefreshCommand(), ()=>!IsBusy);

XAML binding will automatically pick this up, but if you're using code behind code to trigger the Command this becomes:

if (((ViewModel)BindingContext).RefreshCommand.CanExecute())
{
   ((ViewModel)BindingContext).RefreshCommand.Execute();
}

Note: If you replace the LicenceHolders ObservableCollection with a new instance at runtime, rather than alter its contents then you will need to raise/invoke the PropertyChanged manually by calling an OnPropertyChanged handler. e.g.

protected void OnLicenHoldersPropertyChanged()
{
   PropertyChanged?.Invoke(this, new PropertyChangedEventArgs("LicenceHolders"));
}