How to apply SQL query to a C# DataTable/Dataset?

If your users will be entering anything but the simplest select statements, you will have a very difficult time doing this. I imagine it would be prohibitively costly for your project to write a complete parser for SQL, but that's essentially what you're talking about.

For a home-grown ORM that we have, I have a class which transforms essentially pre-defined SQL queries into something that can be used with DataTable.Select, but the where clause is generated from SqlParameters.

Possible Solution

Perhaps you can combine the following projects to get you close to what you're after:

Linqer (SQL to LINQ converter) then LINQ to DataSet

I have not used Linqer myself.

Some other thoughts

I'm sure you've been giving this some thought, but the difficulty of doing this possibly means there's a better way if you zoom out a little. Strictly speaking, querying a cache with an unknown query means that you'd have to fill the cache with all possible data, or be able to call that data upon submitting the query. By definition, this can't deliver better performance than querying the source directly, unless you're hitting the cache enough before it's out of date to make that worthwhile. For an ad-hoc reporting system (my assumption), I tend to doubt that's the case, and I would also worry that it will not outperform the database engine in anything but edge cases.

@JoshC also mentions a possibility with Sqlite, and there's also SQL Server 2012 LocalDB which may fit the bill, though these certainly aren't .net datasets.

if you want to run a search string against c# datatable/dataset inside your application

you can use filter expression in select method.

myDataTable.Select("columnName1 like '%" + value + "%'");