How to bind distinct query results to a combobox and pass it as selected cells value

Question

I have tried numerous Stack Overflow answers but I just cannot find a straight-forward easy example of what I am hoping to achieve.

I am attempting to fill a combo box with distinct results from a table (dimDate) and also change that combo box selection when the grid row is selected (from safety table).

Needless to say what I kept trying does not work as expected. What am I doing wrong, and how can I get around it with the simplest working example?

MainWindow.xaml

MainWindow.cs

namespace Safety
{
    public partial class MainWindow : MetroWindow
    {
        public MainWindow()
        {
            InitializeComponent();
            DataContext = new SafetyViewModel();
            string connectionString = "data Source=xxx; initial catalog=xxx; user id=xxx; password=xxx";
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("Select ID, Year, Quarter, Name, SaferyScore from MATRIX.dbo.Safety", connection);

            try
            {
                connection.Open();
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                dtGrid.DataContext = dt;
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            connection.Close();
        }

        private void dtGrid_SelectedCellsChanged(object sender, SelectedCellsChangedEventArgs e)
        {
            DataRowView row_selected = dtGrid.SelectedItem as DataRowView;
            if (row_selected == null) return;
            txtyr.ItemsSource = row_selected["Year"].ToString();
            txtqt.Text = row_selected["Quarter"].ToString();
            txtnm.Text = row_selected["Name"].ToString();
            txtscc.Value = Convert.ToInt16(row_selected["SafetyScore"].ToString());
            txtID.Text = row_selected["ID"].ToString();
        }
    }
}

SafetyViewModel.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using System.Runtime.CompilerServices;
using Safety.Model;
using System.Windows;
using Safety;
using System.Collections.ObjectModel;

namespace Safety.ViewModel
{
    public class SafetyViewModel : INotifyPropertyChanged
    {
        public SafetyViewModel()
        {
            this.loadyearlist();
            this.loadnamelist();
            this.loadquarterslist();
        }

        public void loadyearlist()
        {
            using (MATRIXEntities db = new MATRIXEntities())
            {
                var yrs = (from a in db.dimDate
                         select a).Distinct()
                         .ToList();

                Years = new ObservableCollection(yrs);
            }
        }

        public void loadquarterslist()
        {
            using (MATRIXEntities db = new MATRIXEntities())
            {
                var qts = (from a in db.dimDate
                           select a).Distinct()
                         .ToList();

                Quarters = new ObservableCollection(qts);
            }
        }

        public void loadnamelist()
        {
            using (MATRIXEntities db = new MATRIXEntities())
            {
                var nms = (from a in db.Employees
                         select a)
                         .ToList();

                Employees = new ObservableCollection(nms);
            }
        }



        private ObservableCollection years;
        public ObservableCollection Years
        {
            get { return years; }
            set
            {
                years = value;
                OnPropertyChanged("Years");
            }
        }

        public string _years_SelectedValue;
        private string years_SelectedValue
        {
            get { return _years_SelectedValue; }
            set
            {
                _years_SelectedValue = value;
                OnPropertyChanged("years_SelectedValue");
            }
        }

        private ObservableCollection quarters;
        public ObservableCollection Quarters
        {
            get { return quarters; }
            set
            {
                quarters = value;
                OnPropertyChanged("Quarters");
            }
        }

        public string _quarters_SelectedValue;
        private string quarters_SelectedValue
        {
            get { return _quarters_SelectedValue; }
            set
            {
                _quarters_SelectedValue = value;
                OnPropertyChanged("quarters_SelectedValue");
            }
        }

        private ObservableCollection employees;
        public ObservableCollection Employees
        {
            get { return employees; }
            set
            {
                employees = value;
                OnPropertyChanged("Employees");
            }
        }

        public string _names_SelectedValue;
        private string names_SelectedValue
        {
            get { return _names_SelectedValue; }
            set
            {
                _names_SelectedValue = value;
                OnPropertyChanged("names_SelectedValue");
            }
        }

        public event PropertyChangedEventHandler PropertyChanged;

        protected virtual void OnPropertyChanged([CallerMemberName]string propertyName = null)
            => PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));

    }
}

Model

namespace Safety.Model
{
    using System;
    using System.Collections.Generic;

    public partial class dimDate
    {
        public int DateID { get; set; }
        public System.DateTime Date { get; set; }
        public int Year { get; set; }
        public int Month { get; set; }
        public int Day { get; set; }
        public int Quarter { get; set; }
    }
}

namespace Safety.Model
{
    using System;
    using System.Collections.Generic;

    public partial class Safety
    {
        public int ID { get; set; }
        public int Year { get; set; }
        public int Quarter { get; set; }
        public string Name { get; set; }
        public int SafetyScore { get; set; }
    }
}

namespace Safety.Model
{
    using System;
    using System.Collections.Generic;

    public partial class Employees
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Position { get; set; }
    }
}

user1672994 · Accepted Answer

Your code has lot of problems. Few are listed below:

Bind Properties should be public instead of private
In Code behind, you are trying to set the Text of ComboBox instead of SelectedValue
You are mixing Code behind with ViewModel design

I've written below code which demonstrates the requirement using MVVM design pattern.

MainWindow.xaml

For demo, I've defined the ViewModel in View.

MainViewModel.Cs

using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Linq;

namespace WpfApp1
{
    public class MainWindowViewModel : INotifyPropertyChanged
    {
        public MainWindowViewModel()
        {
            var data = Model.GetData();
            Safeties.AddRange(data);
            Years.AddRange(data.Select(d => d.Year).Distinct());
            Quarters.AddRange(data.Select(d => d.Quarter).Distinct());
            Names.AddRange(data.Select(d => d.Name).Distinct());
        }

        private ObservableCollection years = new ObservableCollection();
        public ObservableCollection Years
        {
            get { return years; }
        }

        private int _years_SelectedValue;
        public int years_SelectedValue
        {
            get { return _years_SelectedValue; }
            set
            {
                _years_SelectedValue = value;
                OnPropertyChanged("years_SelectedValue");
            }
        }

        private ObservableCollection quarters = new ObservableCollection();
        public ObservableCollection Quarters
        {
            get { return quarters; }
        }

        private int _quarters_SelectedValue;
        public int quarters_SelectedValue
        {
            get { return _quarters_SelectedValue; }
            set
            {
                _quarters_SelectedValue = value;
                OnPropertyChanged("quarters_SelectedValue");
            }
        }

        private ObservableCollection names = new ObservableCollection();
        public ObservableCollection Names
        {
            get { return names; }
        }

        private string _names_SelectedValue;
        public string names_SelectedValue
        {
            get { return _names_SelectedValue; }
            set
            {
                _names_SelectedValue = value;
                OnPropertyChanged("names_SelectedValue");
            }
        }

        private ObservableCollection safeties = new ObservableCollection();
        public ObservableCollection Safeties
        {
            get { return safeties; }
        }

        private Safety selectedSafety;
        public Safety SelectedSafety
        {
            get { return selectedSafety; }
            set
            {
                selectedSafety = value;
                this.OnPropertyChanged(nameof(SelectedSafety));
                if (this.selectedSafety != null)
                {
                    this.years_SelectedValue = selectedSafety.Year;
                    this.quarters_SelectedValue = selectedSafety.Quarter;
                    this.names_SelectedValue = selectedSafety.Name;
                }
            }
        }

        public event PropertyChangedEventHandler PropertyChanged;

        private void OnPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }
    }
}

With above code incorporation, the below gif demos how the feature works:

Additional code

To demo, I've created a class to get the Data

public static class Model
    {
        public static List GetData()
        {
            return new List()
            {
                new Safety() { ID = 1, Name = "First", Quarter = 1, SafetyScore = 1, Year = 2001 },
                new Safety() { ID = 2, Name = "Second", Quarter = 2, SafetyScore = 1, Year = 2001 },
                new Safety() { ID = 3, Name = "Third", Quarter = 3, SafetyScore = 1, Year = 2001 },
                new Safety() { ID = 4, Name = "Fourth", Quarter = 4, SafetyScore = 1, Year = 2001 },
                new Safety() { ID = 5, Name = "First", Quarter = 1, SafetyScore = 1, Year = 2002 },
                new Safety() { ID = 6, Name = "Second", Quarter = 2, SafetyScore = 1, Year = 2002 },
                new Safety() { ID = 7, Name = "Third", Quarter = 3, SafetyScore = 1, Year = 2002 },
                new Safety() { ID = 8, Name = "Fourth", Quarter = 4, SafetyScore = 1, Year = 2002 },
            };
        }
    }

And extension for AddRange for ObservableCollection. However, you can use the ObservableRangeCollection introduced in C#7.0

public static class ObservableCollectionExtensions
    { 
        public static void AddRange(this ObservableCollection observableCollection, IEnumerable data)
        {
            foreach (T item in data)
            {
                observableCollection.Add(item);
            }
        }
    }

Safety.cs

public class Safety : INotifyPropertyChanged
    {
        private int id;
        public int ID
        {
            get
            {
                return id;
            }
            set
            {
                id = value;
                this.RaisePropertyChange(nameof(ID));
            }
        }

        private int year;
        public int Year
        {
            get
            {
                return year;
            }
            set
            {
                year = value;
                this.RaisePropertyChange(nameof(ID));
            }
        }

        private int quarter;
        public int Quarter
        {
            get
            {
                return quarter;
            }
            set
            {
                quarter = value;
                this.RaisePropertyChange(nameof(ID));
            }
        }

        private string name;
        public string Name
        {
            get
            {
                return name;
            }
            set
            {
                name = value;
                this.RaisePropertyChange(nameof(ID));
            }
        }

        private int safetyScore;

        public int SafetyScore
        {
            get
            {
                return safetyScore;
            }
            set
            {
                safetyScore = value;
                this.RaisePropertyChange(nameof(ID));
            }
        }

        public event PropertyChangedEventHandler PropertyChanged;

        private void RaisePropertyChange(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }
    }

How to bind distinct query results to a combobox and pass it as selected cells value

Answers (1)

Related Questions