How to filter datagridview using multiple combo boxes?

[rockovo]

Hi all!

​

I am very new to coding and am trying to learn C# through little project. I spent a week or more trying to find solution, but although there is many threads, none of them made sense for me and couldn't get it to work.
I have a form with datagridview and I want to create another 5 comboboxes to filter it. All with same data source.
Let's say, if user select combobox with specific "Role", it will display all records of employees that are in this role, however if user additionally select another combobox e.g. "Shift", it will further narrow it down to the employees in this "Role" on that "Shift"
I managed so far to get datagridview working with one combobox but don't know how to add more of them.
Can you please give me an example based on my code on how to add more comboboxes?
I'd much appreciate your help.
Below is the code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace TM_Hub
{
    public partial class frmEmpList : Form
   {
       public frmEmpList()
      {
          InitializeComponent();
      }

    private void frmEmpList_Load(object sender, EventArgs e)
   {
       this.PopulateComboBox();
       this.PopulateDataGridView();
   }
   private void PopulateComboBox()
  {
      string query = "SELECT DISTINCT LastName FROM Employees";
      string constr = @"Data Source=DESKTOP-Q2B3UUH\SQLEXPRESS;Initial Catalog=PeopleManager;Integrated Security=True";
      using (SqlConnection con = new SqlConnection(constr))
     {
         using (SqlDataAdapter sda = new SqlDataAdapter(query, con))
         {
          //Fill the DataTable with records from Table.
          DataTable dt = new DataTable();
          sda.Fill(dt);

          //Insert the Default Item to DataTable.
          DataRow row = dt.NewRow();
          row[0] = "";
          dt.Rows.InsertAt(row, 0);

          //Assign DataTable as DataSource.
          cbLastName.DataSource = dt;
          cbLastName.DisplayMember = "LastName";
          cbLastName.ValueMember = "LastName";
          }
     }
 }
     private void PopulateDataGridView()
    {
         string query = "SELECT EmpID, FirstName, LastName, Role, Grade, Dept, Shift FROM Employees";
         query += " WHERE LastName = @LastName";
         query += " OR ISNULL(@LastName, '') = ''";
         string constr = @"Data Source=DESKTOP-Q2B3UUH\SQLEXPRESS;Initial Catalog=PeopleManager;Integrated Security=True";
         using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
           {
               cmd.Parameters.AddWithValue("@LastName", cbLastName.SelectedValue);
               using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
              {
                  DataTable dt = new DataTable();
                  sda.Fill(dt);
                  dgEmpList.DataSource = dt;
               }
            }
         }
      }

      private void cbLastName_SelectionChangeCommitted(object sender, EventArgs e)
     {
         this.PopulateDataGridView();
     }
   }
}

Thanks in advance!

 

[jmcilhinney]

Add a BindingSource to your form and then, rather than binding the DataTable directly to the DataGridView, bind the DataTable to the BindingSource and the BindingSource to the grid. You can then filter the data by setting the Filter property of the BingingSource. You can write a single method to do that and then call that method from the SelectedIndexChanged or SelectionChangeCommitted event handler of each ComboBox. Such a method might look like this:

var comboBoxes = new [] {cbLastName, …]; // Put all your ComboBoexes here.

// This will get a string like "LastName = 'Smith'" for each ComboBox with a selection.
var criteria = comboBoxes.Where(cb=> cb.Text != string.Empty)
                         .Select(cb => $"{cb.DisplayMember} = '{cb.Text}'")
                         .ToArray();

// Combine the criteria if there are any, otherwise clear the filter.
BindingSource1.Filter = criteria.Any()
                                       ? string.Join(" AND ", crtieria)
                                       : string.Empty;

Just note that, as I specified in the comments, that code will put single quotes around the value in each case. If any of those values are not strings then you'll have to account for that, i.e. not add single quotes for numbers and add '#' symbols and appropriate format for dates.