In this article I will explain with an example, how to Search all (multiple) Columns of DataGridView in Windows Forms (WinForms) Application using C# and VB.Net.
By default the DataGridView will show all records. As soon as user starts to type in the TextBox, the records will be searched in all (multiple) Columns displayed in the DataGridView 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 TextBox and a DataGridView control.
![Search all (multiple) Columns of DataGridView 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
Search all (multiple) Columns of DataGridView in Windows Forms Application
The DataGridView is populated using the PopulateDataGridView function in two places, first in the Form Load event handler and second in the KeyUp event handler of the TextBox.
The SQL Query is created in such a way that it will return all records if the TextBox value is Blank and will return filtered records when the TextBox has a value.
The SQL Query will match the TextBox value with all the three columns displayed in the DataGridView.
Inside the PopulateDataGridView function, the SQL Query is executed and the results are populated into a DataTable.
C#
private void Form1_Load(object sender, EventArgs e)
{
dataGridView1.DataSource = this.PopulateDataGridView();
}
private void txtName_KeyUp(object sender, KeyEventArgs e)
{
dataGridView1.DataSource = this.PopulateDataGridView();
}
private DataTable PopulateDataGridView()
{
string query = "SELECT CustomerID, ContactName, Country FROM Customers";
query += " WHERE CustomerID LIKE '%' + @SearchTerm + '%'";
query += " OR ContactName LIKE '%' + @SearchTerm + '%'";
query += " OR Country LIKE '%' + @SearchTerm + '%'";
query += " OR @SearchTerm = ''";
string constr = @"Data Source=.\SQL2017;Initial Catalog=Northwind;Integrated Security=true";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.Parameters.AddWithValue("@SearchTerm", txtSearchTerm.Text.Trim());
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
}
}
VB.Net
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
dataGridView1.DataSource = Me.PopulateDataGridView()
End Sub
Private Sub txtName_KeyUp(sender As System.Object, e As System.Windows.Forms.KeyEventArgs) Handles txtName.KeyUp
dataGridView1.DataSource = Me.PopulateDataGridView()
End Sub
Private Function PopulateDataGridView() As DataTable
Dim query As String = "SELECT CustomerID, ContactName, Country FROM Customers"
query &= " WHERE CustomerID LIKE '%' + @SearchTerm + '%'"
query &= " OR ContactName LIKE '%' + @SearchTerm + '%'"
query &= " OR Country LIKE '%' + @SearchTerm + '%'"
query &= " OR @SearchTerm = ''"
Dim constr As String = "Data Source=.\SQL2017;Initial Catalog=Northwind;Integrated Security=true"
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(query, con)
cmd.Parameters.AddWithValue("@SearchTerm", txtSearchTerm.Text.Trim())
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
Screenshot
![Search all (multiple) Columns of DataGridView in Windows Forms Application using C# and VB.Net]()
Downloads
