How can I filter datatable?

Question

I am using a DataTable with user information, and I want to search for a user or list of users in this DataTable. I try, but not working :(

Here is my C # code:

public DataTable GetEntriesBySearch(string username,string location,DataTable table) { list = null; list = table; string expression; string sortOrder; expression = "Nachname = 'test'"; sortOrder = "nachname DESC"; DataRow[] rows = list.Select(expression, sortOrder); list = null; // for testing list = new DataTable(); // for testing foreach (DataRow row in rows) { list.ImportRow(row); } return list; } 

Answer 1

You can use DataView.

 DataView dv = new DataView(yourDatatable); dv.RowFilter = "query"; // query example = "id = 10" 

http://www.csharp-examples.net/dataview-rowfilter/

Answer 2

If you use at least .NET 3.5, I would suggest using Linq-To-DataTable , as it has become more readable and powerful:

 DataTable tblFiltered = table.AsEnumerable() .Where(row => row.Field<String>("Nachname") == username && row.Field<String>("Ort") == location) .OrderByDescending(row => row.Field<String>("Nachname")) .CopyToDataTable(); 

The above code is just an example; in fact, you have many more methods available .

Remember to add using System.Linq; and for the AsEnumerable extension method, a link to the System.Data.DataSetExtensions dll ( How ).

Answer 3

clear:

 list = null; // for testing list = new DataTable(); // for testing foreach (DataRow row in rows) { list.ImportRow(row); } 

using:

 .CopyToDataTable() 

Example:

 string _sqlWhere = "Nachname = 'test'"; string _sqlOrder = "Nachname DESC"; DataTable _newDataTable = yurDateTable.Select(_sqlWhere, _sqlOrder).CopyToDataTable(); 

Answer 4

For those who work in VB.NET (just in case)

 Dim dv As DataView = yourDatatable.DefaultView dv.RowFilter ="query" ' ex: "parentid = 0" 

Answer 5

It is better to use a DataView for this task.

An example of its use can be found in this post: How to filter data in a dataview

Answer 6

Sometimes you really want to return a DataTable not a DataView . So the DataView not good in my case, and I think very few people want this either. Here is what I did before

 myDataTable.select("myquery").CopyToDataTable() 

This will filter out myDataTable which is a DataTable, and will return a new DataTable

Hope someone finds this helpful

Answer 7

Hi, we can use the ToLower method, sometimes it is not a filter.

 EmployeeId = Session["EmployeeID"].ToString(); var rows = dtCrewList.AsEnumerable().Where (row => row.Field<string>("EmployeeId").ToLower()== EmployeeId.ToLower()); if (rows.Any()) { tblFiltered = rows.CopyToDataTable<DataRow>(); }