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

Last Reply 10 months ago By dharmendr

Posted 10 months ago

I want to show searching text for each column inside datagridview under header in windows form and filter. How it can be done?

https://i.imgur.com/jbsO7oX.gif

        private void DisplayData()
        {
            con = new SqlDbConnect();
            con.SqlQuery("select ASID,ClassName,SectionName,SubjectName from tblAssignSubjects as asub inner join tblDefClass as dc on asub.ClassID=dc.ClassID inner join tblDefSection as ds on asub.SectionID=ds.SectionID inner join tblDefSubject as dsub on asub.SubjectID=dsub.SubjectID;");
            paging.SelectCommand = con.Cmd;
            sBuilder = new SqlCommandBuilder(paging);
            ds = new DataSet();
            paging.Fill(ds, "tblAssignSubjects");
            sTable = ds.Tables["tblAssignSubjects"];
            dGVAssinSub.DataSource = ds.Tables["tblAssignSubjects"].DefaultView;
            
            dGVAssinSub.ReadOnly = true;
        }
You are viewing reply posted by: dharmendr 10 months ago.
Posted 10 months ago Modified on 10 months ago

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