How to Filter Datatable with multiple column values using C#

Question

I am having 5 controls on my web page like txtFirstName(TextBox), txtDisplayName(TextBox), txtFromDate (TextBox), txtToDate(TextBox) and btnFilter(button). Also I am binding some data on GridView same page by using DataTable.

Now I want to filter the DataTable by using these search parameters controls which I have mentioned in the above.

Filter Condition is,

We are not sure about the filled controls and empty controls. So based on the input entered on the controls we need to filter the DataTable.If the control values are empty we should not allow them to filter Datatable.
We need to filter the data values by LIKE operator or String.Startswith()
If txtFromDate and txtToDate have filled then we need to filter the data values between the range.
If all controls are filled then we need to filter the datatable based on all filter parameters wit the date range.

How do we achieve this filtering? Please help me to implement this successfully. Can we do this by LINQ or Lamda expression? If so, tell me the suggestions.

I have tried two ways but its not working ,

First way:

var test=dtActions.AsEnumerable().Where(z=>
    !string.IsNullOrEmpty(txtFirstName.Text)? z.Field<string>("FirstName").ToUpper().StartsWith(txtFirstName.Text.ToUpper()) &&
    !string.IsNullOrEmpty(txtDisplayName.Text)?z.Field<string>("DisplayName").ToUpper().StartsWith(txtDisplayName.Text.ToUpper()) &&
    !string.IsNullOrEmpty(txtCreatedBy.Text)?z.Field<string>("CreatedBy").ToUpper().StartsWith(txtCreatedBy.Text.ToUpper());

I dont have idea to get the data between the DateTime textboxes.

Second way:

var results = from dataRow in dtActions.AsEnumerable()
    where dataRow.Field<string>("FirstName").ToUpper().StartsWith(txtFirstName.Text.ToUpper()) ||
        dataRow.Field<string>("DisplayName").ToUpper().StartsWith(txtFirstName.Text.ToUpper()) ||  
        ((dataRow.Field<DateTime>("CreatedDate")>= Convert.ToDateTime(txtFromDate.Text)) && (dataRow.Field<DateTime>("CreatedDate")<= Convert.ToDateTime(txtToDate.Text)))
    select dataRow;

please post the code of what you have tried. That way, we can check it for mistakes, and offer alternate solutions — Russell Uhl, Jun 12 '14 at 12:43
Use something like `dt.AsEnumerable().Where(x=> x["id" ==1).ToList()` — kostas ch., Jun 12 '14 at 12:45
@kostasch. We need to check textbox is empty or not and will filter the datatable. That too with multiple textbox control values. — Suryakavitha, Jun 12 '14 at 12:51
Rather than filtering after getting the data, why not filter it using the parameters being passed into the stored procedure? I.E. where param = null or myfield = param. — Kevin Cook, Jun 12 '14 at 13:02
Check with `dt.AsEnumerable().Where(x=> (!string.IsNullOrEmpty(txtSkata) ? x.description.Contains(txtSkata.Trim()) : "0" == "0")).ToList()` — kostas ch., Jun 12 '14 at 13:05
@KevinCook: How can we do this by SQL procedure itself? Any Suggestion? — Suryakavitha, Jun 12 '14 at 13:12
Linq answer found here: [How I can filter a dataTable with Linq to datatable?](http://stackoverflow.com/q/19449449/1316573) — Daniel, Jun 12 '14 at 13:27
each filter control data would be passed as a sqlparameter to the stored procedure (or null if the control is empty) and then in the where clause you would check whether the parameter was null or the column data matched the filter data. — Kevin Cook, Jun 12 '14 at 15:09

Durgesh Chaudhary · Accepted Answer · 2014-06-12T18:32:33.687

Following code works :

var test = dtActions.AsEnumerable().Where(z =>
    (string.IsNullOrEmpty(txtFirstName.Text) ? true : z.Field<string>("FirstName").ToUpper().StartsWith(txtFirstName.Text.ToUpper())) &&
    (string.IsNullOrEmpty(txtDisplayName.Text) ? true : z.Field<string>("DisplayName").ToUpper().StartsWith(txtDisplayName.Text.ToUpper())) &&
    (string.IsNullOrEmpty(txtCreatedBy.Text) ? true : z.Field<string>("CreatedBy").ToUpper().StartsWith(txtCreatedBy.Text.ToUpper()))
);

Your Second Query

var results = from dataRow in dtActions.AsEnumerable()
        where (string.IsNullOrEmpty(txtFirstName.Text) ? true : dataRow.Field<string>("FirstName").ToUpper().StartsWith(txtFirstName.Text.ToUpper())) ||
            (string.IsNullOrEmpty(txtDisplayName.Text) ? true : dataRow.Field<string>("DisplayName").ToUpper().StartsWith(txtDisplayName.Text.ToUpper())) ||
            (string.IsNullOrEmpty(txtCreatedBy.Text) ? true : dataRow.Field<string>("CreatedBy").ToUpper().StartsWith(txtCreatedBy.Text.ToUpper())) ||
            (dataRow.Field<DateTime>("CreatedDate") >= Convert.ToDateTime(txtFromDate.Text) && (dataRow.Field<DateTime>("CreatedDate") <= Convert.ToDateTime(txtToDate.Text)))
        select dataRow;

The reason your code is not working is incorrect conditional operator. If string is null or empty then we return true else we check the field value.

Apply ToList() if you want a List in end or ToArray() if you want it as an array.

How to convert it back to datatable again – Developer Feb 04 '22 at 16:41 — Developer, Feb 04 '22 at 16:41

score 1 · Answer 2 · answered Jun 12 '14 at 18:56

1

did you check dataview filters? http://msdn.microsoft.com/en-us/library/system.data.dataview.rowfilter.aspx

answered Jun 12 '14 at 18:56

Furkan YILDIRIM

17
4

How to Filter Datatable with multiple column values using C#

2 Answers2