Introduction
In this article we will learn about how to filter data in datagridview. We can better understand this with an example.
Step 1
Create Windows Form with Textbox and DataGridView.
![Form]()
Step 2
In coding view, code as per the following code. Here, I give both code c# and vb.Net. You can choose as per your requirement(s).
Code as per Vb.Net
Imports System.Data.SqlClient
Public Class Form14
Dim libconn As SqlConnection
Dim daMain As SqlDataAdapter
Dim dtMain As New DataSet
Dim strQuery As String = ""
Dim strConnectionString As String
Dim otable As DataTable = New DataTable()
Private Sub Form14_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
load_data()
DataGridView1.AllowUserToAddRows = False
DataGridView1.AllowUserToDeleteRows = False
End Sub
Private Sub load_data()
Connetion()
daMain = New SqlDataAdapter("Select * From Employee", libconn)
dtMain.Clear()
daMain.Fill(dtMain)
DataGridView1.DataSource = dtMain.Tables(0)
libconn.Close()
DataGridView1.ClearSelection()
TextBox1.Text = ""
otable = GetOriginalDataTable()
End Sub
Public Function Connetion()
strConnectionString = "Data Source=UDAY-LAPTOP;Initial Catalog=sqldemo;Integrated Security=true"
libconn = New SqlConnection
libconn.ConnectionString = strConnectionString
If libconn.State <> ConnectionState.Open Then
Try
libconn.Open()
Catch conn_error As SqlException
MsgBox(conn_error.Message)
Connetion = False
End Try
End If
Connetion = True
End Function
Private Function GetOriginalDataTable() As DataTable
Dim dtable As DataTable = New DataTable()
For Each col As DataGridViewColumn In DataGridView1.Columns
dtable.Columns.Add(col.Name)
Next
For Each row As DataGridViewRow In DataGridView1.Rows
Dim dRow As DataRow = dtable.NewRow()
Dim flag As Integer = -1
For Each cell As DataGridViewCell In row.Cells
dRow(cell.ColumnIndex) = cell.Value
Next
dtable.Rows.Add(dRow)
Next
Return dtable
End Function
Private Function SearchGrid()
Dim dtable As DataTable = New DataTable()
If TextBox1.Text.Length > 0 And DataGridView1.RowCount = 0 Then
DataGridView1.DataSource = otable
End If
If TextBox1.Text.Length = 0 Then
DataGridView1.DataSource = Nothing
DataGridView1.DataSource = otable
Else
For Each col As DataGridViewColumn In DataGridView1.Columns
dtable.Columns.Add(col.Name)
Next
For Each row As DataGridViewRow In DataGridView1.Rows
Dim dRow As DataRow = dtable.NewRow()
Dim flag As Integer = -1
For Each cell As DataGridViewCell In row.Cells
dRow(cell.ColumnIndex) = cell.Value
Dim str As String = cell.Value.ToString().ToLower()
Dim str1 As String = TextBox1.Text.ToLower()
If str.Contains(str1.ToString()) = True Then
flag = 1
End If
Next
If flag = 1 Then
dtable.Rows.Add(dRow)
End If
Next
DataGridView1.DataSource = Nothing
DataGridView1.DataSource = dtable
End If
SearchGrid = True
End Function
Private Function HighlightGrid()
If TextBox1.Text.Length = 0 Then
For n As Integer = 0 To (DataGridView1.Rows.Count) - 1
For m As Integer = 0 To (DataGridView1.Rows(n).Cells.Count) - 1
DataGridView1.Rows(n).Cells(m).Style.BackColor = SystemColors.Control
Next
Next
Else
For n As Integer = 0 To (DataGridView1.Rows.Count) - 1
For m As Integer = 0 To (DataGridView1.Rows(n).Cells.Count) - 1
Dim str As String = DataGridView1.Rows(n).Cells(m).Value.ToString().ToLower()
Dim str1 As String = TextBox1.Text.ToLower()
If str.Contains(str1.ToString()) = True Then
DataGridView1.Rows(n).Cells(m).Style.BackColor = Color.Yellow
Else
DataGridView1.Rows(n).Cells(m).Style.BackColor = SystemColors.Control
End If
Next
Next
End If
HighlightGrid = True
End Function
Private Sub TextBox1_KeyUp(sender As Object, e As System.Windows.Forms.KeyEventArgs) Handles TextBox1.KeyUp
If e.KeyCode = Keys.Back Then
DataGridView1.DataSource = otable
SearchGrid()
HighlightGrid()
DataGridView1.ClearSelection()
End If
End Sub
Private Sub TextBox1_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox1.TextChanged
SearchGrid()
HighlightGrid()
DataGridView1.ClearSelection()
End Sub
End Class
Code as per C#
using System.Data.SqlClient;
public class Form14
{
private SqlConnection libconn;
private SqlDataAdapter daMain;
private DataSet dtMain = new DataSet();
private string strQuery = "";
private string strConnectionString;
private DataTable otable = new DataTable();
private void Form14_Load(System.Object sender, System.EventArgs e)
{
load_data();
DataGridView1.AllowUserToAddRows = false;
DataGridView1.AllowUserToDeleteRows = false;
}
private void load_data()
{
Connetion();
daMain = new SqlDataAdapter("Select * From Employee", libconn);
dtMain.Clear();
daMain.Fill(dtMain);
DataGridView1.DataSource = dtMain.Tables(0);
libconn.Close();
DataGridView1.ClearSelection();
TextBox1.Text = "";
otable = GetOriginalDataTable();
}
public void Connetion()
{
strConnectionString = "Data Source=UDAY-LAPTOP;Initial Catalog=sqldemo;Integrated Security=true";
libconn = new SqlConnection();
libconn.ConnectionString = strConnectionString;
if (libconn.State != ConnectionState.Open)
{
try
{
libconn.Open();
}
catch (SqlException conn_error)
{
Interaction.MsgBox(conn_error.Message);
Connetion = false;
}
}
Connetion = true;
}
private DataTable GetOriginalDataTable()
{
DataTable dtable = new DataTable();
foreach (DataGridViewColumn col in DataGridView1.Columns)
dtable.Columns.Add(col.Name);
foreach (DataGridViewRow row in DataGridView1.Rows)
{
DataRow dRow = dtable.NewRow();
int flag = -1;
foreach (DataGridViewCell cell in row.Cells)
dRow(cell.ColumnIndex) = cell.Value;
dtable.Rows.Add(dRow);
}
return dtable;
}
private void SearchGrid()
{
DataTable dtable = new DataTable();
if (TextBox1.Text.Length > 0 & DataGridView1.RowCount == 0)
DataGridView1.DataSource = otable;
if (TextBox1.Text.Length == 0)
{
DataGridView1.DataSource = null;
DataGridView1.DataSource = otable;
}
else
{
foreach (DataGridViewColumn col in DataGridView1.Columns)
dtable.Columns.Add(col.Name);
foreach (DataGridViewRow row in DataGridView1.Rows)
{
DataRow dRow = dtable.NewRow();
int flag = -1;
foreach (DataGridViewCell cell in row.Cells)
{
dRow(cell.ColumnIndex) = cell.Value;
string str = cell.Value.ToString().ToLower();
string str1 = TextBox1.Text.ToLower();
if (str.Contains(str1.ToString()) == true)
flag = 1;
}
if (flag == 1)
dtable.Rows.Add(dRow);
}
DataGridView1.DataSource = null;
DataGridView1.DataSource = dtable;
}
SearchGrid = true;
}
private void HighlightGrid()
{
if (TextBox1.Text.Length == 0)
{
for (int n = 0; n <= (DataGridView1.Rows.Count) - 1; n++)
{
for (int m = 0; m <= (DataGridView1.Rows(n).Cells.Count) - 1; m++)
DataGridView1.Rows(n).Cells(m).Style.BackColor = SystemColors.Control;
}
}
else
for (int n = 0; n <= (DataGridView1.Rows.Count) - 1; n++)
{
for (int m = 0; m <= (DataGridView1.Rows(n).Cells.Count) - 1; m++)
{
string str = DataGridView1.Rows(n).Cells(m).Value.ToString().ToLower();
string str1 = TextBox1.Text.ToLower();
if (str.Contains(str1.ToString()) == true)
DataGridView1.Rows(n).Cells(m).Style.BackColor = Color.Yellow;
else
DataGridView1.Rows(n).Cells(m).Style.BackColor = SystemColors.Control;
}
}
HighlightGrid = true;
}
private void TextBox1_KeyUp(object sender, System.Windows.Forms.KeyEventArgs e)
{
if (e.KeyCode == Keys.Back)
{
DataGridView1.DataSource = otable;
SearchGrid();
HighlightGrid();
DataGridView1.ClearSelection();
}
}
private void TextBox1_TextChanged(System.Object sender, System.EventArgs e)
{
SearchGrid();
HighlightGrid();
DataGridView1.ClearSelection();
}
}
Output 1
When you run the application, by default all data will be loaded in datagridview as per the following:
![Form]()
Final Output
In Textbox, when I type IT, the following records will be filtered:
![Output]()
Summary
In this article, we have learned about how to filter data in datagridview and also highlight cells.