In this article I will explain with an example, how to Filter DataGridView using Multiple CheckBoxes in Windows Forms Application using C# and VB.Net.
By default the DataGridView will show all records. As soon as user selects a CheckBox, the records will be searched in Database and the DataGridView rows will be filtered in Windows Forms (WinForms) Application using C# and VB.Net.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Form Design
The Form consists of a FlowPanelLayout and a DataGridView control.
![Filter DataGridView using Multiple CheckBoxes in Windows Forms Application using C# and VB.Net]()
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Filter DataGridView using Multiple CheckBoxes in Windows Forms Application
Inside the Form Load event handler, first the list of Countries is fetched from the Database Table and then a loop is executed over the list of Countries.
Inside the loop, CheckBox control is dynamically created and it is assigned with a CheckChanged event handler.
Finally dynamically created CheckBox is added to the FlowLayoutPanel control.
The DataGridView is populated using the PopulateDataGridView function in two places, first in the Form Load event handler and second in the CheckChanged event handler of the dynamic CheckBoxes.
Inside the PopulateDataGridView function, a loop is executed over all the CheckBoxes and if a CheckBox is checked then the Country appended to the SQL Query.
The logic works as follows:-
When none of Items in CheckBoxList is selected the SQL Query is:
"SELECT ContactName, Country FROM Customers"
When CheckBoxes (say Argentina and Austria) are selected then the SQL Query is:
"SELECT ContactName, Country FROM Customers WHERE Country IN ('Argentina', 'Austria')"
Thus using the dynamic SQL Query, the DataGridView can be easily filtered based on multiple CheckBoxes.
C#
private void Form1_Load(object sender, EventArgs e)
{
this.PopulateCheckBoxes();
this.PopulateDataGridView();
}
private void Country_Checked(object sender, EventArgs e)
{
this.PopulateDataGridView();
}
private void PopulateCheckBoxes()
{
string query = "SELECT DISTINCT TOP 3 Country FROM Customers";
string constr = @"Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123";
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);
//Loop and add CheckBoxes to FloyLayoutPanel.
foreach (DataRow row in dt.Rows)
{
CheckBox chk = new CheckBox();
chk.Text = row["Country"].ToString();
chk.CheckedChanged += new EventHandler(Country_Checked);
checkBoxPanel.Controls.Add(chk);
}
}
}
}
private void PopulateDataGridView()
{
string query = "SELECT CustomerID, ContactName, Country FROM Customers";
string condition = string.Empty;
foreach (CheckBox chk in checkBoxPanel.Controls.OfType<CheckBox>())
{
condition += chk.Checked ? string.Format("'{0}',", chk.Text) : string.Empty;
}
if (!string.IsNullOrEmpty(condition))
{
condition = string.Format(" WHERE Country IN ({0})", condition.Substring(0, condition.Length - 1));
}
string constr = @"Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query + condition, con))
{
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
}
}
}
}
VB.Net
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Me.PopulateCheckBoxes()
Me.PopulateDataGridView()
End Sub
Private Sub Country_Checked(ByVal sender As Object, ByVal e As EventArgs)
Me.PopulateDataGridView()
End Sub
Private Sub PopulateCheckBoxes()
Dim query As String = "SELECT DISTINCT TOP 3 Country FROM Customers"
Dim constr As String = "Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123"
Using con As SqlConnection = New SqlConnection(constr)
Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
'Fill the DataTable with records from Table.
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
'Loop and add CheckBoxes to FloyLayoutPanel.
For Each row As DataRow In dt.Rows
Dim chk As CheckBox = New CheckBox()
chk.Text = row("Country").ToString()
AddHandler chk.CheckedChanged, AddressOf Country_Checked
checkBoxPanel.Controls.Add(chk)
Next
End Using
End Using
End Sub
Private Sub PopulateDataGridView()
Dim query As String = "SELECT CustomerID, ContactName, Country FROM Customers"
Dim condition As String = String.Empty
For Each chk As CheckBox In checkBoxPanel.Controls.OfType(Of CheckBox)()
condition &= If(chk.Checked, String.Format("'{0}',", chk.Text), String.Empty)
Next
If Not String.IsNullOrEmpty(condition) Then
condition = String.Format(" WHERE Country IN ({0})", condition.Substring(0, condition.Length - 1))
End If
Dim constr As String = "Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123"
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(query & condition, con)
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
dataGridView1.DataSource = dt
End Using
End Using
End Using
End Sub
Screenshot
![Filter DataGridView using Multiple CheckBoxes in Windows Forms Application using C# and VB.Net]()
Downloads
