How I can search rows in a datatable with a searchstring?

Question

I want to search rows in my DataTable.

I've tried this:

 protected void imggastsuche_Click(object sender, EventArgs e) 
        {
            string searchstring = txtgastsuche.Text;

            DataTable tb = DataBaseManager.GetDataTable(mysqlconnectionstring);

            DataRow[] foundRows = tb.Select("FIRSTNAME,LASTNAME,NAME,COMPANY,TIMEFROM,TIMETO,CREATOR Like '%" + searchstring + "%'");

            tb = foundRows.CopyToDataTable();

            this.ListView.DataSource = tb;
            this.ListView.DataBind();

        }

But I have an error in my string.

What can I do if I want to search these columns?

Tim Schmelter · Accepted Answer · 2013-01-24T09:14:16.380

You get the error because the parameter to Select is the filterExpression and you have passed all columns. Understand the filterExpression as a WHERE clause in sql. You want all columns but you want to filter by just one. You get all columns anyway since they are all part of the DataTable/DataView so you don't need to list them explicitely.

You could either use the DataTable.Select, DatView.RowFilter methods or LINQ-to-DataSet:

LINQ-To-DataSet (which i prefer):

var filtered = tb.AsEnumerable()
    .Where(r => r.Field<String>("CREATOR").Contains(searchstring));

ADO.NET(DataTable.Select):

DataRow[] filteredRows = tb.Select("CREATOR LIKE '%" + searchstring + "%'");

ADO.NET(DataView.RowFilter):

 tb.DefaultView.RowFilter = "CREATOR LIKE '%" + searchstring + "%'";

If you want to search for this string in any column instead:

DataRow[] filteredRows = tb.Select("FIRSTNAME LIKE '%" + searchstring + "%' OR LASTNAME LIKE '%" + searchstring + "%' OR NAME LIKE '%" + searchstring + "%' OR COMPANY LIKE '%" + searchstring + "%' OR CREATOR LIKE '%" + searchstring + "%'");

The same with Linq:

var filtered = tb.AsEnumerable()
    .Where(r => r.Field<String>("FIRSTNAME").Contains(searchstring)
           ||   r.Field<String>("LASTNAME").Contains(searchstring))
           ||   r.Field<String>("NAME").Contains(searchstring)
           ||   r.Field<String>("COMPANY").Contains(searchstring)
           ||   r.Field<String>("CREATOR").Contains(searchstring));

@Tarasov: You want all columns but you want to filter by one, you get all columns anyway since they are all part of the `DataTable`/`DataView`. — Tim Schmelter, Jan 24 '13 at 09:07
I want to search every column to this searchstring. I thought I can dothis :( — Tarasov, Jan 24 '13 at 09:08

score 9 · Answer 2 · answered Sep 07 '16 at 22:35

9

If some one else needs return specifically a DataTable you can use the code below:

DataTable dtResult= tb.Select("CREATOR LIKE '%"+searchstring+"%'").CopyToDataTable();

answered Sep 07 '16 at 22:35

ejhn5

105
3
8

Tom Stein · Answer 3 · 2019-10-18T17:43:09.770

I just made a extension method to the DataTable class for this. It returns a new Datatable containing only the rows you want.

public static DataTable SearchInAllColums(this DataTable table, string keyword, StringComparison comparison)
{
    if(keyword.Equals(""))
    {
        return table;
    }
    DataRow[] filteredRows = table.Rows
           .Cast<DataRow>()
           .Where(r => r.ItemArray.Any(
           c => c.ToString().IndexOf(keyword, comparison) >= 0))
           .ToArray();

    if (filteredRows.Length == 0)
    {
        DataTable dtTemp = table.Clone();
        dtTemp.Clear();
        return dtTemp ;
    }
    else
    {
        return filteredRows.CopyToDataTable();
    }
}

Usage:

DataTable dataTable = getData();
dataTable.SearchInAllColums(Keyword, StringComparison.OrdinalIgnoreCase);

score 0 · Answer 4 · answered Jan 14 '17 at 08:09

you could build the query you are going to use in the select.

            if(TextBoxCusName.Text != "")
            {
                query = "CustomerName LIKE '%" + TextBoxCusName.Text.Trim()+"%' AND ";
            }
            if(TextBoxCusContact.Text != "")
            {
                query = query + "CustomerNo LIKE '%" + TextBoxCusContact.Text.Trim() + "%' AND ";
            }
            if(TextBoxVehicleNo.Text != "")
            {
                query = query + "VehicleNo LIKE '%" + TextBoxVehicleNo.Text.Trim()+"%'";
            }
            if(query.EndsWith("AND "))
            {
                query = query.Remove(query.Length - 4);
            }
            DataRow[] result = dataCustomerAndVehicle.Select(query);

this equivalent to

select * from dataCustomerAndVehicle where CustomerName LIKE '%...%' AND ...

score 0 · Answer 5 · edited May 06 '20 at 17:02

Static method for search any column

public static DataTable SearchInAllColums(DataTable table, string keyword)
{
    StringComparison comparison = StringComparison.OrdinalIgnoreCase;

    if (keyword.Equals(""))
    {
        return table;
    }

    DataRow[] filteredRows = table.Rows
           .Cast<DataRow>()
           .Where(r => r.ItemArray.Any(
           c => c.ToString().IndexOf(keyword, comparison) >= 0))
           .ToArray();

    if (filteredRows.Length == 0)
    {
        DataTable dtTemp = table.Clone();
        dtTemp.Clear();
        return dtTemp;
    }
    else
    {
        return filteredRows.CopyToDataTable();
    }
}

How I can search rows in a datatable with a searchstring?

5 Answers5

Linked