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

donB

New member
Joined
May 25, 2022
Messages
1
Programming Experience
3-5
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

1653485450429.png


The files code's look like this


Bills.cs:
    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");
            }
        }
        //....
    }

ViewModelBase.cs:
    public class ViewModelBase : INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;

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

BillsViewModel.cs:
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");
            }));
        }

    }

DatabaseLayer.cs:
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;
            }
        }

    }

PersonnelBusinessObject.cs:
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
XML:
        <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

MainWindow.xaml.cs:
    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!
 
Top Bottom