question

RezaJafery avatar image
0 Votes"
RezaJafery asked RezaJafery edited

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

First of all I am sorry for my language grammar because my first language is Persian (Iran).
I want to display rows whose value is set by the user
Look at this image:

121444-database.png

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

121396-filter.png

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





dotnet-csharpdotnet-wpf-xaml
database.png (14.8 KiB)
filter.png (174.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sometimes we need to be patient. Quite often Microsoft personnel review unanswered questions. They are more likely to review the older ones, so they are more likely to review your DataGrid row filter based on user inputs without use Linq commands in WPF (by C#) before they get to this one, if it had not been closed.


0 Votes 0 ·
RezaJafery avatar image
0 Votes"
RezaJafery answered RezaJafery edited

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:
139017-algorithm.png
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:
139005-example.gif
Results or outputs:
Example A:
139025-filter-a.gif
Example B:
139026-filter-b-and-removing-filters.gif
Thanks



algorithm.png (297.2 KiB)
example.gif (2.9 MiB)
filter-a.gif (610.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SimpleSamples avatar image
0 Votes"
SimpleSamples answered RezaJafery commented

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This answer, did not solve my problem.

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered RezaJafery commented

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));



· 9
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

When you say loading data into a DataTable do you mean something as in my answer to the previous version of this question, the version that got closed? And my answer posted here links to a complete sample that uses CollectionViewSource.

0 Votes 0 ·

@SimpleSamples

Yes but from this code sample which uses EF Core.

121915-figure1.png


0 Votes 0 ·
figure1.png (9.8 KiB)

If RezaJafery learns Entity Framework then that will be best.

0 Votes 0 ·
Show more comments
RezaJafery avatar image RezaJafery karenpayneoregon ·

Thanks for your feedback, share my solution

0 Votes 0 ·