Highlight matching records in Windows Form DataGridView after search result on Button Click using C# and VB.Net

Last Reply 3 months ago By pandeyism

Posted 3 months ago

how to highlight matching records in datagridview rows after search result returns

Posted 3 months ago

Hey satabeach,

Please refer below sample.

Namespaces

C#

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

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

private void Form1_Load(object sender, EventArgs e)
{
    dataGridView1.DataSource = this.PopulateDataGridView();
}

private DataTable PopulateDataGridView()
{
    string query = "SELECT CustomerID, Name, Country FROM Customers";
    string constr = @"Data Source=.\SQL2005;Initial Catalog=Test;User ID=sa;Password=pass@123";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }
        }
    }
}

private DataTable FiterDataGridView()
{
    string query = "SELECT CustomerID, Name, Country FROM Customers";
    query += " WHERE Country LIKE '%' + @Country + '%'";
    query += " OR @Country = ''";
    string constr = @"Data Source=.\SQL2005;Initial Catalog=Test;User ID=sa;Password=pass@123";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@Country", txtName.Text.Trim());
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }
        }
    }
}

private void Search_Click(object sender, EventArgs e)
{
    dataGridView1.DataSource = this.PopulateDataGridView();
    DataTable filterDt = FiterDataGridView();
    for (int i = 0; i < dataGridView1.Rows.Count; i++)
    {
        int id = Convert.ToInt32(dataGridView1.Rows[i].Cells[0].Value.ToString().Trim());
        string country = dataGridView1.Rows[i].Cells[2].Value.ToString().Trim();
        DataRow[] dr = filterDt.Select("CustomerID=" + id + " AND Country = '" + country + "'");
        if (dr.Length > 0)
        {
            dataGridView1.Rows[i].DefaultCellStyle.BackColor = Color.Red;
            dataGridView1.Rows[i].DefaultCellStyle.ForeColor = Color.White;
        }
    }
}

VB.Net

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    dataGridView1.DataSource = Me.PopulateDataGridView()
End Sub

Private Function PopulateDataGridView() As DataTable
    Dim query As String = "SELECT CustomerID, Name, Country FROM Customers"
    Dim constr As String = "Data Source=.\SQL2005;Initial Catalog=Test;User ID=sa;Password=pass@123"

    Using con As SqlConnection = New SqlConnection(constr)

        Using cmd As SqlCommand = New SqlCommand(query, con)

            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 Function FiterDataGridView() As DataTable
    Dim query As String = "SELECT CustomerID, Name, Country FROM Customers"
    query += " WHERE Country LIKE '%' + @Country + '%'"
    query += " OR @Country = ''"
    Dim constr As String = "Data Source=.\SQL2005;Initial Catalog=Test;User ID=sa;Password=pass@123"

    Using con As SqlConnection = New SqlConnection(constr)

        Using cmd As SqlCommand = New SqlCommand(query, con)
            cmd.Parameters.AddWithValue("@Country", txtName.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

Private Sub Search_Click(ByVal sender As Object, ByVal e As EventArgs)
    dataGridView1.DataSource = Me.PopulateDataGridView()
    Dim filterDt As DataTable = FiterDataGridView()

    For i As Integer = 0 To dataGridView1.Rows.Count - 1
        Dim id As Integer = Convert.ToInt32(dataGridView1.Rows(i).Cells(0).Value.ToString().Trim())
        Dim country As String = dataGridView1.Rows(i).Cells(2).Value.ToString().Trim()
        Dim dr As DataRow() = filterDt.Select("CustomerID=" & id & " AND Country = '" & country & "'")

        If dr.Length > 0 Then
            dataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.Red
            dataGridView1.Rows(i).DefaultCellStyle.ForeColor = Color.White
        End If
    Next
End Sub

Screenshot