Query a datatable where text contains

Question

Building on this question: how to run query on dataset?

I'm trying to query a datatable from my dataset where the text contains a string, similar to the String.Contains method or the sql LIKE operator.

Here's what I've tried so far:

    private void textBox1_TextChanged(object sender, EventArgs e)
    {
        DataTable tbl = globals.UserDataSet.Tables[0];
        DataRow[] tempDataRows = tbl.Select("USER_ID Like " + textBox1.Text + " OR THE_NAME Like " + textBox1.Text);
    }

This gives a System.Data.SyntaxError error.

Is it possible to query a datatable for text containing a substring? Is there a better way of doing this?

@Kevin I like that idea, can you please refer me to some documentation for using LINQ to search within a datatable? — Alex, Dec 18 '14 at 18:19

score 12 · Accepted Answer · edited May 23 '17 at 10:29

Couple of things:

First you need to enclose your values in single quote. (that is there in the linked question)
Second, if you are trying to compare for Contains then your values should have % (Just like SQL)

so your statement should be:

DataRow[] tempDataRows = tbl.Select("USER_ID Like '%" + textBox1.Text + "%' OR THE_NAME Like '%" + textBox1.Text +"%');

You can also use LINQ to DataSet/DataTable to filter your results like:

var query = tbl.AsEnumerable()
    .Where(r => r.Field<string>("USER_ID").Contains(textBox1.Text) &&
                r.Field<string>("THE_NAME").Contains(textBox1.Text));

I just changed a LINQ query on a list of objects to a datatable `.select` as above and picked up a ton of speed — MX313, Mar 29 '23 at 11:42

score 5 · Answer 2 · answered Dec 18 '14 at 18:12

5

You need to add single quotes around your strings:

DataRow[] tempDataRows = tbl.Select("USER_ID Like '" + textBox1.Text + "' OR THE_NAME Like '" + textBox1.Text + "'");

answered Dec 18 '14 at 18:12

D Stanley

149,601
11
178
240

2

Also the percentage when using Like '%' – A Ghazal Dec 18 '14 at 18:15
1

Oops, thank you my mistake. The "Like" operator doesn't seem to be working though, e.g., typing part of a known USER_ID from the list doesn't return any rows unless I type the whole thing. I'll modify my question to reflect this. – Alex Dec 18 '14 at 18:17
Beat me to it @AGhazal – Alex Dec 18 '14 at 18:18
@AGhazal I assumed (apparently wrongly) that the operators were included in the text value. – D Stanley Dec 18 '14 at 20:58

score 1 · Answer 3 · answered Dec 18 '14 at 18:15

You need single quotes and % around string values. It's throwing a syntax error because the constructed query has the raw strings from the inputs, as in "WHERE THE_NAME Like John". Try this:

tbl.Select("USER_ID Like '%" + textBox1.Text + "%' OR THE_NAME Like '%" + textBox1.Text + "%'");

Query a datatable where text contains

3 Answers3