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?

Answer 1

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

Answer 2

You need to add single quotes around your strings:

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