How to save all the updates in a datagrid (set of comboboxes) MVVM back to the database in WPF C#?

Question

I have similar issue as @Mesh Ka working on the filtering between comboboxes. https://learn.microsoft.com/en-us/answers/questions/1481853/filtering-a-combobox-in-a-datagrid-based-on-anothe Thanks for the solution! The challenge I have is to save all the changes back to the database when user updates those comboboxes in the datagrid under MVVM. I can create DAL to do the save but need to figure out which row is for update/delete or even an insert on a new row.

Answer 1

Hi, you can try my Solution:

XAML MainWindow:

<Window x:Class="WpfApp1.Window003"
        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:WpfApp003"
        xmlns:uc="clr-namespace:WpfApp003"
        mc:Ignorable="d"
        Title="Yvon_240305" Height="600" Width="900">
  <Window.Resources>
    <local:ViewModelMain x:Key="vm"/>
  </Window.Resources>
  <Grid DataContext="{StaticResource vm}" Margin="10">
    <Grid.Resources>
      <Style TargetType="Button">
        <Setter Property="Margin" Value="10"/>
        <Setter Property="Width" Value="150"/>
        <Setter Property="Height" Value="25"/>
        <Setter Property="VerticalAlignment" Value="Top"/>
      </Style>
    </Grid.Resources>
    <Grid.RowDefinitions>
      <RowDefinition Height="auto"/>
      <RowDefinition Height="200"/>
      <RowDefinition Height="auto"/>
      <RowDefinition Height="200"/>
      <RowDefinition/>
    </Grid.RowDefinitions>
    <Grid.ColumnDefinitions>
      <ColumnDefinition/>
      <ColumnDefinition Width="220"/>
    </Grid.ColumnDefinitions>
    <Label Grid.Row="0" Grid.Column="0" Content="TeacherAddress" Margin="5"/>
    <uc:PlacesDataGrid Grid.Row="1" Grid.Column="0" Margin="5"
                       DataGridItemsSource="{Binding AllTeachers}"/>
    <Button Grid.Row="1" Grid.Column="1" Margin="5" Width="150" HorizontalAlignment="Left"
            Content="AddTeacherAddress" 
            Command="{Binding Cmd}" CommandParameter="AddTeacher"/>
    <Label Grid.Row="2" Grid.Column="0" Content="StudentAddress" Margin="5"/>
    <uc:PlacesDataGrid Grid.Row="3" Grid.Column="0" Margin="5"
                       DataGridItemsSource="{Binding AllStudents}"/>
    <Button Grid.Row="3" Grid.Column="1"  Margin="5" Width="150" HorizontalAlignment="Left"
            Content="AddStudentAddress"
            Command="{Binding Cmd}" CommandParameter="AddStudent"/>
    <StackPanel Grid.Row="4" Grid.ColumnSpan="2" HorizontalAlignment="Right" Orientation="Horizontal" >
      <Button Content="CancelAll" Command="{Binding Cmd}" CommandParameter="CancelAll" Margin="5"/>
      <Button Content="SaveAll" Command="{Binding Cmd}" CommandParameter="SaveAll" Margin="5"/>
    </StackPanel>
  </Grid>
</Window>

XAML UserControl:

<UserControl x:Name="userControl"
             x:Class="WpfApp003.PlacesDataGrid"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
             xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 
             xmlns:local="clr-namespace:WpfApp003"
             xmlns:vm="clr-namespace:WpfApp003"
             mc:Ignorable="d" 
             d:DesignHeight="450" d:DesignWidth="800">
  <UserControl.Resources>
    <vm:PlacesDataGridViewModel x:Key="viewModel"/>
    <local:ConvProvinceID x:Key="ConvProvenceID"/>
    <local:ConvDistrictID x:Key="ConvDistrictID"/>
    <Color x:Key="GlyphColor">#FF444444</Color>
  </UserControl.Resources>
  <Grid x:Name="grid" DataContext="{StaticResource viewModel}">
    <DataGrid x:Name="dg"
              AutoGenerateColumns="False"
              CanUserAddRows="False"
              ItemsSource="{Binding DataGridItemsSource, RelativeSource={RelativeSource AncestorType=UserControl}}"
              SelectedItem="{Binding SelectedItem}"
              local:PlacesDataGridViewModel.AttProp="True">
      <DataGrid.Columns>
        <DataGridTemplateColumn Header="Country" Width="120">
          <DataGridTemplateColumn.CellTemplate>
            <DataTemplate>
              <ComboBox
                ItemsSource="{Binding Countries, Source={StaticResource viewModel}}"
                DisplayMemberPath="CountryName"
                SelectedValue="{Binding CountryName, UpdateSourceTrigger=PropertyChanged}"
                SelectedValuePath="CountryName">
              </ComboBox>
            </DataTemplate>
          </DataGridTemplateColumn.CellTemplate>
        </DataGridTemplateColumn>
        <DataGridTemplateColumn Header="Province" Width="130">
          <DataGridTemplateColumn.CellTemplate>
            <DataTemplate>
              <Grid>
                <Grid.ColumnDefinitions>
                  <ColumnDefinition />
                  <ColumnDefinition Width="20" />
                </Grid.ColumnDefinitions>
                <TextBlock>
                  <TextBlock.Text>
                    <MultiBinding Converter="{StaticResource ConvProvenceID}">
                      <Binding Path="ProvinceID"/>
                      <Binding Path="Provinces" Source="{StaticResource viewModel}"/>
                    </MultiBinding>
                  </TextBlock.Text>
                </TextBlock>
                <Grid Grid.Column="1" MouseDown="Path_MouseDown">
                  <Path HorizontalAlignment="Center"
                        VerticalAlignment="Center"
                        Data="M 0 0 L 4 4 L 8 0 Z" >
                    <Path.Fill>
                      <SolidColorBrush Color="{DynamicResource GlyphColor}"/>
                    </Path.Fill>
                  </Path>
                </Grid>
              </Grid>
            </DataTemplate>
          </DataGridTemplateColumn.CellTemplate>
          <DataGridTemplateColumn.CellEditingTemplate>
            <DataTemplate DataType="ComboBox">
              <ComboBox
                DisplayMemberPath="ProvinceName"
                ItemsSource="{Binding CurrentProvinces, Source={StaticResource viewModel}}"
                SelectedValue="{Binding ProvinceID, UpdateSourceTrigger=PropertyChanged}"
                SelectedValuePath="ProvinceID" />
            </DataTemplate>
          </DataGridTemplateColumn.CellEditingTemplate>
        </DataGridTemplateColumn>
        <DataGridTemplateColumn Header="District" Width="140">
          <DataGridTemplateColumn.CellTemplate>
            <DataTemplate>
              <Grid>
                <Grid.ColumnDefinitions>
                  <ColumnDefinition />
                  <ColumnDefinition Width="20" />
                </Grid.ColumnDefinitions>
                <TextBlock>
                  <TextBlock.Text>
                    <MultiBinding Converter="{StaticResource ConvDistrictID}">
                      <Binding Path="DistrictID"/>
                      <Binding Path="Districts" Source="{StaticResource viewModel}"/>
                    </MultiBinding>
                  </TextBlock.Text>
                </TextBlock>
                <Grid Grid.Column="1" MouseDown="Path_MouseDown">
                  <Path Grid.Column="1"
                      HorizontalAlignment="Center"
                      VerticalAlignment="Center"
                      Data="M 0 0 L 4 4 L 8 0 Z" >
                    <Path.Fill>
                      <SolidColorBrush Color="{DynamicResource GlyphColor}"/>
                    </Path.Fill>
                  </Path>
                </Grid>
              </Grid>
            </DataTemplate>
          </DataGridTemplateColumn.CellTemplate>
          <DataGridTemplateColumn.CellEditingTemplate>
            <DataTemplate>
              <ComboBox
                DisplayMemberPath="DistrictName"
                ItemsSource="{Binding CurrentDistricts, Source={StaticResource viewModel}}"
                SelectedValue="{Binding DistrictID, UpdateSourceTrigger=PropertyChanged}"
                SelectedValuePath="DistrictID" />
            </DataTemplate>
          </DataGridTemplateColumn.CellEditingTemplate>
        </DataGridTemplateColumn>
        <DataGridTemplateColumn Header="Operation">
          <DataGridTemplateColumn.CellTemplate>
            <DataTemplate>
              <Button Content="Delete Record" Margin="2"
                      Command="{Binding CmdDelete, Source={StaticResource viewModel}}" 
                      CommandParameter="{Binding}" />
            </DataTemplate>
          </DataGridTemplateColumn.CellTemplate>
        </DataGridTemplateColumn>
      </DataGrid.Columns>
    </DataGrid>
  </Grid>
</UserControl>

CodeBehind UserControl:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Input;
using System.Windows.Media;
using WpfApp1;
namespace WpfApp003
{
	/// <summary>
	/// Interaction logic for Window003UC1.xaml
	/// </summary>
	public partial class PlacesDataGrid : UserControl
	{
		public PlacesDataGrid()
		{
			InitializeComponent();
		}
		public static readonly DependencyProperty DataGridItemsSourceProperty =
				DependencyProperty.Register("DataGridItemsSource", typeof(IEnumerable), typeof(PlacesDataGrid), new PropertyMetadata(null));
		public IEnumerable DataGridItemsSource
		{
			get { return (IEnumerable)GetValue(DataGridItemsSourceProperty); }
			set { SetValue(DataGridItemsSourceProperty, value); }
		}
		private void Path_MouseDown(object sender, MouseButtonEventArgs e)
		{
			HitTestResult hitTestResult = VisualTreeHelper.HitTest(dg, e.GetPosition(dg));
			DataGridRow dataGridRow = hitTestResult.VisualHit.GetParentOfType<DataGridRow>();
			if (dataGridRow == null) return;
			int index = dataGridRow.GetIndex();
			var item = dg.Items[index];
			if (dg.SelectedItem != item || dg.SelectedItem == null) dg.SelectedItem = item;
			DataGridCell dgc = hitTestResult.VisualHit.GetParentOfType<DataGridCell>();
			if (dgc == null) return;
			dgc.Focus();
			dg.BeginEdit();
		}
		public T GetParentOfType<T>(DependencyObject element) where T : DependencyObject
		{
			Type type = typeof(T);
			if (element == null) return null;
			DependencyObject parent = VisualTreeHelper.GetParent(element);
			if (parent == null && ((FrameworkElement)element).Parent is DependencyObject) parent = ((FrameworkElement)element).Parent;
			if (parent == null) return null;
			else if (parent.GetType() == type || parent.GetType().IsSubclassOf(type)) return parent as T;
			return GetParentOfType<T>(parent);
		}
	}
	public class ConvProvinceID : IMultiValueConverter
	{
		public object Convert(object[] values, Type targetType, object parameter, CultureInfo culture)
		{
			if (values[0] == DependencyProperty.UnsetValue || values[1] == DependencyProperty.UnsetValue || values[1] == null) return string.Empty;
			int i = (int)values[0];
			IList<DataRow> l = (IList<DataRow>)values[1];
			return i > 0 && (l.Count > 0) ? ((Window003DS.ProvincesRow)l[i - 1]).ProvinceName : string.Empty;
		}
		public object[] ConvertBack(object value, Type[] targetTypes, object parameter, CultureInfo culture)
		{
			throw new NotImplementedException();
		}
	}
	public class ConvDistrictID : IMultiValueConverter
	{
		public object Convert(object[] values, Type targetType, object parameter, CultureInfo culture)
		{
			if (values[0] == DependencyProperty.UnsetValue || values[1] == DependencyProperty.UnsetValue || values[1] == null) return string.Empty;
			int i = (int)values[0];
			IList<DataRow> l = (IList<DataRow>)values[1];
			return (i > 0 && l.Count > 0) ? ((Window003DS.DistrictsRow)l[i - 1]).DistrictName : string.Empty;
		}
		public object[] ConvertBack(object value, Type[] targetTypes, object parameter, CultureInfo culture)
		{
			throw new NotImplementedException();
		}
	}
}

Additional classes:

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Input;
using System.Windows.Media;
using WpfApp1;
namespace WpfApp003
{
	public partial class PlacesDataGridViewModel
	{
		public PlacesDataGridViewModel() { LoadData(); }
		private void LoadData()
		{
			DAL da = new DAL();
			this.Countries = new ObservableCollection<DataRow>(da.LoadCountries());
			this.Provinces = new ObservableCollection<DataRow>(da.LoadProvinces());
			this.Districts = new ObservableCollection<DataRow>(da.LoadDistricts());
		}
		public ObservableCollection<DataRow> Countries { get; set; }
		public ObservableCollection<DataRow> Provinces { get; set; }
		public ObservableCollection<DataRow> Districts { get; set; }
		public object CurrentProvinces => (new DAL()).ProvincesByCountry(SelectedItem?.Row);
		public object CurrentDistricts => (new DAL()).DistrictsByProvinceID(SelectedItem?.Row);
		public DataRowView SelectedItem { get; set; }
		public ICommand CmdDelete { get => new RelayCommand(CmdDeleteExec); }
		private void CmdDeleteExec(object obj) => (obj as DataRowView).Delete();
		public static readonly DependencyProperty AttPropProperty = DependencyProperty.Register("AttProp",
			typeof(bool), typeof(DataGrid), new UIPropertyMetadata(false, OnAttProp));
		public static bool GetAttProp(DependencyObject obj) => (bool)obj.GetValue(AttPropProperty);
		public static void SetAttProp(DependencyObject obj, bool value) => obj.SetValue(AttPropProperty, value);
		private static void OnAttProp(DependencyObject depObj, DependencyPropertyChangedEventArgs e)
		{
			var dg = depObj as DataGrid; if (dg == null) return;
			if ((e.NewValue is bool) && (bool)(e.NewValue)) dg.Loaded += Dg_Loaded;
		}
		private static void Dg_Loaded(object sender, RoutedEventArgs e)
		{
			DataGrid dg = sender as DataGrid;
			if (dg == null) return;
			PlacesDataGridViewModel vm = dg.DataContext as PlacesDataGridViewModel;
			if (vm == null) return;
			vm.dg = dg;
		}
		DataGrid dg;
		public static T GetChildOfType<T>(DependencyObject depObj) where T : DependencyObject
		{
			if (depObj == null) return null;
			for (int i = 0; i < VisualTreeHelper.GetChildrenCount(depObj); i++)
			{
				var child = VisualTreeHelper.GetChild(depObj, i);
				var result = (child as T) ?? GetChildOfType<T>(child);
				if (result != null) return result;
			}
			return null;
		}
	}
	public class ViewModelMain : INotifyPropertyChanged
	{
		public ViewModelMain()
		{
			DAL da = new DAL();
			da.LoadData();
			cvsTeachers.Source = da.GetTeachers();
			cvsStudents.Source = da.GetStudents();
			OnPropertyChanged(nameof(AllTeachers));
			OnPropertyChanged(nameof(AllStudents));
		}
		private CollectionViewSource cvsTeachers = new CollectionViewSource();
		public ICollectionView AllTeachers { get => cvsTeachers.View; }
		private CollectionViewSource cvsStudents = new CollectionViewSource();
		public ICollectionView AllStudents { get => cvsStudents.View; }
		public ICommand Cmd { get => new RelayCommand(CmdExec); }
		private void CmdExec(object obj)
		{
			switch (obj.ToString())
			{
				case "AddTeacher":
					(new DAL()).AddTeacher();
					break;
				case "AddStudent":
					(new DAL()).AddStudent();
					break;
				case "SaveAll":
					(new DAL()).SaveAll();
					break;
				case "CancelAll":
					(new DAL()).CancelAll();
					OnPropertyChanged(nameof(AllTeachers));
					OnPropertyChanged(nameof(AllStudents));
					break;
				default:
					break;
			}
		}
		public event PropertyChangedEventHandler PropertyChanged;
		private void OnPropertyChanged([CallerMemberName] string propName = "") => PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propName));
	}
	public class DAL
	{
		private string ConnString = WpfApp1.Properties.Settings.Default.cnSQL_CollegDB;
		private static Window003DS ds;
		public void LoadData()
		{
			ds = new Window003DS();
			using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Teachers", ConnString)) da.Fill(ds.Teachers);
			using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Students", ConnString)) da.Fill(ds.Students);
			using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Countries", ConnString)) da.Fill(ds.Countries);
			using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Provinces", ConnString)) da.Fill(ds.Provinces);
			using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Districts", ConnString)) da.Fill(ds.Districts);
			ds.Teachers.RowChanged += (sender, e) =>
			{
				if (e.Action == DataRowAction.Change)
				{
					Window003DS.TeachersRow row = e.Row as Window003DS.TeachersRow;
					if (ProvincesByCountry(row).Where((p) => p.ProvinceID == row.ProvinceID).FirstOrDefault() == null && row.ProvinceID != 0) row.ProvinceID = 0;
					if (DistrictsByProvinceID(row).Where((p) => p.DistrictID == row.DistrictID).FirstOrDefault() == null && row.DistrictID != 0) row.DistrictID = 0;
				}
			};
			ds.Students.RowChanged += (sender, e) =>
			{
				if (e.Action == DataRowAction.Change)
				{
					Window003DS.StudentsRow row = e.Row as Window003DS.StudentsRow;
					if (ProvincesByCountry(row).Where((p) => p.ProvinceID == row.ProvinceID).FirstOrDefault() == null && row.ProvinceID != 0) row.ProvinceID = 0;
					if (DistrictsByProvinceID(row).Where((p) => p.DistrictID == row.DistrictID).FirstOrDefault() == null && row.DistrictID != 0) row.DistrictID = 0;
				}
			};
		}
		internal object GetTeachers() => ds.Teachers;
		internal object GetStudents() => ds.Students;
		internal void AddTeacher() => ds.Teachers.Rows.Add(ds.Teachers.NewRow());
		internal void AddStudent() => ds.Students.Rows.Add(ds.Students.NewRow());
		internal void SaveAll()
		{
			using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Teachers", ConnString))
			{
				SqlCommandBuilder cb = new SqlCommandBuilder(da);
				da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
				da.RowUpdated += OnRowUpdated;
				da.Update(ds.Teachers);
			}
			using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Students", ConnString))
			{
				SqlCommandBuilder cb = new SqlCommandBuilder(da);
				da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
				da.RowUpdated += OnRowUpdated;
				da.Update(ds.Students);
			}
		}
		private void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e)
		{
			// Include a variable and a command to retrieve the identity value from the Access database.
			SqlCommand idCMD = new SqlCommand("SELECT @@IDENTITY", e.Command.Connection);
			if (e.StatementType != StatementType.Insert) return;
			// Retrieve the identity value and store it in the ID column.
			object newID = idCMD.ExecuteScalar();
			// ID column
			DataColumn primCol = e.Row.Table.PrimaryKey[0];
			e.Row[primCol] = Convert.ChangeType(newID, primCol.DataType);
		}
		internal void CancelAll() => ds.RejectChanges();
		public IEnumerable<DataRow> LoadCountries() => ds.Countries.AsEnumerable();
		public IEnumerable<DataRow> LoadProvinces() => ds.Provinces.AsEnumerable();
		public IEnumerable<DataRow> LoadDistricts() => ds.Districts.AsEnumerable();
		internal List<Window003DS.ProvincesRow> ProvincesByCountry(DataRow item)
		{
			if (item == null) return null;
			var countryName = item?.GetType().GetProperty("CountryName")?.GetValue(item, null);
			if (countryName == null) return null;
			return ds.Provinces.Where((p) => ((Window003DS.ProvincesRow)p).CountryName == countryName.ToString()).ToList();
		}
		internal List<Window003DS.DistrictsRow> DistrictsByProvinceID(DataRow item)
		{
			var provinceID = item?.GetType().GetProperty("ProvinceID")?.GetValue(item, null);
			if (provinceID == null) return null;
			return ds.Districts.Where((p) => ((Window003DS.DistrictsRow)p).ProvinceID == (int)provinceID).ToList().ToList();
		}
	}
	public class RelayCommand : ICommand
	{
		private readonly Predicate<object> _canExecute;
		private readonly Action<object> _action;
		public RelayCommand(Action<object> action) { _action = action; _canExecute = null; }
		public RelayCommand(Action<object> action, Predicate<object> canExecute) { _action = action; _canExecute = canExecute; }
		public void Execute(object o) => _action(o);
		public bool CanExecute(object o) => _canExecute == null ? true : _canExecute(o);
		public event EventHandler CanExecuteChanged
		{
			add { CommandManager.RequerySuggested += value; }
			remove { CommandManager.RequerySuggested -= value; }
		}
	}
}
namespace WpfApp003
{
	public static class MyExtensions
	{
		public static T GetParentOfType<T>(this DependencyObject element) where T : DependencyObject
		{
			Type type = typeof(T);
			if (element == null) return null;
			DependencyObject parent = VisualTreeHelper.GetParent(element);
			if (parent == null && ((FrameworkElement)element).Parent is DependencyObject) parent = ((FrameworkElement)element).Parent;
			if (parent == null) return null;
			else if (parent.GetType() == type || parent.GetType().IsSubclassOf(type)) return parent as T;
			return GetParentOfType<T>(parent);
		}
		public static IEnumerable<T> Query<T>(this IDbConnection con, string sql)
		{
			if (con.State == ConnectionState.Closed) con.Open();
			using (IDbCommand cmd = con.CreateCommand())
			{
				cmd.CommandText = sql;
				IDataReader rdr = cmd.ExecuteReader();
				while (rdr.Read())
				{
					T obj = (T)Activator.CreateInstance(typeof(T));
					for (int i = 0; i < rdr.FieldCount; i++)
					{
						PropertyInfo prop = obj.GetType().GetProperty(rdr.GetName(i), BindingFlags.Public | BindingFlags.Instance);
						if (null != prop && prop.CanWrite) prop.SetValue(obj, rdr.GetValue(i), null);
					}
					yield return obj;
				}
			}
		}
	}
}

typed DataSet:

Answer 2