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

Yvon 50

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.

Yvon 50 Reputation points

2024-02-22T18:43:39.9066667+00:00

Sorry for the duplicate posting. There was 404 errors and thought it didn't go through.
Peter Fleischer (former MVP) 19,231 Reputation points

2024-02-23T05:29:48.97+00:00

Hi,
which event you want to use to start saving?

Accepted answer

Peter Fleischer (former MVP) 19,231

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:

21 additional answers

Peter Fleischer (former MVP) 19,231

Hi,
to demonstrate all functions you can try following demo:

XAML MainWindow:

<Window x:Class="WpfApp1.Window060"
        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:WpfApp060"
        mc:Ignorable="d"
        Title="Yvon_240305" Height="450" Width="800">
  <Window.Resources>
    <local:ViewModelMain x:Key="vm"/>
  </Window.Resources>
  <Grid DataContext="{StaticResource vm}" Margin="5">
    <Grid.RowDefinitions>
      <RowDefinition/>
      <RowDefinition Height="auto"/>
    </Grid.RowDefinitions>
    <DataGrid ItemsSource="{Binding View}" IsReadOnly="True" AutoGenerateColumns="False" Grid.ColumnSpan="2">
      <DataGrid.Columns>
        <DataGridTextColumn Binding="{Binding Country}" Header="Country"/>
        <DataGridTextColumn Binding="{Binding Province}" Header="Province"/>
        <DataGridTextColumn Binding="{Binding District}" Header="District"/>
        <DataGridTemplateColumn Header="Operation">
          <DataGridTemplateColumn.CellTemplate>
            <DataTemplate>
              <StackPanel Orientation="Horizontal">
                <Button Content="Edit" Command="{Binding CmdEdit, Source={StaticResource vm}}" CommandParameter="{Binding}" Margin="2"/>
                <Button Content="Delete" Command="{Binding CmdDelete, Source={StaticResource vm}}" CommandParameter="{Binding}" Margin="2"/>
              </StackPanel>
            </DataTemplate>
          </DataGridTemplateColumn.CellTemplate>
        </DataGridTemplateColumn>
      </DataGrid.Columns>
    </DataGrid>
    <StackPanel HorizontalAlignment="Right" Orientation="Horizontal" Grid.Row="1" Grid.ColumnSpan="2">
      <Button Content="New" Command="{Binding Cmd}" CommandParameter="New"  Margin="5"/>
      <Button Content="Refresh" Command="{Binding Cmd}" CommandParameter="Refresh" Margin="5"/>
    </StackPanel>
  </Grid>
</Window>

XAML DetailWindow:

<Window x:Class="WpfApp060.Window060Detail"
        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"
        xmlns:vm="clr-namespace:WpfApp060"
        mc:Ignorable="d"
        Title="Window060Detail" Height="250" Width="400"
        vm:ViewModelDetail.AttProp="True">
  <Grid>
    <Grid.RowDefinitions>
      <RowDefinition Height="auto"/>
      <RowDefinition Height="auto"/>
    </Grid.RowDefinitions>
    <Grid DataContext="{Binding View}">
      <Grid.ColumnDefinitions>
        <ColumnDefinition Width="auto"/>
        <ColumnDefinition/>
      </Grid.ColumnDefinitions>
      <Grid.RowDefinitions>
        <RowDefinition Height="auto"/>
        <RowDefinition Height="auto"/>
        <RowDefinition Height="auto"/>
      </Grid.RowDefinitions>
      <Label Grid.Row="0" Grid.Column="0" Content="Country:" HorizontalAlignment="Right" Margin="5"/>
      <TextBox Grid.Row="0" Grid.Column="1" Text="{Binding Country}" Margin="5"/>
      <Label Grid.Row="1" Grid.Column="0" Content="Province:" HorizontalAlignment="Right" Margin="5"/>
      <TextBox Grid.Row="1" Grid.Column="1" Text="{Binding Province}" Margin="5"/>
      <Label Grid.Row="2" Grid.Column="0" Content="District:" HorizontalAlignment="Right" Margin="5"/>
      <TextBox Grid.Row="2" Grid.Column="1" Text="{Binding District}" Margin="5"/>
    </Grid>
    <StackPanel Grid.Row="3" Grid.Column="0" Grid.ColumnSpan="2" Orientation="Horizontal" HorizontalAlignment="Right">
      <Button Content="Save" Command="{Binding Cmd}" CommandParameter="Save" Margin="5"/>
      <Button Content="Delete" Command="{Binding Cmd}" CommandParameter="Delete" Margin="5"/>
      <Button Content="Cancel" Command="{Binding Cmd}" CommandParameter="Cancel" Margin="5"/>
    </StackPanel>
  </Grid>
</Window>

And code:

using Microsoft.EntityFrameworkCore;
using System;
using System.ComponentModel;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Windows;
using System.Windows.Data;
using System.Windows.Input;
namespace WpfApp060
{
	public class ViewModelMain : INotifyPropertyChanged
	{
		public ViewModelMain() => refreshData();
		private CollectionViewSource cvs = new CollectionViewSource(); // View source for displaying
		public ICollectionView View { get => cvs.View; }
		private void refreshData()
		{
			cvs.Source = (new DataAccess()).GetData();
			OnPropertyChanged(nameof(View));
		}
		public ICommand Cmd { get => new RelayCommand(CmdExec); }
		private void CmdExec(object obj)
		{
			switch (obj.ToString())
			{
				case "Refresh":
					refreshData();
					break;
				case "New":
					CmdEditExec(null);
					break;
				default:
					break;
			}
		}
		public ICommand CmdEdit { get => new RelayCommand(CmdEditExec); }
		private void CmdEditExec(object obj)
		{
			int id = (obj is PlacesOfInterest) ? ((PlacesOfInterest)obj).ID : 0;
			ViewModelDetail vm = new ViewModelDetail() { ID = id };
			vm.ExecRefresh += (s, e) => refreshData();
			(new Window060Detail() { DataContext = vm }).Show();
		}
		public ICommand CmdDelete { get => new RelayCommand(CmdDeleteExec); }
		private void CmdDeleteExec(object obj)
		{
			PlacesOfInterest item = obj as PlacesOfInterest;
			if (item != null) (new DataAccess()).DeleteData(item);
			refreshData();
		}
		public event PropertyChangedEventHandler PropertyChanged;
		private void OnPropertyChanged([CallerMemberName] string propName = "") => PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propName));
	}
	public class ViewModelDetail
	{
		public int ID { get; set; }
		private DataAccess da = new DataAccess();
		private PlacesOfInterest _view = null;
		public PlacesOfInterest View
		{
			get
			{
				if (_view == null) _view = da.PlacesOfInterest.SingleOrDefault(a => a.ID == ID);
				if (_view == null) _view = new PlacesOfInterest();
				return _view;
			}
		}
		public event EventHandler ExecRefresh;
		public ICommand Cmd { get => new RelayCommand(CmdExec); }
		private void CmdExec(object obj)
		{
			switch (obj.ToString())
			{
				case "Save":
					if (_view.ID == 0) da.AddData(_view);
					da.UpdateData(View);
					break;
				case "Delete":
					da.DeleteData(View);
					break;
				case "Cancel":
					da.ReloadData(View);
					break;
				default:
					break;
			}
			ExecRefresh.Invoke(this, null);
			SubWindow.Close();
		}
		#region Attached property
		public static readonly DependencyProperty AttPropProperty = DependencyProperty.Register("AttProp",
	typeof(bool), typeof(Window), 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 wnd = depObj as Window;
			if (wnd == null) return;
			if ((e.NewValue is bool) && (bool)(e.NewValue))
				wnd.Loaded += Wnd_Loaded;
		}
		private static void Wnd_Loaded(object sender, RoutedEventArgs e)
		{
			Window wnd = sender as Window;
			if (wnd == null) return;
			ViewModelDetail dc = wnd.DataContext as ViewModelDetail;
			if (dc == null) return;
			dc.SubWindow = wnd;
		}
		Window SubWindow { get; set; }
		#endregion
	}
	public partial class PlacesOfInterest
	{
		public int ID { get; set; }
		public string Country { get; set; }
		public string Province { get; set; }
		public string District { get; set; }
	}
	public partial class DataAccess : DbContext
	{
		protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
		{
			optionsBuilder.UseSqlServer(WpfApp1.Properties.Settings.Default.cnSQL_MyDB);
		}
		public virtual DbSet<PlacesOfInterest> PlacesOfInterest { get; set; }
		internal object GetData() => from item in PlacesOfInterest.AsEnumerable() orderby item.Country select item;
		internal void AddData(PlacesOfInterest item)
		{
			PlacesOfInterest.Add(item);
			this.SaveChanges();
		}
		internal void UpdateData(PlacesOfInterest item)
		{
			if (PlacesOfInterest.SingleOrDefault(a => a.ID == item.ID) != null) this.SaveChanges();
		}
		internal void DeleteData(PlacesOfInterest item)
		{
			PlacesOfInterest.Remove(PlacesOfInterest.Single(a => a.ID == item.ID));
			this.SaveChanges();
		}
		internal void ReloadData(PlacesOfInterest item)
		{
			if (this.Entry(item).State != EntityState.Detached)
			{
				this.Entry(item).State = EntityState.Unchanged;
				this.SaveChanges();
			}
		}
	}
	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; }
		}
	}
}

Result:

Yvon 50 Reputation points

2024-03-07T23:53:48.1133333+00:00

Thanks @Peter Fleischer (former MVP) for help but unfortunately, we can't use the .NET and we are using .NET Framework. So we can't install the EFcore and can't proceed with the above changes.

Peter Fleischer (former MVP) 19,231

Hi,
instead of EFCore you can use following DataAccess class in Framwork 4.8 and you get with prvious code the same result:

	public class DataAccess
	{
		private static ObservableCollection<PlacesOfInterest> _placesOfInterest;
		public ObservableCollection<PlacesOfInterest> PlacesOfInterest { get => _placesOfInterest; }
		public ObservableCollection<PlacesOfInterest> GetData()
		{
			using (IDbConnection conn = new SqlConnection(WpfApp1.Properties.Settings.Default.cnSQL_MyDB))
			{
				if (conn.State == ConnectionState.Closed) conn.Open();
				_placesOfInterest = new ObservableCollection<PlacesOfInterest>(conn.Query<PlacesOfInterest>("SELECT * FROM PlacesOfInterest"));
			}
			return _placesOfInterest;
		}
		internal void UpdateData(PlacesOfInterest item)
		{
			if (PlacesOfInterest.SingleOrDefault(a => a.ID == item.ID) != null)
			{
				using (IDbConnection conn = new SqlConnection(WpfApp1.Properties.Settings.Default.cnSQL_MyDB))
				{
					if (conn.State == ConnectionState.Closed) conn.Open();
					using (IDbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandText = "Update PlacesOfInterest SET Country=@Country, Province=@Province, District=@District WHERE ID=@ID";
						cmd.Parameters.Add(new SqlParameter("@ID", item.ID));
						cmd.Parameters.Add(new SqlParameter("@Country", item.Country));
						cmd.Parameters.Add(new SqlParameter("@Province", item.Province));
						cmd.Parameters.Add(new SqlParameter("@District", item.District));
						cmd.ExecuteNonQuery();
					}
				}
			}
			if (PlacesOfInterest.SingleOrDefault(a => a.ID == item.ID) == null && item.ID == 0)
			{
				using (IDbConnection conn = new SqlConnection(WpfApp1.Properties.Settings.Default.cnSQL_MyDB))
				{
					if (conn.State == ConnectionState.Closed) conn.Open();
					using (IDbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandText = "INSERT PlacesOfInterest(Country,Province,District) VALUES(@Country,@Province,@District); SELECT SCOPE_IDENTITY()";
						cmd.Parameters.Add(new SqlParameter("@Country", item.Country));
						cmd.Parameters.Add(new SqlParameter("@Province", item.Province));
						cmd.Parameters.Add(new SqlParameter("@District", item.District));
						var id = cmd.ExecuteScalar();
						item.ID = (int)(decimal)id;
					}
				}
			}
		}
		internal void DeleteData(PlacesOfInterest item)
		{
			if (item.ID != 0 && PlacesOfInterest.Remove(PlacesOfInterest.Single(a => a.ID == item.ID)))
			{
				using (IDbConnection conn = new SqlConnection(WpfApp1.Properties.Settings.Default.cnSQL_MyDB))
				{
					if (conn.State == ConnectionState.Closed) conn.Open();
					using (IDbCommand cmd = conn.CreateCommand())
					{
						cmd.CommandText = "DELETE PlacesOfInterest WHERE ID=@ID";
						cmd.Parameters.Add(new SqlParameter("@ID", item.ID));
						cmd.ExecuteNonQuery();
					}
				}
			}
		}
		internal void ReloadData(PlacesOfInterest item)
		{
			if (PlacesOfInterest.SingleOrDefault(a => a.ID == item.ID) != null)
			{
				using (IDbConnection conn = new SqlConnection(WpfApp1.Properties.Settings.Default.cnSQL_MyDB))
				{
					if (conn.State == ConnectionState.Closed) conn.Open();
					var p = conn.Query<PlacesOfInterest>($"SELECT * FROM PlacesOfInterest WHERE ID={item.ID}").FirstOrDefault<PlacesOfInterest>();
					if (p != null)
					{
						if (item.Country != p.Country) item.Country = p.Country;
						if (item.District != p.District) item.District = p.District;
						if (item.Province != p.Province) item.Province = p.Province;
					}
				}
			}
		}
	}
	public static class MyExtensions
	{
		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;
				}
			}
		}
	}

Yvon 50 Reputation points

2024-03-07T23:57:37.5+00:00

To handle the crud, I need directly access to the database and not dataset. Thanks!
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Yvon 50 Reputation points

2024-03-08T00:36:28.0233333+00:00

I will work with your solution for next week without convert .NET framework to .NET and will update you afterward. Thanks!
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Yvon 50 Reputation points

2024-03-13T22:23:35.86+00:00

Thanks @Peter Fleischer (former MVP) The individual commands are working fine, but I need the persistence of the data flow based on the following operations

I will need to persist the add/modify/delete for both datagrids (as the above example); before I can do 'SaveAll' or 'CancelAll'

To 'AddStudentAddress' or 'AddTeacherAddress', as it shows above by adding a new row to the datagrid. I will add a new record into the collection but not the database yet.

To 'Delete Record', I just remove the row from the collection or strike it out, but not delete it right away from the database.

To modify the data, directly update to the datagrid and not to the database yet.

Once I am ready to 'save' will use 'SaveAll' and this command will persist the above all operations(crud) to the database at once. Same to 'CancelAll' - to cancel all the operations and refresh the original data back from the database.
Please sign in to rate this answer.
Peter Fleischer (former MVP) 19,231 Reputation points

2024-03-14T04:00:35.48+00:00

Hi,
you can use DataSet/DataTable to detect DataRowVersion for SaveAll and DataAdapter to solve conflicts.

Yvon 50 Reputation points

2024-03-14T16:47:21.48+00:00

This is a different direction. Do you mean to discard all the RelayCommand?

The changes are based on the filtering comboboxes and then use those result as the selections to do the crud.
Sign in to comment

Share via

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

21 additional answers