ASP.NET – DataTable Filter by Column Value

ASP.NET DataSet Filter - Date Filter Output Snapshot 03

ASP.NET DataTable Filter by Column Value :

Filtering Data of ASP.NET DataTable can avoid round trips to your database multiple times and also increases your Web application’s response time. Filtering the values of ASP.NET DataTable can be achieved using RowFilter property where in we can make use of “where” clause to filter our DataTable based on Column values. Filtering can also  be done for multiple column values and also for Date values using the ‘between’ keyword. Below are the Syntax and usage of the Syntax respectively. ASP.NET datatable filter :

My Database table :  

ASP.NET DataTable Filter - Database Snapshot 01

Syntax for filtering ASP.NET DataTable using one column value :

DataView dv = dataTableId.DefaultView;
dv.RowFilter = "ColumnName ='YourFilteringData'";
dataGridId.DataSource = dv;
dataGridId.DataBind();

Usage :

protected void Page_Load(object sender, EventArgs e)
{
FillGrid();
}

public void FillGrid()
{
SqlConnection con = new SqlConnection("Data Source=192.168.0.192;Initial Catalog=ParallelCodes;User       ID=sa;Password=789");
SqlCommand cmd = new SqlCommand("Select * from tblCountries", con);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
DataTable dtCountries = new DataTable();
sqlAdapter.Fill(dtCountries);

DataView dv = dtCountries.DefaultView;
dv.RowFilter = "Country ='Chile'";
dataGrid.DataSource = dv;
dataGrid.DataBind();

sqlAdapter.Dispose();
cmd.Dispose();
con.Close();
}

Output :

ASP.NET DataTable Filter - Output Snapshot 01

ASP.NET DataTable Filter – Output Snapshot 01

 

Syntax for filtering ASP.NET DataTable using multiple column values :

DataView dv = dataTableId.DefaultView;
dv.RowFilter = "FirstColumnName ='YourFilteringData' and SecondColumnName ='YourFilteringData' ";
dataGridId.DataSource = dv;
dataGridId.DataBind();

Usage :

protected void Page_Load(object sender, EventArgs e)
{
FillGrid();
}

public void FillGrid()
{
SqlConnection con = new SqlConnection("Data Source=192.168.0.192;Initial Catalog=ParallelCodes;User       ID=sa;Password=789");
SqlCommand cmd = new SqlCommand("Select * from tblCountries", con);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
DataTable dtCountries = new DataTable();
sqlAdapter.Fill(dtCountries);

DataView dv = dtCountries.DefaultView;
dv.RowFilter = "Country ='India' and Id='1'";
dataGrid.DataSource = dv;
dataGrid.DataBind();

sqlAdapter.Dispose();
cmd.Dispose();
con.Close();
}

Output :

ASP.NET DataTable Filter - Multiple Column Output Snapshot 2

ASP.NET DataTable Filter – Multiple Column Output Snapshot 2

Syntax for filtering ASP.NET DataTable using Date value :

DataView dv = dataTableId.DefaultView;
dv.RowFilter = "DateColumnName  >='FromDate' and DateColumnName  <='ToDate'";
dataGridId.DataSource = dv;
dataGridId.DataBind();

Usage :

protected void Page_Load(object sender, EventArgs e)
{
FillGrid();
}

public void FillGrid()
{
SqlConnection con = new SqlConnection("Data Source=192.168.0.192;Initial Catalog=ParallelCodes;User         ID=sa;Password=789");
SqlCommand cmd = new SqlCommand("Select * from tblCountries", con);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
DataTable dtCountries = new DataTable();
sqlAdapter.Fill(dtCountries);

DataView dv = dtCountries.DefaultView;
dv.RowFilter = "OnDate >= '2018-02-01 00:00:00.000' and OnDate <='2018-02-05 23:59:59.000'";
dataGrid.DataSource = dv;
dataGrid.DataBind();

sqlAdapter.Dispose();
cmd.Dispose();
con.Close();
}

Output :

ASP.NET DataTable Filter - Date Filter Output Snapshot 03

ASP.NET DataTable Filter – Date Filter Output Snapshot 03

Also see :
ASP.NET DataTable filter by Date values – Filtering data of DataTable in ASP.NET using dates.
ASP.NET DataTable filter using Multiple Columns – Filtering data of DataTable in ASP.NET.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.