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

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();

    }

}
}

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

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.

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.