How to Filter a DataTable in C#
-
Use the
DataView.RowFilterProperty to Filter theDataTableinC# -
Use the
CopyToDataTable()Method to Filter theDataTableinC# -
Use the
Select()Method to Filter theDataTableinC#
The .NET Framework’s DataView represents a databindtable for filtering, which represents a customized view of a DataTable in C#. The DataTable (the DataView is synched with or connected to) contains data that requires filtering.
A DataView cannot store data and only represents data of its corresponding DataTable. Any calculations performed on a DataView result in affecting the DataTable.
Furthermore, changing or modifying the data of a DataTable will affect all of its associated or connected DataViews.
The DataView class exists in the System.Data namespace in C#. This tutorial will teach you the three primary methods to filter a DataTable in C#.
The DataView.RowFilter property, CopytoDataTable() and Select() methods are some primary ways to filter a DataTable in C#. The filtering is a common requirement for every C# application because data is becoming larger and requires high-level manipulation.
Use the DataView.RowFilter Property to Filter the DataTable in C#
The DataView.RowFilter property is extremely productive when filtering a DataTable in C#. It allows you to get or set the data associated with a DataView and helps you define the individual DataView objects that can filter the data in each DataTable.
The RowFilter property represents a string that specifies how rows are to be filtered, and that string’s value data type is System.String. You can assign a RowFilter value as username = "aUsername" and filter the rows of DataView to display filtered data programmatically in a C# application.
The most interesting thing about the RowFilter property is its ability to change/filter data of underlying DataTable, of which a DataView provides a dynamic view. The dynamic and data binding capabilities of a DataView make it ideal for data-binding C# applications.
Similar to the view provided by a DataTable, a DataView in C# can dynamically represent a single data set, enabling you to perform different filters. You can apply the RowFilter property to a DataView, which will hide all the unnecessary data of its associated DataTable object’s rows collection that doesn’t match the filter expression or criteria.
using System;
using System.Data;
using System.Windows.Forms;
namespace DataTableExp {
public partial class Form1 : Form {
private DataSet dtSet;
public Form1() {
InitializeComponent();
CreateTable();
}
private void CreateTable() {
// Create a new DataTable.
DataTable filterExpCust = new DataTable("FilterUserDataTable");
DataColumn filterExpColumn;
DataRow filterExpDRow;
// create an `id` column
filterExpColumn = new DataColumn();
// define the column's type
filterExpColumn.DataType = typeof(Int32);
filterExpColumn.ColumnName = "id"; // column name
filterExpColumn.Caption = "User Account `ID`";
filterExpColumn.ReadOnly = false;
filterExpColumn.Unique = true;
// Add column to the DataColumnCollection.
filterExpCust.Columns.Add(filterExpColumn);
// create a `name` column
filterExpColumn = new DataColumn();
filterExpColumn.DataType = typeof(String);
filterExpColumn.ColumnName = "Name"; // column name
filterExpColumn.Caption = "User Account `Name`";
filterExpColumn.AutoIncrement = false;
filterExpColumn.ReadOnly = false;
filterExpColumn.Unique = false;
// Add column to the DataColumnCollection.
filterExpCust.Columns.Add(filterExpColumn);
// create an `address` column
filterExpColumn = new DataColumn();
filterExpColumn.DataType = typeof(String);
filterExpColumn.ColumnName = "Address"; // column name
filterExpColumn.Caption = "User Account Address";
filterExpColumn.ReadOnly = false;
filterExpColumn.Unique = false;
// add colume to the `filterExpColumn`
filterExpCust.Columns.Add(filterExpColumn);
// make the `id` the primary key of the table
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = filterExpCust.Columns["id"];
filterExpCust.PrimaryKey = PrimaryKeyColumns;
// create a new DataSet
dtSet = new DataSet();
// add `FilterUserDataTable` table to the DataSet.
dtSet.Tables.Add(filterExpCust);
// add data rows to the `FilterUserDataTable` table using the `NewRow` method
// first user details
filterExpDRow = filterExpCust.NewRow();
filterExpDRow["id"] = 1001;
filterExpDRow["Name"] = "Stephan Hawking";
filterExpDRow["Address"] = "79 Gulberg Road, Havana, Cuba";
filterExpCust.Rows.Add(filterExpDRow);
// second user details
filterExpDRow = filterExpCust.NewRow();
filterExpDRow["id"] = 1002;
filterExpDRow["name"] = "John Snow";
filterExpDRow["Address"] = " The Kings Landing, North California";
filterExpCust.Rows.Add(filterExpDRow);
// third user details
filterExpDRow = filterExpCust.NewRow();
filterExpDRow["id"] = 1003;
filterExpDRow["Name"] = "Scooby Doo";
filterExpDRow["Address"] = "194 St. Patrick Avenue, London, UK";
filterExpCust.Rows.Add(filterExpDRow);
}
private void button2_Click(object sender, EventArgs e) {
// Create a BindingSource
BindingSource bs = new BindingSource();
bs.DataSource = dtSet.Tables["FilterUserDataTable"];
// Bind data to DataGridView.DataSource
dataGridView1.DataSource = bs;
}
private void button1_Click(object sender, EventArgs e) {
// filter the datatable
// create a customers table and access it into a `dv` DataView
DataView dv = new DataView(dtSet.Tables["FilterUserDataTable"]);
// it will filter the row where `id` is `1001`
dv.RowFilter = "id = 1001";
// output the filtered datatable in a `dataGridView1` data grid view
dataGridView1.DataSource = dv;
}
}
}
Output:
*click `button2` to bind data to a data grid view*
*click `button2` to filter datatable*
id Name Address
1001 Stephan Hawking 79 Gulberg Road, Havana, Cuba
The CreateTable() method creates a FilterUserDataTable datatable which contains the id, name, and address columns. The button2 button can bind the data of this DataTable to dataGridView1.
Press button1 to filter the DataTable and show the filtered results in the dataGridView1.
Use the CopyToDataTable() Method to Filter the DataTable in C#
You can filter a DataTable and return the results in a new DataTable rather than a DataView. A new DataTable, as a result, contains copies of a DataRow object, given an input IEnumberable<T> object.
The source IEnumerable<T> of the DataTable cannot be null; otherwise, the new DataTable cannot be created. It is based on a query to select a DataTable and copy its content to a new one bound to BindingSource, which acts as a proxy for DataGridView.
private void button1_Click(object sender, EventArgs e) {
DataTable _newFilteredDataTable =
dtSet.Tables["FilterUserDataTable"].Select("id = 1001").CopyToDataTable();
dataGridView1.DataSource = _newFilteredDataTable;
}
Output:
*click `button2` to bind data to a data grid view*
*click `button2` to filter datatable*
id Name Address
1001 Stephan Hawking 79 Gulberg Road, Havana, Cuba
The _newFilteredDataTable will hold the filtered data of FilterUserDataTable DataTable where id = 1001. This C# code is executable in the previous C# example by pasting this button1_Click event code.
Use the Select() Method to Filter the DataTable in C#
The Select() method projects each element of a sequence of a DataTable into a new form. As it belongs to the System.Linq namespace, it is a piece of great news for developers who enjoy using LINQ to retrieve filtered data from a DataTable in C#.
This method utilizes an object as a return value and stores all the information required to perform the action. It gets the array of data of DataRow objects using one string argument, which is the condition in this case.
You can use the Select() method with the condition as the parameter to query the DataTable, and after obtaining the result, you can iterate through it and print the required fields. Assign filter rows to an array or DataTable using the DataTable.Select() expression.
In other words, the Select() method of a DataTable accepts a filter, sorts the arguments in return, and transfers them to an array of DataRow objects. The DataRow objects that contain the filtered DataTable rows confirm the criteria in a FilterExpression.
// `dataTable` is the targeted DataTable in a C# program which contains data
// use `Select("id > 30")` to filter the data from `dataTable` where `id` is greater than `30`
// the `filterResult` will contain the filtered information of the `dataTable`
DataRow[] filterResult = dataTable.Select("id > 30");
foreach (DataRow filterRow in filterResult) {
Console.WriteLine("ID: {0} Name: {1} Address: {2}", filterRow[0], filterRow[1], filterRow[2]);
}
Output:
ID: 1003, Name: Scooby Doo, Address: 194 St. Patrick Avenue, London, UK
When dealing with a huge amount of data in C#, DataTable plays a pivotal role. This tutorial has taught you three different methods to filter a DataTable in C#.
The methods of filtering a DataTable should be optimized and efficient in fetching and manipulating data from a DataTable.
Hassan is a Software Engineer with a well-developed set of programming skills. He uses his knowledge and writing capabilities to produce interesting-to-read technical articles.
GitHub