Filter Windows Form DataGridView using TextBox in Header (Header Row) in C# and VB.Net

Hi smile,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Drawing;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing

Code

C#

public partial class Form1 : Form
{
    public string ContactName { get; set; }
    public string Country { get; set; }
    public Form1()
    {
        InitializeComponent();
    }
    private void Form1_Load(object sender, EventArgs e)
    {
        ContactName = "";
        Country = "";
        dataGridView1.DataSource = this.PopulateDataGridView();
        dataGridView1.ReadOnly = true;
    }

    private DataTable PopulateDataGridView()
    {
        string constr = @"Data Source=.;Initial Catalog=Northwind;User ID=sa;Password=123";
        using (SqlConnection con = new SqlConnection(constr))
        {
            string query = "SELECT CustomerID,ContactName,Country FROM Customers";
            query += " WHERE (ContactName LIKE @Name + '%' OR @Name IS NULL)";
            query += " AND (Country LIKE @Country + '%' OR @Country IS NULL)";
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.AddWithValue("@Name", !string.IsNullOrEmpty(ContactName) ? ContactName : (object)DBNull.Value);
                cmd.Parameters.AddWithValue("@Country", !string.IsNullOrEmpty(Country) ? Country : (object)DBNull.Value);
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
    }

    private void dataGridView1_Paint(object sender, PaintEventArgs e)
    {
        int columnIndex = 1;
        Point headerCellLocation = this.dataGridView1.GetCellDisplayRectangle(columnIndex, -1, true).Location;
        //Place the TextBox in the Location of the Header Cell.
        TextBox txtBox = new TextBox();
        txtBox.Location = new Point(headerCellLocation.X + 100, headerCellLocation.Y);
        txtBox.Width = 100;
        txtBox.TextChanged += new EventHandler(TextBox_Changed);
        dataGridView1.Controls.Add(txtBox);
        dataGridView1.Columns[1].Width = 200;

        columnIndex = 2;
        headerCellLocation = this.dataGridView1.GetCellDisplayRectangle(columnIndex, -1, true).Location;
        //Place the TextBox in the Location of the Header Cell.
        txtBox = new TextBox();
        txtBox.Location = new Point(headerCellLocation.X + 100, headerCellLocation.Y);
        txtBox.Width = 100;
        txtBox.TextChanged += new EventHandler(CountryTextBox_Changed);
        dataGridView1.Controls.Add(txtBox);
        dataGridView1.Columns[2].Width = 200;
    }

    private void TextBox_Changed(object sender, EventArgs e)
    {
        ContactName = (sender as TextBox).Text;
        dataGridView1.DataSource = this.PopulateDataGridView();
        dataGridView1.ReadOnly = true;
    }

    private void CountryTextBox_Changed(object sender, EventArgs e)
    {
        Country = (sender as TextBox).Text;
        dataGridView1.DataSource = this.PopulateDataGridView();
        dataGridView1.ReadOnly = true;
    }
}

VB.Net

Public Class Form1
    Public Property ContactName As String
    Public Property Country As String
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        ContactName = ""
        Country = ""
        dataGridView1.DataSource = Me.PopulateDataGridView()
        dataGridView1.ReadOnly = True
    End Sub

    Private Function PopulateDataGridView() As DataTable
        Dim constr As String = "Data Source=.;Initial Catalog=Northwind;User ID=sa;Password=123"
        Using con As SqlConnection = New SqlConnection(constr)
            Dim query As String = "SELECT CustomerID,ContactName,Country FROM Customers"
            query += " WHERE (ContactName LIKE @Name + '%' OR @Name IS NULL)"
            query += " AND (Country LIKE @Country + '%' OR @Country IS NULL)"
            Using cmd As SqlCommand = New SqlCommand(query, con)
                cmd.Parameters.AddWithValue("@Name", If(Not String.IsNullOrEmpty(Name), ContactName, CObj(DBNull.Value)))
                cmd.Parameters.AddWithValue("@Country", If(Not String.IsNullOrEmpty(Country), Country, CObj(DBNull.Value)))
                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

    Private Sub dataGridView1_Paint(ByVal sender As Object, ByVal e As PaintEventArgs) Handles dataGridView1.Paint
        Dim columnIndex As Integer = 1
        ' Adding TextBox to header cell.
        Dim headerCellLocation As Point = dataGridView1.GetCellDisplayRectangle(columnIndex, -1, True).Location
        Dim txtBox As TextBox = New TextBox()
        txtBox.Location = New Point(headerCellLocation.X + 100, headerCellLocation.Y)
        txtBox.Width = 100
        AddHandler txtBox.TextChanged, AddressOf TextBox_Changed
        dataGridView1.Controls.Add(txtBox)
        dataGridView1.Columns(1).Width = 200

        columnIndex = 2
        headerCellLocation = Me.dataGridView1.GetCellDisplayRectangle(columnIndex, -1, True).Location
        txtBox = New TextBox()
        txtBox.Location = New Point(headerCellLocation.X + 100, headerCellLocation.Y)
        txtBox.Width = 100
        AddHandler txtBox.TextChanged, AddressOf CountryTextBox_Changed
        dataGridView1.Controls.Add(txtBox)
        dataGridView1.Columns(2).Width = 200
    End Sub

    Private Sub TextBox_Changed(ByVal sender As Object, ByVal e As EventArgs)
        ContactName = (TryCast(sender, TextBox)).Text
        dataGridView1.DataSource = Me.PopulateDataGridView()
        dataGridView1.ReadOnly = True
    End Sub

    Private Sub CountryTextBox_Changed(ByVal sender As Object, ByVal e As EventArgs)
        Country = (TryCast(sender, TextBox)).Text
        dataGridView1.DataSource = Me.PopulateDataGridView()
        dataGridView1.ReadOnly = True
    End Sub
End Class

Screenshot