C# DataTable Filter

Introduction to C# DataTable Filter

C# DataTable is a central object used to access most of the objects and data related to the data table. Since the data table comprises data in huge amounts and is not in an organized format, there comes the need to apply a filter. To satisfy the filtering properties in DataTable related to C#, it is needed to get the filter to arrange and order data streamlined C# database filter.

Syntax:

There is no specific syntax for C# DataTable filter, but still, it makes use of the filter functions associated with columns which are represented as follows:

dataView.RowFilter = "s_id=180";

DataTable filter function associated with literals is represented as follows:

dataView.RowFilter = "s_name = 'anu'"

DataTable filter function associated with number values is represented as follows:

dataView.RowFilter = "dt_of_brth = 1987"

How to Filter DataTable in C#?

Filter function in C# is mostly used whenever the data and its associated operations are huge in number. If the data present in the DataTable gets increasing, then the only savior with respect to rows and columns filtering is the filter in DataTable.

Let’s check the working pattern to filter DataTable in C#:

• Filtering DataTable in C# is not unique and different from other types of filtering technique; still, it can be achieved in varieties of ways.
• Filtering DataTable varieties of ways include select(String) method, which selects the required row or column and then based on that applies the filter.
• Filtering can be done using Select, Where, AND, OR, NOT logical operator and on top of it applying the value also there.
• Data Rows and columns present in the data table also make use of the sorting method, which sorts and orders the data in an Ascending or Descending format as necessary.
• Selecting the string as enumerable is useful while saving any object, and then applying filter and order operation based on the computation helps in providing the desired result.
• Evaluation of DataTable with its associated string also needs to be taken care of with respect to the true or false return function.

Examples of C# DataTable Filter

Given below are the examples of C# DataTable Filter:

Example #1

This program demonstrates the filtering and fetching of row data by using select statement as filter statement as for each with AND, OR and NOT condition and returns any number greater than the mentioned number but is less than the other upper limit as shown in the output.

Code:

using System;
using System.Data;
using System.Xml;
using System.Collections.Generic;
using System.Linq;
using System.Data.DataSetExtensions;
public class Data_tbl_Demo
{
public static void Main()
{
DataTable tbl_1 = new DataTable("Creation of Data for players");
tbl_1.Columns.Add(new DataColumn("Size_of_team", typeof(int)));
tbl_1.Columns.Add(new DataColumn("Team_work", typeof(char)));
tbl_1.Rows.Add(50, 'c');
tbl_1.Rows.Add(100, 'c');
tbl_1.Rows.Add(250, 'd');
tbl_1.Rows.Add(567, 'd');
tbl_1.Rows.Add(123, 'd');
DataRow[] rslt = tbl_1.Select("Size_of_team >= 123 AND Team_work = 'd'");
foreach (DataRow row in rslt)
{
Console.WriteLine("{0}, {1}", row[0], row[1]);
}
}
}

Output:

Example #2

This program is used to demonstrate the DataTable filtering expression, which is used to return an array of DataRow Objects after sorting in Descending order as shown in the output.

Code:

using System;
using System.Data;
using System.Xml;
using System.Collections.Generic;
using System.Linq;
using System.Data.DataSetExtensions;
public class Data_tbl_Demo
{
public static void Main()
{
DataTable tbl2_2 = new DataTable("Orders_plcd");
tbl2_2.Columns.Add("Order_ID", typeof(Int32));
tbl2_2.Columns.Add("Order_Quantity", typeof(Int32));
tbl2_2.Columns.Add("Company_Name", typeof(string));
tbl2_2.Columns.Add("Date_on_day", typeof(DateTime));
DataRow nw_row = tbl2_2.NewRow();
nw_row["Order_ID"] = 1;
nw_row["Order_Quantity"] = 5;
nw_row["Company_Name"] = "New_Company_Nm";
nw_row["Date_on_day"] = "2014, 5, 25";
tbl2_2.Rows.Add(nw_row);
DataRow nw_row2 = tbl2_2.NewRow();
nw_row2["Order_ID"] = 2;
nw_row2["Order_Quantity"] = 6;
nw_row2["Company_Name"] = "New_Company_Nm2";
tbl2_2.Rows.Add(nw_row2);
DataRow nw_row3 = tbl2_2.NewRow();
nw_row3["Order_ID"] = 3;
nw_row3["Order_Quantity"] = 8;
nw_row3["Company_Name"] = "New_Company_Nm3";
tbl2_2.Rows.Add(nw_row3);
string exprsn = "Date_on_day = '5/25/2014' or Order_ID = 2";
string sort_Order = "Company_Name DESC";
DataRow[] sorted_Rows;
sorted_Rows = tbl2_2.Select(exprsn, sort_Order);
for (int i = 0; i < sorted_Rows.Length; i++)
Console.WriteLine(sorted_Rows[i][2]);
}
}

Output:

Example #3

This program demonstrates the select query where the DataTable finds for the two matching rows which have dates on the more recent format and is filtered using DateTime as shown in the output.

Code:

using System;
using System.Data;
using System.Xml;
using System.Collections.Generic;
using System.Linq;
using System.Data.DataSetExtensions;
public class Using_Date_Time
{
public static void Main()
{
DataTable tbl_dt_time = new DataTable("Widgets");
tbl_dt_time.Columns.Add(new DataColumn("rw_ID", typeof(int)));
tbl_dt_time.Columns.Add(new DataColumn("Date", typeof(DateTime)));
tbl_dt_time.Rows.Add(180, new DateTime(2003, 1, 1));
tbl_dt_time.Rows.Add(123, new DateTime(2000,1, 1));
tbl_dt_time.Rows.Add(350, new DateTime(2001,1, 1));
DataRow[] filterd_result = tbl_dt_time.Select("Date > #6/1/2001#");
foreach (DataRow row in filterd_result)
{
Console.WriteLine(row["rw_ID"]);
}
}
}

Output:

Example #4

This program illustrates an invalid expression by selecting a value like A that does not gets evaluated to true or false and throws a nasty error which is not desired.

Code:

using System;
using System.Data;
using System.Xml;
using System.Collections.Generic;
using System.Linq;
using System.Data.DataSetExtensions;
public class Using_Date_Time
{
public static void Main()
{
DataTable table = new DataTable();
table.Columns.Add("Anusua", typeof(int));
table.Rows.Add(1);
table.Rows.Add(2);
table.Rows.Add(3);
table.Rows.Add(4);
table.Rows.Add(5);
DataRow[] rows = table.Select("Anusua");
System.Console.WriteLine(rows.Length);
}
}

Output:

using System;
using System.Data;
using System.Xml;
using System.Collections.Generic;
using System.Linq;
using System.Data.DataSetExtensions;
public class Program
{
public static void Main()
{
DataTable dt_4 = new DataTable();
dt_4.Columns.Add("emp_Id",typeof(int));
dt_4.Columns.Add("customer_Name",typeof(string));
dt_4.Columns.Add("Amount_type",typeof(decimal));
dt_4.Rows.Add(1,"A",50);
dt_4.Rows.Add(2,"b",68);
dt_4.Rows.Add(3,"c",22);
dt_4.Rows.Add(4,"d",null);
decimal dec_ml = 0;
object sum_Obj;
sum_Obj = dt_4.Compute("Sum(Amount_type)", string.Empty);
decimal total = dt_4.AsEnumerable().Where(r => !r.IsNull("Amount_type") && decimal.TryParse(r["Amount_type"].ToString(), out dec_ml)).Sum(r => dec_ml);
Console.WriteLine(sum_Obj);
Console.WriteLine(total);
}
}