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

Last Reply one month ago By pandeyism

Posted one month ago

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

You are viewing reply posted by: pandeyism one month ago.
Posted one month 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