如何将 SQL 查询应用于 C# 数据表/数据集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12513853/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to apply SQL query to a C# DataTable/Dataset?
提问by Eternal21
I have an application, where users can enter an sql query as text, and I need to run it against a C# DataTable/Dataset inside my application. Is it possible to do?
我有一个应用程序，用户可以在其中以文本形式输入 sql 查询，我需要在我的应用程序中针对 C# DataTable/Dataset 运行它。有可能吗？
EDIT: Based on the answers, and some more research it cannot be done - there is no way to apply an SQL query to a table that you already read into your application. Look at answers for possible workarounds.
编辑：根据答案和更多的研究，它无法完成 - 无法将 SQL 查询应用于已读入应用程序的表。查看可能的解决方法的答案。
EDIT 2: The final solution in my case was implementing a simple parser using ANTLR. It allowed users to enter simple queries using 'AND', and 'OR' keywords as well as parentheses. ANTLR generated class would convert that into a set of instructions which I could then use to query the C# Dataset.
编辑 2：在我的案例中的最终解决方案是使用 ANTLR 实现一个简单的解析器。它允许用户使用“AND”和“OR”关键字以及括号输入简单的查询。ANTLR 生成的类会将其转换为一组指令，然后我可以使用这些指令来查询 C# 数据集。
采纳答案by Tim Lehner
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.
如果您的用户将输入除最简单的 select 语句之外的任何内容，您将很难做到这一点。我想为您的项目编写一个完整的 SQL 解析器的成本会高得令人望而却步，但这基本上就是您所谈论的内容。
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.
对于我们拥有的本土 ORM，我有一个类，它基本上将预定义的 SQL 查询转换为可以与 一起使用的内容DataTable.Select，但 where 子句是从 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
Linqer（SQL 到 LINQ 转换器）然后是LINQ 到 DataSet
I have not used Linqer myself.
我自己没用过Linqer。
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.
@JoshC 还提到了 Sqlite 的可能性，还有 SQL Server 2012 LocalDB 可能符合要求，尽管这些肯定不是 .net 数据集。
回答by Turbot
if you want to run a search string against c# datatable/dataset inside your application
如果要在应用程序中针对 c# 数据表/数据集运行搜索字符串
you can use filter expression in selectmethod.
您可以在select方法中使用过滤器表达式。
myDataTable.Select("columnName1 like '%" + value + "%'");
myDataTable.Select("columnName1 like '%" + value + "%'");
