Best way to add items from a database to both a combobox and datagrid in WPF...

[donB]

Hi, I'm totally new to MVVM pattern in WPF and even after going through a lot of posts and tutorials on the internet still struggling to fully grasp it. Anyways, here is what I'm trying to do, I have a SQLite database table with multiple columns.

When I run the WPF app I want to fill a datagrid with all of the data from that table and also set the itemsource of a combobox to be the unique items from a particular column in the database. Furthermore, when I update the database table the itemsource of the combobox and the data shown in the datagrid should be automatically updated.

Here is the structure of my app

The files code's look like this

    public class Bills : ViewModelBase
    {
        private int id;
        private string party;
        private string billNo;
        //... and some other

        public int Id
        {
            get { return id; }
            set
            {
                id = value;
                OnPropertyChanged("ID");
            }
        }
        public string Party
        {
            get { return party; }
            set
            {
                party = value;
                OnPropertyChanged("Party");
            }
        }
        public string BillNo
        {
            get { return billNo; }
            set
            {
                billNo = value;
                OnPropertyChanged("BillNo");
            }
        }
        //....
    }

    public class ViewModelBase : INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;

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

public class BillsViewModel : ViewModelBase
    {

        public BillsViewModel()
        {
            personnel = new PersonnelBusinessObject();
            personnel.BillChanged += new EventHandler(personnel_BillChanged);
            personnel.BillChanged += new EventHandler(personnel_uBillChanged);
        }
        PersonnelBusinessObject personnel;
        private ObservableCollection<Bills> _Bill;
        public ObservableCollection<Bills> Bill
        {
            get
            {
                _Bill = new ObservableCollection<Bills>(personnel.GetBills());
                return _Bill;
            }
        }


        private ObservableCollection<Bills> _Vendor;
        public ObservableCollection<Bills> Vendor
        {
            get
            {
                _Vendor = new ObservableCollection<Bills>(personnel.GetP());
                return _Vendor;
            }
        }


        public int SelectedIndex { get; set; }
        object _SelectedBill;
        public object SelectedBill
        {
            get
            {
                return _SelectedBill;
            }
            set
            {
                if (_SelectedBill != value)
                {
                    _SelectedBill = value;
                    OnPropertyChanged("SelectedBill");
                }
            }
        }


        void personnel_BillChanged(object sender, EventArgs e)
        {
            Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Background, new Action(() =>
            {
                OnPropertyChanged("Bill");
            }));
        }

        void personnel_uBillChanged(object sender, EventArgs e)
        {
            Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Background, new Action(() =>
            {
                OnPropertyChanged("Vendor");
            }));
        }

    }

public static class DatabaseLayer
    {

        public static List<Bills> GetBillFromDatabase()
        {
            try
            {
                SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=Bills.db");
                SQLiteCommand sqlCom = new SQLiteCommand("Select * From billdata", m_dbConnection);
                SQLiteDataAdapter sda = new SQLiteDataAdapter(sqlCom);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                var Bill = new List<Bills>();
                foreach (DataRow row in dt.Rows)
                {
                    var obj = new Bills()
                    {
                        Id = Convert.ToInt32(row["Id"]),
                        Party = (string)row["Party"],
                        BillNo = (string)row["BillNo"],
                        BillDt = (string)(row["BillDt"]),
                        Amt = (string)row["Amt"],
                        DueDt = (string)(row["DueDt"]),
                        PaidOn = (string)(row["PaidOn"])
                    };
                    Bill.Add(obj);
                    m_dbConnection.Close();
                }
                return Bill;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static List<Bills> GetPartyFromDatabase()
        {
            try
            {
                SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=Bills.db");
                SQLiteCommand sqlCom = new SQLiteCommand("SELECT DISTINCT Party FROM billdata ORDER BY Party", m_dbConnection);
                SQLiteDataAdapter sda = new SQLiteDataAdapter(sqlCom);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                var Bill = new List<Bills>();
                foreach (DataRow row in dt.Rows)
                {

                    Bill.Add(new Bills
                    {
                        Party = (string)row["Party"],
                    });

                    m_dbConnection.Close();
                }
                return Bill;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }

public class PersonnelBusinessObject
    {
        internal EventHandler BillChanged;
        internal EventHandler uBillChanged;

        List<Bills> Bill { get; set; }
        List<Bills> uBill { get; set; }

        public PersonnelBusinessObject()
        {
            Bill = DatabaseLayer.GetBillFromDatabase();
            uBill = DatabaseLayer.GetPartyFromDatabase();
        }

        public List<Bills> GetBills()
        {
            return Bill = DatabaseLayer.GetBillFromDatabase();
        }

        public List<Bills> GetP()
        {
            return uBill = DatabaseLayer.GetPartyFromDatabase();
        }



        void OnBillChanged()
        {
            if (BillChanged != null)
                BillChanged(this, null);
        }

        void OnuBillChanged()
        {
            if (uBillChanged != null)
                uBillChanged(this, null);
        }
    }

The RelayCommand.cs is generic & my mainwindow.xaml looks partially like

        <GroupBox Header="Data" HorizontalAlignment="Center" VerticalAlignment="Center" BorderThickness="0">
            <Grid>
                <Grid.RowDefinitions>
                    <RowDefinition />
                        <RowDefinition />
                        <RowDefinition Height="Auto"/>
                </Grid.RowDefinitions>
                    <StackPanel Grid.Row="0">
                        <ComboBox ItemsSource="{Binding Vendor}" DisplayMemberPath="Party"
                                        VerticalAlignment="Top"
                                        HorizontalAlignment="Left"
                                        Width="400"
                                        Height="30"
                                        IsTextSearchEnabled="False"
                                        IsEditable="True"
                                        FontSize="17"
                                        Margin="2,10,30,10" />
                    </StackPanel>
                    <DataGrid x:Name="dg1" ItemsSource="{Binding Bill}" SelectedItem="{Binding SelectedBill}" CanUserAddRows="False" Grid.Row="1"
                          CanUserDeleteRows="False" SelectionMode="Single" SelectedIndex="{Binding SelectedIndex}" VerticalAlignment="Top"
                          AutoGenerateColumns="False" Margin="5,5,5,5">
                    <DataGrid.Columns>
                        <DataGridTextColumn Header="Party"  Binding="{Binding Path=Party, Mode=TwoWay}"  Width="105" IsReadOnly="True" />
                        <DataGridTextColumn Header="Bill No."  Binding="{Binding Path=BillNo, Mode=TwoWay}"  Width="75" IsReadOnly="True" />
                        //... some other columns
                    </DataGrid.Columns>
                </DataGrid>
                    
                </Grid>
        </GroupBox>

and last but not the least

    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();

            this.DataContext = new BillsViewModel();

        }
    }

It runs okay and does the job but combobox items takes quite some time to load....I feel I'm repeating a lot of code unnecessarily but don't know how to make it more efficient.

Need help!