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

DAL.cs

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using WpfSimple_v2.Models;

namespace WpfSimple_v2

{

public class DAL

{



    private static CDataSet ds;

    public void LoadData()

    {

        ds = new CDataSet();

        using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Reflectors", ConnString)) da.Fill(ds.Reflectors);

        using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM ReflectorList", ConnString)) da.Fill(ds.ReflectorList);

        using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Moderators where partnumber = '1234'", ConnString)) da.Fill(ds.Moderators);

        using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM ModeratorType", ConnString)) da.Fill(ds.ModeratorType);

        //using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM PlacesOfInterest", ConnString)) da.Fill(ds.PlacesOfInterest);

        //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.Moderators.RowChanged += (sender, e) =>

        {

            if (e.Action == DataRowAction.Change)

            {

                CDataSet.ModeratorsRow row = e.Row as CDataSet.ModeratorsRow;

                if (MassByModerator(row).Where((p) => p.Mass == row.Mass).FirstOrDefault() == null && row.Mass != null) row.Mass = "";

                if (ModeratorByMaterial(row).Where((p) => p.Moderator == row.Material).FirstOrDefault() == null && row.Material != null) row.Material = "";


          

            }

        };

        ds.Students.RowChanged += (sender, e) =>

        {

            if (e.Action == DataRowAction.Change)

            {

                CDataSet.StudentsRow row = e.Row as CDataSet.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 void AddTeacher() => ds.Teachers.Rows.Add(ds.Teachers.NewRow());

   // internal void AddStudent() => ds.Students.Rows.Add(ds.Students.NewRow());

    internal object GetStudents() => ds.Students;

    internal void AddStudent() => ds.Students.Rows.Add(ds.Students.NewRow());

    internal object GetModerators() => ds.Moderators;

    internal void AddModerators()

    {

        ds.Moderators.Rows.Add(ds.Moderators.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();

    public IEnumerable<DataRow> LoadModerators() => ds.Moderators.AsEnumerable();

    public IEnumerable<DataRow> LoadModeratorType() => ds.ModeratorType.AsEnumerable();

    internal List<CDataSet.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) => ((CDataSet.ProvincesRow)p).CountryName == countryName.ToString()).ToList();

    }

    internal List<CDataSet.DistrictsRow> DistrictsByProvinceID(DataRow item)

    {

        var provinceID = item?.GetType().GetProperty("ProvinceID")?.GetValue(item, null);

        if (provinceID == null) return null;

        return ds.Districts.Where((p) => ((CDataSet.DistrictsRow)p).ProvinceID == (int)provinceID).ToList().ToList();

    }

    internal List<CDataSet.ModeratorTypeRow> ModeratorByMaterial(DataRow item)

    {

        if (item == null) return null;

        var material = item?.GetType().GetProperty("Material")?.GetValue(item, null);

        if (material == null) return null;

        return ds.ModeratorType.Where((p) => ((CDataSet.ModeratorTypeRow)p).Moderator == material.ToString()).ToList();

    }

    internal List<CDataSet.ModeratorTypeRow> MassByModerator(DataRow item)

    {

        if (item == null) return null;

        var material = item?.GetType().GetProperty("Material")?.GetValue(item, null);

        if (material == null) return null;

        return ds.ModeratorType.Where((p) => ((CDataSet.ModeratorTypeRow)p).Moderator == material.ToString()).ToList();

    }

}
}

Answer 3

I use the MassConverter

using System;

using System.Collections.Generic;

using System.Data;

using System.Globalization;

using System.Windows;

using System.Windows.Data;

using WpfSimple_v2.Models;

namespace WpfSimple_v2.ViewModels

{

public partial class MassConverter : IMultiValueConverter

{

    public object Convert(object[] values, Type targetType, object parameter, CultureInfo culture)

    {

        if (values == null) return null;

        if (values[0] == null || values[0] == DependencyProperty.UnsetValue) return null;

        var type = values[0].ToString();

        if (values[1] == null || values[1] == DependencyProperty.UnsetValue) return null;

        IList<DataRow> l = (IList<DataRow>)values[1];

        //return  ((CDataSet.ModeratorTypeRow)l[1]).Mass;

        var subTypeList = values[1] as CDataSet.ModeratorTypeRow;

        if (subTypeList == null) return null;

        return subTypeList[type];

    }

    public object[] ConvertBack(object value, Type[] targetTypes, object parameter, CultureInfo culture)

    {

        return null;

    }

}
```}

This may be a problem.  Can't cast string to int similar to ConvDistrictID.cs

Answer 4

I kind of did it earlier by mixture of MVVM and Both on xaml.cs file,

	private Guid updatedPending;   

    private void DataGrid_SelectionChanged(object sender, SelectionChangedEventArgs e)   
    {   
        var context = DataContext as <your viewmodel>;   
        context.ItemsAdded();
        if (updatedPending != null && updatedPending != Guid.Empty)
        {
            context.ItemUpdated(updatedPending);
            updatedPending = Guid.Empty;
        }
    }
    private void DataGrid_CellEditEnding(object sender, DataGridCellEditEndingEventArgs e)
    {
       var item = e.Row.Item as <your-model>;
       updatedPending = item.Id;
    }

Let me know if it helps.

Answer 5

Hi,@ Yvon. Welcome to Microsoft Q&A.

To save changes to the database, you would typically need to update the records in the database based on the changes made in your WPF application. Below is a simplified example of how you might implement a SaveChanges method in your data access layer (DAL) to update the PlacesOfInterest records in the database.

 private ICommand saveCommand;
 public ICommand SaveCommand
 {
   get
   {
     if (saveCommand == null)
     {
       saveCommand = new RelayCommand(param => SaveChanges(), param => CanSaveChanges());
     }
     return saveCommand;
   }
 }

 private void SaveChanges()
 {

   DAL.SaveChanges(PlacesOfInterest);
 }

 private bool CanSaveChanges()
 {
   
   return true;
 }

  public static void SaveChanges(ObservableCollection<PlacesOfInterest> places)
  {
    using (IDbConnection conn = new SqlConnection(ConnString))
    {
      if (conn.State == ConnectionState.Closed) conn.Open();

     
      string updateQuery = @"
              UPDATE PlacesOfInterest  SET CountryID = @CountryID, ProvinceID = @ProvinceID, DistrictID = @DistrictID    WHERE ID = @ID";
      foreach (var place in places)
      {
        conn.Execute(updateQuery, new
        {
          CountryID = place.CountryID,
          ProvinceID = place.ProvinceID,
          DistrictID = place.DistrictID,
          ID = place.ID 
        });
      }
    }

  } 

Complete code:

   <Window.DataContext>
       <local:MainWindowViewModel />
   </Window.DataContext>
   <Window.Resources>
       <local:MainWindowViewModel x:Key="vm"/>
       <local:ConvProvenceID x:Key="ConvProvenceID"/>
       <local:ConvDistrictID x:Key="ConvDistrictID"/>
   </Window.Resources>
   <Grid  DataContext="{StaticResource vm}">
       <StackPanel>
           <DataGrid ItemsSource="{Binding ViewPlacesOfInterest}"    SelectedItem="{Binding SelectedPlace}"
                     AutoGenerateColumns="False"   CanUserAddRows="False">
               <DataGrid.Columns>
                   <DataGridTemplateColumn Header="Country">
                       <DataGridTemplateColumn.CellTemplate>
                           <DataTemplate>
                               <ComboBox
               Width="120"
               ItemsSource="{Binding Countries, Source={StaticResource vm}}"
               DisplayMemberPath="CountryName"
               SelectedValue="{Binding CountryID, UpdateSourceTrigger=PropertyChanged}"
               SelectedValuePath="CountryID"></ComboBox>
                           </DataTemplate>
                       </DataGridTemplateColumn.CellTemplate>
                   </DataGridTemplateColumn>
                   <DataGridTemplateColumn Header="Province">
                       <DataGridTemplateColumn.CellTemplate>
                           <DataTemplate>
                               <TextBlock>
                                   <TextBlock.Text>
                                       <MultiBinding Converter="{StaticResource ConvProvenceID}">
                                           <Binding Path="ProvinceID"/>
                                           <Binding Path="Provinces" Source="{StaticResource vm}"/>
                                       </MultiBinding>
                                   </TextBlock.Text></TextBlock>
                           </DataTemplate>
                       </DataGridTemplateColumn.CellTemplate>
                       <DataGridTemplateColumn.CellEditingTemplate>
                           <DataTemplate DataType="ComboBox">
                               <ComboBox
               Width="120"
               DisplayMemberPath="ProvinceName"
               ItemsSource="{Binding CurrentProvinces, Source={StaticResource vm}}"
               SelectedValue="{Binding ProvinceID, UpdateSourceTrigger=PropertyChanged}"
               SelectedValuePath="ProvinceID" />
                           </DataTemplate>
                       </DataGridTemplateColumn.CellEditingTemplate>
                   </DataGridTemplateColumn>
                   <DataGridTemplateColumn Header="District">
                       <DataGridTemplateColumn.CellTemplate>
                           <DataTemplate>
                               <TextBlock>
                                   <TextBlock.Text>
                                       <MultiBinding Converter="{StaticResource ConvDistrictID}">
                                           <Binding Path="DistrictID"/>
                                           <Binding Path="Districts" Source="{StaticResource vm}"/>
                                       </MultiBinding>
                                   </TextBlock.Text></TextBlock>
                           </DataTemplate>
                       </DataGridTemplateColumn.CellTemplate>
                       <DataGridTemplateColumn.CellEditingTemplate>
                           <DataTemplate>
                               <ComboBox
               Width="120"
               DisplayMemberPath="DistrictName"
               ItemsSource="{Binding CurrentDistricts, Source={StaticResource vm}}"
               SelectedValue="{Binding DistrictID, UpdateSourceTrigger=PropertyChanged}"
               SelectedValuePath="DistrictID" />
                           </DataTemplate>
                       </DataGridTemplateColumn.CellEditingTemplate>
                   </DataGridTemplateColumn>
               </DataGrid.Columns>
           </DataGrid>
           <Button Content="Save" Command="{Binding SaveCommand}" />
       </StackPanel>
   </Grid>

Codebedhind:

public partial class MainWindowViewModel : INotifyPropertyChanged
{
  public ObservableCollection<PlacesOfInterest> PlacesOfInterest { get; set; }
  public ObservableCollection<CountriesModel> Countries { get; set; }
  public ObservableCollection<ProvincesModel> Provinces { get; set; }
  public ObservableCollection<DistrictsModel> Districts { get; set; }



  public MainWindowViewModel()
  {
    this.PlacesOfInterest = new ObservableCollection<PlacesOfInterest>(DAL.LoadPlacesOfInterest());


    this.Countries = new ObservableCollection<CountriesModel>(DAL.LoadCountries());
    this.Provinces = new ObservableCollection<ProvincesModel>(DAL.LoadProvinces());
    this.Districts = new ObservableCollection<DistrictsModel>(DAL.LoadDistricts());
    cvsPlacesOfInterest.Source = PlacesOfInterest;
  }
  public ICollectionView ViewPlacesOfInterest { get => cvsPlacesOfInterest.View; }
  private CollectionViewSource cvsPlacesOfInterest = new CollectionViewSource();
  private PlacesOfInterest selectedPlace;

  public PlacesOfInterest SelectedPlace
  {
    get { return selectedPlace; }
    set
    {
      selectedPlace = value;
      OnPropertyChanged(nameof(SelectedPlace));
      OnPropertyChanged(nameof(ViewPlacesOfInterest));
    }
  }
  private ICommand saveCommand;
  public ICommand SaveCommand
  {
    get
    {
      if (saveCommand == null)
      {
        saveCommand = new RelayCommand(param => SaveChanges(), param => CanSaveChanges());
      }
      return saveCommand;
    }
  }

  private void SaveChanges()
  {
 
    DAL.SaveChanges(SelectedPlace);
  }

  private bool CanSaveChanges()
  {
    
    return true;
  }

  public object CurrentProvinces
  {

    get
    {
      return Countries[SelectedPlace.CountryID - 1].GetProvincesForCountry(SelectedPlace.CountryID);
    }
  }



  public object CurrentDistricts
  {


    get
    {

      return Provinces[SelectedPlace.ProvinceID - 1].GetDistrictsForProvince(SelectedPlace.ProvinceID);
    }
  }


  public event PropertyChangedEventHandler PropertyChanged;
  public event EventHandler CanExecuteChanged;
  private void OnPropertyChanged([CallerMemberName] String propertyName = "")
          => PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));

}
public class DAL
{

  private static readonly string ConnString = "Data Source=(localdb)\\ProjectModels;Initial Catalog=Datas;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";



  public static List<PlacesOfInterest> LoadPlacesOfInterest()
  {
    using (IDbConnection conn = new SqlConnection(ConnString))
    {
      if (conn.State == ConnectionState.Closed) conn.Open();
      var places = conn.Query<PlacesOfInterest>("SELECT * FROM PlacesOfInterest").ToList();



      return places;
    }
  }


  public static List<CountriesModel> LoadCountries()
  {
    using (IDbConnection conn = new SqlConnection(ConnString))
    {
      if (conn.State == ConnectionState.Closed) conn.Open();
      return conn.Query<CountriesModel>("SELECT * FROM Countries").ToList();
    }
  }

  public static List<ProvincesModel> LoadProvinces()
  {
    using (IDbConnection conn = new SqlConnection(ConnString))
    {
      if (conn.State == ConnectionState.Closed) conn.Open();
      return conn.Query<ProvincesModel>("SELECT * FROM Provinces").ToList();
    }
  }
  public static List<ProvincesModel> LoadProvincesForCountry(int country)
  {
    using (IDbConnection conn = new SqlConnection(ConnString))
    {
      if (conn.State == ConnectionState.Closed) conn.Open();
      string query = "SELECT * FROM Provinces WHERE CountryID = @CountryID";


      return conn.Query<ProvincesModel>(query, new { CountryID = country }).ToList();
    }
  }
  public static List<DistrictsModel> LoadDistricts()
  {
    using (IDbConnection conn = new SqlConnection(ConnString))
    {
      if (conn.State == ConnectionState.Closed) conn.Open();
      return conn.Query<DistrictsModel>("SELECT * FROM Districts").ToList();
    }
  }

  public static List<DistrictsModel> LoadDistrictsForProvince(int province)
  {
    using (IDbConnection conn = new SqlConnection(ConnString))
    {
      if (conn.State == ConnectionState.Closed) conn.Open();

      string query;
      if (province == null)
      {
        return conn.Query<DistrictsModel>("SELECT * FROM Districts").ToList();

      }
      else
      {
        query = "SELECT * FROM Districts WHERE ProvinceID = @ProvinceID";
        return conn.Query<DistrictsModel>(query, new { ProvinceID = province }).ToList();
      }


    }
  }

   public static void SaveChanges(ObservableCollection<PlacesOfInterest> places)
  {
    using (IDbConnection conn = new SqlConnection(ConnString))
    {
      if (conn.State == ConnectionState.Closed) conn.Open();

     
      string updateQuery = @"
              UPDATE PlacesOfInterest  SET CountryID = @CountryID, ProvinceID = @ProvinceID, DistrictID = @DistrictID    WHERE ID = @ID";
      foreach (var place in places)
      {
        conn.Execute(updateQuery, new
        {
          CountryID = place.CountryID,
          ProvinceID = place.ProvinceID,
          DistrictID = place.DistrictID,
          ID = place.ID 
        });
      }
    }

  } 
}

public partial class PlacesOfInterest : INotifyPropertyChanged
{
  private string countryName;
  public string CountryName
  {
    get { return countryName; }
    set
    {
      if (countryName != value)
      {
        countryName = value;
        OnPropertyChanged(nameof(CountryName));
      }
    }
  }


  int _iD;
  public int ID
  {
    get => this._iD;
    set { this._iD = value; OnPropertyChanged(); }
  }

  int _countryID;
  public int CountryID
  {
    get => this._countryID;
    set { this._countryID = value; ProvinceID = 0; DistrictID = 0; OnPropertyChanged(); }
  }


  int _provinceID;
  public int ProvinceID
  {
    get => this._provinceID;
    set { this._provinceID = value; DistrictID = 0; OnPropertyChanged(); }
  }

  int _districtID;
  public int DistrictID
  {
    get => this._districtID;
    set { this._districtID = value; OnPropertyChanged(); }
  }

  public event PropertyChangedEventHandler PropertyChanged;
  public event EventHandler CanExecuteChanged;
  private void OnPropertyChanged([CallerMemberName] String propertyName = "")
          => PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));


}
public class ConvProvenceID : IMultiValueConverter
{

  public object Convert(object[] values, Type targetType, object parameter, CultureInfo culture)
  {
    int i = (int)values[0];
    IList<ProvincesModel> l = (IList<ProvincesModel>)values[1];
    return i > 0 && (l.Count > 0) ? 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)
  {
    int i = (int)values[0];
    IList<DistrictsModel> l = (IList<DistrictsModel>)values[1];
    return (i > 0 && l.Count > 0) ? l[i - 1].DistrictName : string.Empty;
  }
  public object[] ConvertBack(object value, Type[] targetTypes, object parameter, CultureInfo culture)
  {
    throw new NotImplementedException();
  }
}
public partial class CountriesModel : ObservableObject
{
  [ObservableProperty]
  public string countryID;
  [ObservableProperty]
  public string countryName;


  public ObservableCollection<ProvincesModel> GetProvincesForCountry(int country)
  {
    return new ObservableCollection<ProvincesModel>(DAL.LoadProvincesForCountry(country));
  }

}
public partial class ProvincesModel : ObservableObject
{
  [ObservableProperty]
  public string countryID;

  [ObservableProperty]
  public int provinceID;

  [ObservableProperty]
  public string provinceName;
  public ObservableCollection<DistrictsModel> GetDistrictsForProvince(int province)
  {
    return new ObservableCollection<DistrictsModel>(DAL.LoadDistrictsForProvince(province));
  }
}

public partial class DistrictsModel : ObservableObject
{
  [ObservableProperty]
  public int provinceID;

  [ObservableProperty]
  public int districtID;

  [ObservableProperty]
  public string districtName;

}
public class RelayCommand : ICommand
{
  private readonly Action<object> execute;
  private readonly Predicate<object> canExecute;

  public RelayCommand(Action<object> execute, Predicate<object> canExecute)
  {
    this.execute = execute ?? throw new ArgumentNullException(nameof(execute));
    this.canExecute = canExecute ?? throw new ArgumentNullException(nameof(canExecute));
  }

  public RelayCommand(Action<object> execute) : this(execute, null) { }

  public event EventHandler CanExecuteChanged
  {
    add { CommandManager.RequerySuggested += value; }
    remove { CommandManager.RequerySuggested -= value; }
  }

  public bool CanExecute(object parameter)
  {
    return canExecute == null || canExecute(parameter);
  }

  public void Execute(object parameter)
  {
    execute(parameter);
  }
}

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.