Querying a DataTable

[nbrege]

How do I query a DataTable where a value has an apostrophe in it? For example, my datatable has a lastname column and I want to search for the name "O'Bryan", how would I do it? With an SQL query I would just use a parameter & it would take care of the apostrophe automatically. Is there a way to use parameters with a DataTable? Thanks for any help...

[stanav]

Quick way: escape the ' by replacing it with two ''. (string.replace("'", "''")). This method is not recommended.

Proper way: use parameterized queries.

Edit: Sorry, I read it too fast. You're searching a datatable and not querying a database. So method 1 is OK to use.

[nbrege]

Thanks stanav ... I just wondered if there was a better way than the escape method...

[jmcilhinney]

Exactly how are you performing this "query"? If you're calling DataTable.Select or setting DataTable.DefaultView.RowFilter then you're simply passing a String so you have no choice but to use the escape method. Assuming .NET 3.5 or later, you also have the option of using LINQ, in which case you don't have to do anything about single quotes.

[nbrege]

JMC ... yes, I am doing a DataTable.Select. Is "query" not the correct term when getting data from a DataTable?

[jmcilhinney]

I just quoted "query" because it's not actually going to the database, which is what we usually consider to be a query. The term is fine.

Assuming .NET 3.5 or later, you might also like to consider using LINQ instead of DataTable.Select. This:

Dim rows As DataRow() = table.Select(String.Format("SomeColumn = '{0}'", textBox.Text.Replace("'", "''")))

is equivalent to this:

Dim rows As DataRow() = table.AsEnumerable().Select(Function(r) r.Field(Of String)("SomeColumn") = textBox.Text).ToArray()

and this:

Dim rows As DataRow() = (From r In table.AsEnumerable() Where r.Field(Of String)("SomeColumn") = textBox.Text).ToArray()

If you have a typed DataSet then it gets neater still.