c# – How to properly filter datagrid as per combobox text on selectedindexchanged using MVVM pattern?

I have a SQLite database with a table whose columns are represented by the items in the class Bills inside the Model folder.

   public class Bills : ViewModelBase
    {
        private int _id;
        public int Id
        {
            get
            {
                return _id;
            }
            set
            {
                _id = value;
                OnPropertyChanged("Id");
            }
        }

        private string _party;
        public string Party
        {
            get
            {
                return _party;
            }
            set
            {
                _party = value;
                OnPropertyChanged("Party");
            }
        }

        //................

        private ObservableCollection<Bills> _bill;
        public ObservableCollection<Bills> Bill
        {
            get
            {
                return _bill;
            }
            set
            {
                _bill = value;
                OnPropertyChanged("Bill");
            }
        }
    }

Now I’m trying to add the unique data from the Party column of the database and add it as itemsource to the combobox and also when I choose an item from that combobox the datagrid should show the filtered data using the combobox text.

Here is the View ie mainwindow

<ComboBox
    ItemsSource="{Binding ComboItems}"
    DisplayMemberPath="Party"
    local:SelectionChangedBehaviour.Command="{Binding KeyCmd}"
    VerticalAlignment="Top"
    HorizontalAlignment="Left"
    Width="400"
    Height="30"
    IsTextSearchEnabled="False"
    IsEditable="True"
    FontSize="17"
    Margin="2,10,30,10"
    x:Name="cmb" />

    ........

<DataGrid
    ItemsSource="{Binding bills}"  SelectedItem="{Binding SelectedItem}"
    Language="en-IN"
    BorderBrush="Transparent"
    VerticalAlignment="Stretch"
    HorizontalAlignment="Stretch"
    Background="#212121"
    RowBackground="#212121"
    AlternatingRowBackground="#000000"
    ScrollViewer.CanContentScroll="True"
    Foreground="White"
    HeadersVisibility="Column"
    GridLinesVisibility="None"
    SelectionMode="Single"
    IsReadOnly="True"
    HorizontalScrollBarVisibility="Auto"
    CanUserAddRows="False"
    SelectionUnit="FullRow"
    x:Name="dg"
    Margin="10,0,10,20"
    Padding="5"
    AutoGenerateColumns="False">
    <DataGrid.Columns>
        <DataGridTextColumn
            Header="Party"
            Binding="{Binding Path=Party, Mode=OneWay}"
            Width="275"
            IsReadOnly="True" />
        <DataGridTextColumn
            Header="Bill No."
            Binding="{Binding Path=BillNo, Mode=OneWay}"
            Width="275"
            IsReadOnly="True" />
            ......
    </DataGrid.Columns>
</DataGrid>

and this is the ViewModel

public class BillsViewModel : ViewModelBase
    {
        MainWindow mainwindow = Application.Current.Windows
                                .Cast<Window>()
                                .FirstOrDefault(window => window is MainWindow) as MainWindow;

        DataSet ds;
        public ObservableCollection<Bills> bills { get; set; }
        public ObservableCollection<Bills> partys { get; set; }


        public void FillDG()
        {
            using (SQLiteConnection conn = new SQLiteConnection(@"Data Source="+Path.GetFullPath("./Bills.db;")))
            {
                conn.Open();

                string stm = "SELECT * FROM billdata ORDER BY Party";

                using (SQLiteCommand cmd = new SQLiteCommand(stm, conn))
                {
                    using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd))
                    {
                        ds = new DataSet();
                        adapter.Fill(ds, "billdata");

                        if (bills == null)
                            bills = new ObservableCollection<Bills>();

                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            bills.Add(new Bills
                            {
                                Id = Convert.ToInt32(dr[0].ToString()),
                                Party = dr[1].ToString(),
                                BillNo = dr[2].ToString(),
                                BillDt = dr[3].ToString(),
                                Amt = float.Parse(dr[4].ToString()),
                                DueDt = dr[5].ToString(),
                                PaidOn = dr[6].ToString()
                            });
                        }
                    }
                }
                ds = null;
                conn.Close();
            }
        }

        public ObservableCollection<Bills> FillCombos2()
        {

            using (SQLiteConnection conn = new SQLiteConnection(@"Data Source="+Path.GetFullPath("./Bills.db;")))
            {
                conn.Open();

                string stm = "SELECT DISTINCT Party FROM billdata ORDER BY Party";

                using (SQLiteCommand cmd = new SQLiteCommand(stm, conn))
                {
                    using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd))
                    {
                        ds = new DataSet();
                        adapter.Fill(ds, "billdata");

                        if (partys == null)
                            partys = new ObservableCollection<Bills>();

                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            partys.Add(new Bills
                            {
                                Party = dr[0].ToString(),
                            });
                        }
                    }
                }
                ds = null;
                conn.Close();
            }
            return partys;
        }


        public ICollectionView ComboItems { get; set; }

        private ICommand _keyCmd;

        public ICommand KeyCmd
        {
            get
            {
                if (_keyCmd == null)
                    _keyCmd = new RelayCommand(param => FilterSource(), null);

                return _keyCmd;
            }
        }

        public void FilterSource()
        {
            using (SQLiteConnection conn = new SQLiteConnection(@"Data Source="+Path.GetFullPath("./Bills.db")))
            {
                conn.Open();

                SQLiteCommand command = new SQLiteCommand("SELECT * FROM billdata WHERE Party LIKE @name", conn);
                command.Parameters.AddWithValue("@name", mainwindow.cmb.Text);
                //command.Parameters.AddWithValue("@name", (sender as System.Windows.Controls.ComboBox).SelectedItem as string);
                command.ExecuteNonQuery();

                SQLiteDataAdapter adap = new SQLiteDataAdapter(command);

                DataTable dt = new DataTable("wtf");
                adap.Fill(dt);

                mainwindow.dg.ItemsSource=dt.DefaultView;

                conn.Close();
            }
        }

        public BillsViewModel()
        {
            var myItems = FillCombos2();
            ComboItems = CollectionViewSource.GetDefaultView(myItems);
            FillDG();
        }
    }

public class ViewModelBase : INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;

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

I’ve also used a class SelectionChangedBehaviour in my code taken from here as I did not want to break the MVVM pattern.

But, first of all I’m not sure if this break the MVVM pattern or not and when I change the items of the combobox it initially shows a blank datagrid, then when I change it the 2nd time it filters the datagrid as per the previously chosen combobox text. How do I fix these ?

Leave a Comment