Hi PRA,
You have to use DataTable Select method for filtering the records or use linq query or DataTable.DefaultView.RowFilter property.
Please refer below sample. Here i have used DataTable Select method.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
Namespace
C#
using System.Data.SqlClient;
VB.Net
Imports System.Data.SqlClient
Code
C#
public Form1()
{
InitializeComponent();
dgvGrid.AutoGenerateColumns = false;
dgvGrid.ColumnCount = 4;
dgvGrid.Columns[0].DataPropertyName = "CustomerID";
dgvGrid.Columns[0].HeaderText = "ID";
dgvGrid.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dgvGrid.Columns[1].DataPropertyName = "ContactName";
dgvGrid.Columns[1].HeaderText = "Name";
dgvGrid.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dgvGrid.Columns[2].DataPropertyName = "City";
dgvGrid.Columns[2].HeaderText = "City";
dgvGrid.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
dgvGrid.Columns[3].DataPropertyName = "Country";
dgvGrid.Columns[3].HeaderText = "Country";
dgvGrid.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
BindGrid(string.Empty);
}
private void BindGrid(string country)
{
string constring = @"Data Source=.;Initial Catalog=Northwind;UID=sa;PWD=pass@123";
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers",con))
{
using (DataTable dt = new DataTable())
{
con.Open();
try
{
dt.Load(cmd.ExecuteReader());
if (!string.IsNullOrEmpty(country))
{
DataRow[] drs = dt.Select("Country='" + country + "'");
if (drs.Length > 0)
{
dt = drs.CopyToDataTable();
}
}
}
catch { }
con.Close();
dgvGrid.DataSource = dt;
}
}
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
BindGrid(txtCountry.Text);
}
VB.Net
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
InitializeComponent()
dgvGrid.AutoGenerateColumns = False
dgvGrid.ColumnCount = 4
dgvGrid.Columns(0).DataPropertyName = "CustomerID"
dgvGrid.Columns(0).HeaderText = "ID"
dgvGrid.Columns(0).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvGrid.Columns(1).DataPropertyName = "ContactName"
dgvGrid.Columns(1).HeaderText = "Name"
dgvGrid.Columns(1).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvGrid.Columns(2).DataPropertyName = "City"
dgvGrid.Columns(2).HeaderText = "City"
dgvGrid.Columns(2).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
dgvGrid.Columns(3).DataPropertyName = "Country"
dgvGrid.Columns(3).HeaderText = "Country"
dgvGrid.Columns(3).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
BindGrid(String.Empty)
End Sub
Private Sub BindGrid(ByVal country As String)
Dim constring As String = "Data Source=.;Initial Catalog=Northwind;UID=sa;PWD=pass@123"
Using con As SqlConnection = New SqlConnection(constring)
Using cmd As SqlCommand = New SqlCommand("SELECT * FROM Customers", con)
Using dt As DataTable = New DataTable()
con.Open()
Try
dt.Load(cmd.ExecuteReader())
If Not String.IsNullOrEmpty(country) Then
Dim drs As DataRow() = dt.Select("Country='" & country & "'")
If drs.Length > 0 Then
dt = drs.CopyToDataTable()
End If
End If
Catch
End Try
con.Close()
dgvGrid.DataSource = dt
End Using
End Using
End Using
End Sub
Private Sub btnSearch_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
BindGrid(btnSearch.Text)
End Sub
Screenshot