Filtering Data Table in .Net C#

Many time you need to filter the data table so select some specific row. You might have tried lot of methods to implement this. Today I am going to explain two basic methods through which you can filter the data table and use it in your code. The most commonly used method is to apply SELECT method on the data table which returns Data Row collection and then you again have to added these rows in the new data table using some loops or other methods which seems some tedious task for the developers. There are many other methods available through which you can filter the datatable and it will return data table only which you can directly use to bind the controls. Lets first understand the basic filtration method.

Lets create a simple data table having 3 columns.

DataTable dtTestTable = new DataTable();
dtTestTable.Columns.Add(New DataColumn("ID"));
dtTestTable.Columns.Add(New DataColumn("Name"));
dtTestTable.Columns.Add(New DataColumn("Address"));

Lets add some data into this data table so that we can understand the article in more convenient way.

DataRow dr;
dr =dtTestTable.NewRow();
dr[0] = "1";
dr[1] = "Dharmendra";
dr[2] ="Noida";
dtTestTable.Rows.Add(dr);

dr =dtTestTable.NewRow();
dr[0] = "2";
dr[1] = "Amit";
dr[2] ="Noida";
dtTestTable.Rows.Add(dr);

dr =dtTestTable.NewRow();
dr[0] = "3";
dr[1] = "Ashish";
dr[2] ="Noida";
dtTestTable.Rows.Add(dr);

dr =dtTestTable.NewRow();
dr[0] = "4";
dr[1] = "Prakash";
dr[2] ="Delhi";
dtTestTable.Rows.Add(dr);

dr =dtTestTable.NewRow();
dr[0] = "5";
dr[1] = "Rana";
dr[2] ="Basti";
dtTestTable.Rows.Add(dr);

1. Using 'Select' Method 

If we go to the MSDN for select method of the data table then we have 4 overloaded method for the select. The framework 1.1 have only one select method and it does not take any parameters but from framework 2.0 onward we have 4 overloaded method to filter the data table using Select method. You can find more details on these methods on below link :

 http://msdn.microsoft.com/en-us/library/system.data.datatable.select(v=vs.110).aspx

Lets take an simple example to filter a data table which is created above. If I want to get the persons who lives in Delhi then we can use the query in the select method as "Address = Delhi". Lets see the below example :

    string expression;
    expression = "Address = Delhi";
    DataRow[] foundRows;
    // Use the Select method to find all rows matching the filter.
    foundRows = dtTestTable.Select(expression);

Now in our case foundRows collection variable will contain only one rows as in Data Table we have only one person who lives in Delhi. Now you can iterate the foundRows variable and get the values.

1. Using 'Lambda Expressions' 
Lambda expressions are very useful for playing with collections in .Net framework. Lambda expressions made developer life very easy . With writing lot codes for sorting collection, avoiding lot of for / foreach loops, developers can easily manipulate collections and get the desired results. Lambda Expressions available from visual studio 2008 onward version. For more information on Lambda Expressions visit http://msdn.microsoft.com/en-us/library/bb397687(v=vs.90).aspx .

Lets try the above filtration using lambda expression which was done using 'select' method.

string searchString="Delhi";
 DataTable dtFiltered = dtTestTable.AsEnumerable().Where(m => m.Field("Address")==searchString).CopyToDataTable();

Here we are converting the Data Table to Enumerable type on which we can play with Lambda Expressions and after applying the filter we are copying the result in the Data Table and we can take the result of this expression in the new DataTable or in the existing table.

Let me know if you not able to implement the above functionality or you got any difficulties. You can mail me your queries either to my email address info@dkumar.co.in or leave your comment here.