How I can filter a Datatable?

Question

I use a DataTable with Information about Users and I want search a user or a list of users in this DataTable. I try it butit don't work :(

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; 
        }

Can you elaborate on what exactly doesn't work? Do you get an exception or do you get 0 results (2 different things)? — James, Oct 22 '12 at 13:40

score 144 · Accepted Answer · answered Oct 22 '12 at 13:39

144

You can use DataView.

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

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

answered Oct 22 '12 at 13:39

Kadir

3,094
4
37
57

exactly what I needed! thanks for the example link! – Richard Varno Oct 03 '13 at 22:05

score 136 · Answer 2 · edited May 23 '17 at 11:33

136

If you're using at least .NET 3.5, i would suggest to use Linq-To-DataTable instead since it's much 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();

Above code is just an example, actually you have many more methods available.

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

edited May 23 '17 at 11:33

Community

1
1

answered Oct 22 '12 at 13:38

Tim Schmelter

450,073
74
686
939

4

Note of caution - CopyToDataTable() copies the datarows. This is not a clone operation, so some of the properties from the original table are not copied across e.g. table name – Malcolm Swaine Oct 20 '16 at 10:28
1

@Tim: What about the performance point of view of this code? Is it better to store in some object list and then apply linq/lambda? – Rajesh Mishra Apr 19 '17 at 06:39
2

@RajeshMishra: if you already have a filled `DataTable` keep it. Otherwise i would prefer a `List` because you have no boxing/unboxing. A `DataTable` uses `System.Object` for everything so you always have to cast. Having a custom class with meaningful properties has also many more advantages apart from readability. – Tim Schmelter Apr 19 '17 at 07:40
1

Note that you may get the error `The source contains no DataRows` which can be avoided like this: https://stackoverflow.com/questions/28324740/the-source-contains-no-datarows#28324802 – SharpC Feb 04 '21 at 14:25
@SharpC: from a performance point of view it would be better to use `try...catch` and handle the `InvalidOperationException` instead of using `if(rows.Any())...` because you execute the query twice otherwise. – Tim Schmelter Jul 27 '22 at 08:20

VolkanCetinkaya · Answer 3 · 2021-02-19T15:18:34.247

21

use it:

.CopyToDataTable()

example:

string _sqlWhere = "Nachname = 'test'";
string _sqlOrder = "Nachname DESC";

DataTable _newDataTable = yurDateTable.Select(_sqlWhere, _sqlOrder).CopyToDataTable();

edited Feb 19 '21 at 15:18

answered Mar 13 '14 at 10:53

VolkanCetinkaya

645
7
13

2

Can you explain with a bit more detail why this is an answer to the question asked? – Adrian Wragg Mar 13 '14 at 11:33

score 12 · Answer 4 · edited May 13 '19 at 08:29

12

Sometimes you actually want to return a DataTable than a DataView. So a DataView was not good in my case and I guess few others would want that too. Here is what I used to do

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

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

Hope someone will find that is useful

edited May 13 '19 at 08:29

kara

3,205
4
20
34

answered May 03 '18 at 23:30

Dilaksha A

279
3
4

1

This will fail if the select query returned no results. – Rayanth Oct 22 '20 at 00:31

score 10 · Answer 5 · edited Dec 11 '15 at 15:01

10

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

Dim dv As DataView = yourDatatable.DefaultView

dv.RowFilter ="query" ' ex: "parentid = 0"

edited Dec 11 '15 at 15:01

bluish

26,356
27
122
180

answered Mar 21 '15 at 03:37

nghiavt

423
5
14

score 5 · Answer 6 · edited May 23 '17 at 12:10

5

It is better to use DataView for this task.

Example of the using it you can find in this post: How to filter data in dataview

edited May 23 '17 at 12:10

Community

1
1

answered Oct 22 '12 at 13:38

Maxim Kornilov

5,675
1
24
35

score 2 · Answer 7 · edited Jan 30 '18 at 12:41

2

Hi we can use ToLower Method sometimes it is not 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>();
   }

edited Jan 30 '18 at 12:41

Hamza Zafeer

2,360
13
30
42

answered Jan 30 '18 at 12:01

Santosh Kumar

69
1
11

How I can filter a Datatable?

7 Answers7

Linked

Related