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?

Kimberlite

you could always use LINQ – Gaw

@Gaw I like that idea, can you please refer me to some documentation for using LINQ to search within a datatable? – Kimberlite

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

Socha

I just changed a LINQ query on a list of objects to a datatable .select as above and picked up a ton of speed – Doorjamb

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 + "'");

Larcener

Also the percentage when using Like '%' – Denney

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. – Kimberlite

Beat me to it @AGhazal – Kimberlite

@AGhazal I assumed (apparently wrongly) that the operators were included in the text value. – Larcener

Answer 3

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 + "%'");

Salcido