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?

Answer 1

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