DataGrid row filter based on user inputs without use Linq commands in WPF (by C#)

Question

First and foremost, I apologize for my grammatical errors; my first language is Persian (Iran).
I want to display rows whose value is set by the user
Look at this image:

Suppose I want to display rows that have the same values in the LastName, NumberOfBooksBorrowed, and LoanDate rows (by user input as shown below).

Note: I can get information from Access (2007) via the following code but i can not display it in DataGrid.

OleDbCommand OleDCmd = new OleDbCommand("Select * From MemberTable Where LastName='" +   
LastName_TextBox.Text.Trim() + "'And NumberOfBooksBorrowed='" +   
NumberOfBooksBorrowed_TextBox.Text.Trim() + "'And LoanDate='"+LoanDate_TextBox.Text.Trim()+ "'",   
OleDbConnect);  
OleDCmd.CommandType = System.Data.CommandType.Text;  
OleDbConnect.Open();  
OleDbDataReader DataReader = OleDCmd.ExecuteReader();  
while (DataReader.Read())  
{  
  //Like this  
  //MemberDataGrid.Rows[0] = DataReader[All].ToString();  
}  

Please help, thanks

Answer 1

Hooray, after about two months of trying, I found 3 solutions that I will explain the best solution to you.
First I explain its algorithm

The image below shows you my solution algorithm:

Now we implement it:

    DataTable MemberDT = new DataTable();  
    public MainWindow()  
    {  
        InitializeComponent();  
        MemberDT.Columns.Add("FirstName", typeof(string));  
        MemberDT.Columns.Add("LastName", typeof(string));  
        MemberDT.Columns.Add("DateOfBirth", typeof(string));  
        MemberDT.Columns.Add("IDNumber", typeof(long));  
        MemberDT.Columns.Add("PhoneNumber", typeof(string));  
        MemberDT.Columns.Add("MobilePhoneFirstNumber", typeof(string));  
        MemberDT.Columns.Add("MobilePhoneSecondNumber", typeof(string));  
        MemberDT.Columns.Add("LoanDate", typeof(string));  
        MemberDT.Columns.Add("NumberOfBooksBorrowed", typeof(string));  
        MemberDT.Columns.Add("NamesOfBorrowedBooks", typeof(string));  
        MemberDT.Columns.Add("BookReturnDate", typeof(string));  
        MemberDT.Columns.Add("MembershipDate", typeof(string));  
        MemberDT.Columns.Add("RegistrationFee", typeof(string));  
        MemberDT.Columns.Add("QR", typeof(string));  
        MemberDT.Columns.Add("Credit", typeof(string));  
        MemberDT.Columns.Add("Debt", typeof(string));  
        MemberDT.Columns.Add("Description", typeof(string));  
        MemberDT.Columns.Add("MemberImage", typeof(byte[]));  
    }  

We must first fill in the DataTable.
I used DataReader instead of DataAdapter because it is faster than DataAdapter.

    public DataTable FillDT(DataTable dataTable, string tableName, OleDbConnection oleDbConnect)  
    {  
        oleDbConnect.Open();  
        OleDbCommand OleDCmd = new OleDbCommand("Select * From " + tableName, oleDbConnect);  
        OleDCmd.CommandType = CommandType.Text;  
        OleDbDataReader ODR = OleDCmd.ExecuteReader();  
        dataTable.Rows.Clear();  
        dataTable.Load(ODR);  
        oleDbConnect.Close();  
        return dataTable;  
    }  

    public DataTable SetDT(DataTable dataTable, string column, TextBox textBox, OleDbConnection oleDbConnect)  
    {  
        oleDbConnect.Open();  
        //In this line, if the rows are longer than int (2,147,483,647) an error may occur, I did not test this  
        DataRow[] Rows = dataTable.Select("[" + column + "]='" + textBox.Text.Trim() + "'");  
        switch (Rows.Length > 0)  
        {  
            case true:  
                DataTable DT = new DataTable();  
                DT = dataTable.Copy();  
                dataTable.Rows.Clear();  
                dataTable = DT.Select("[" + column + "]='" + textBox.Text.Trim() + "'").CopyToDataTable();  
                break;  
        }  
        oleDbConnect.Close();  
        return dataTable;  
    }  

    public void MemberFilter(DataGrid memberDataGrid)  
    {  
        MemberDT = FillDT(MemberDT, "MemberTable", OleDbConnect);  
        //We check the user entries one by one (in this case we have 8 Text Boxes), if it wasn't empty, we filter the DataTable based on it  
        switch (LastName_TextBox.Foreground == Brushes.Black && LastName_TextBox.Text!="")  
        {  
            case true:  
                var TempDT = SetDT(MemberDT, "LastName", LastName_TextBox, OleDbConnect);  
                MemberDT.Rows.Clear();  
                MemberDT = TempDT.Copy();  
                break;  
        }  
        switch (LoanDate_TextBox.Foreground == Brushes.Black && LoanDate_TextBox.Text != "")  
        {  
            case true:  
                var TempDT = SetDT(MemberDT, "LoanDate", LoanDate_TextBox, OleDbConnect);  
                MemberDT.Rows.Clear();  
                MemberDT = TempDT.Copy();  
                break;  
        }  
        switch (NumberOfBooksBorrowed_TextBox.Foreground == Brushes.Black && NumberOfBooksBorrowed_TextBox.Text != "")  
        {  
            case true:  
                var TempDT = SetDT(MemberDT, "NumberOfBooksBorrowed", NumberOfBooksBorrowed_TextBox, OleDbConnect);  
                MemberDT.Rows.Clear();  
                MemberDT = TempDT.Copy();  
                break;  
        }  
        switch (NamesOfBorrowedBooks_TextBox.Foreground == Brushes.Black && NamesOfBorrowedBooks_TextBox.Text != "")  
        {  
            case true:  
                var TempDT = SetDT(MemberDT, "NamesOfBorrowedBooks", NamesOfBorrowedBooks_TextBox, OleDbConnect);  
                MemberDT.Rows.Clear();  
                MemberDT = TempDT.Copy();  
                break;  
        }  
        switch (BookReturnDate_TextBox.Foreground == Brushes.Black && BookReturnDate_TextBox.Text != "")  
        {  
            case true:  
                var TempDT = SetDT(MemberDT, "BookReturnDate", BookReturnDate_TextBox, OleDbConnect);  
                MemberDT.Rows.Clear();  
                MemberDT = TempDT.Copy();  
                break;  
        }  
        switch (MembershipDate_TextBox.Foreground == Brushes.Black && MembershipDate_TextBox.Text != "")  
        {  
            case true:  
                var TempDT = SetDT(MemberDT, "MembershipDate", MembershipDate_TextBox, OleDbConnect);  
                MemberDT.Rows.Clear();  
                MemberDT = TempDT.Copy();  
                break;  
        }  
        switch (Credit_TextBox.Foreground == Brushes.Black && Credit_TextBox.Text != "")  
        {  
            case true:  
                var TempDT = SetDT(MemberDT, "Credit", Credit_TextBox, OleDbConnect);  
                MemberDT.Rows.Clear();  
                MemberDT = TempDT.Copy();  
                break;  
        }  
        switch (Debt_TextBox.Foreground == Brushes.Black && Debt_TextBox.Text != "")  
        {  
            case true:  
                var TempDT = SetDT(MemberDT, "Debt", Debt_TextBox, OleDbConnect);  
                MemberDT.Rows.Clear();  
                MemberDT = TempDT.Copy();  
                break;  
        }  
        memberDataGrid.ItemsSource = MemberDT.DefaultView;  
    }  

Finally, we connect the filtered DataTable to the DataGrid.

    private void MemberFilter_RemovingFilter_Button_PreviewMouseLeftButtonDown(object sender, MouseButtonEventArgs e)  
    {  
        switch(MemberFilter_RemovingFilter_Button.Content==(object)"Filter" || MemberFilter_RemovingFilter_Button.Content == (object)"پالایه")  
        {  
            case true:  
               switch(App.EnumLanguage)  
                {  
                    case AllLanguage.English:  
                        MemberFilter_RemovingFilter_Button.Content = "Removing filters";  
                        break;  
                    default:  
                        MemberFilter_RemovingFilter_Button.Content = "حذف پالایه ها";  
                        break;  
                }  
                //We connect the filtered DataTable to the DataGrid  
                MemberFilter(MemberDataGrid);  
                break;  
            default:  
                switch (App.EnumLanguage)  
                {  
                    case AllLanguage.English:  
                        MemberFilter_RemovingFilter_Button.Content = "Filter";  
                        break;  
                    default:  
                        MemberFilter_RemovingFilter_Button.Content = "پالایه";  
                        break;  
                }  
                //To remove all filters, we use the FillDT method (this method first clears the rows and then refills the DataTable)  
                MemberDataGrid.ItemsSource = FillDT(MemberDT, "MemberTable", OleDbConnect).DefaultView;  
                break;  
        }  
    }  

First I show you my DataGrid data:

Results or outputs:
Example A:

Example B:

Thanks

Answer 2

The sample at abhi2434/CollectionViewWPF: Sample code for collection view in WPF should help. I tried it, it works.

Answer 3

Have you considered

• loading data into a DataTable then set the ItemSource of the Grid to the default view of the DataTable?
• Using Entity Framework Core (there is an ms-access provider)? This way in short filter can be done in Lambda and also via a CollectionViewSource which has a filter property ICollectionView cvs = CollectionViewSource.GetDefaultView(EmployeeGrid.ItemsSource); // cvs.Filter = item =>
  item is Employees employees && (employees.LastName.StartsWith(lastNameFilter,
  StringComparison.InvariantCultureIgnoreCase));