在 .net 2 中构建动态 SQL 查询？
我可以重新发明轮子 - 但是..
我需要允许用户能够从我们的数据库构建“客户报告” - 这将来自 GUI。
他们无法仅访问 SQL 表（数据组）和这些组中的列的列表。
他们还能够创建Where 子句（标准）。
我环顾谷歌 - 但什么也没出现。
有什么想法吗？
I could be re-inventing the wheel - but..
I need to allow a user to be able to build 'customer reports' from our database - which will be from a GUI.
They can't have access to SQL just a list of Tables (Data groups) and columns within those groups.
They also have the ability to create Where clauses (criteria).
I've looked around Google - but nothing cropped up.
Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我的建议是，Developer Express 有一些令人惊叹的最终用户标准构建器，您可以使用他们的。
还有其他控件可以创建最终用户标准，例如
http://devtools.korzh.com/query-builder-net/
我希望这有助于您
上面的两个控件抽象数据访问层，以便您的最终用户无法向数据库发送直接查询。这些控件仅构建条件，您只需将查询发送到数据库即可。
well my recommendation, Developer express have some amazing end user criteria builder, you can use theirs.
there are other controls to create end users criteria , like
http://devtools.korzh.com/query-builder-net/
I hope that help you
both controls above abstract the data acess layer so your end users wont have access to send a direct query to the database. The controls only build the criteria and its your work to send the query to the database.
作为我答案的先驱，有许多昂贵的产品，例如 Izenda (www.izenda.com)，它们可以非常优雅地完成此操作...
如果您想推出自己的产品（并回答您的问题），您可以可以很快地伪造这一点（是的，这不能很好地扩展到超过 4 个连接），如下所示：
创建要向用户公开的所有可用字段的字典，如下所示：Dictionary = Dictionary<[漂亮的可显示名称], [完全限定 Sql 字段名称]>
让用户从上面的字典中构建他们想要查看的字段和想要添加的条件的选择列表，并使用字典值将返回结果所需的 SQL 字符串连接在一起。
（我跳过了相当多的验证工作，以确保用户不会尝试错误输入条件等，但本质上是，对于一小部分表，您可以创建一个静态“来自”语句，然后安全地附加用户构建的连接的“select”和“where”）
请注意，我已经在一些实际存储表关系的系统上工作，并编译了最有效的“from”语句...这并不是这个答案的巨大延伸，而是只是再多做一点工作。
As a precurser to my answer, there are a number of expensive products out there like Izenda (www.izenda.com) that will do this very elegantly...
If you want to roll your own (and to speak to your question) you can fake this pretty quickly (and yes, this does not scale well to more than about 4 joins) like this:
Create a dictionary of all available fields you want to expose to the user such as this: Dictionary = Dictionary<[Pretty displayable name], [fully qualified Sql field name]>
Let the user build a select list of fields they want to see and conditions they want add from the dictionary above and use the dictionary value to concat the sql string together that is necessary to return their results.
(I'm skipping quite a bit of validation work about making sure that the user doesn't try to mis-type the condition and such, but essentially point of this is that for a small collection of tables you can create a static "from" statement and then safely tack on the concat'ed "select" and "where" that the user builds)
Note that I've worked on some systems that actually stored the relationships of the table and compiled the most efficient "from" statement possible... that is not a huge stretch from this answer, it's just a bit more work.
我强烈建议使用现有产品，例如 Crystal Reports、Sql Server Report Builder 或 Infomaker。获得看似有效的东西是如此容易，但却让您容易受到 SQL 注入攻击。
如果您继续，我建议对这些报告使用单独的 sql 连接。此连接应该有一个仅具有数据库中任何位置的读取权限的用户帐户。
I strongly recommend going with an existing product like Crystal Reports, Sql Server Report Builder, or Infomaker. It's just so easy to get something that seems to work, but leaves you open for an sql injection attack.
If you do go ahead, I recommend using a separate sql connection for these reports. This connection should have a user account that only has read privileges anywhere in the database.
感谢您的回答！我们最终通过收集视图自己完成了这件事！
例如：
这些视图已经处理了表之间的大部分连接，并返回尽可能多的连接数据。
然后，用户选择他们希望从每个视图中看到的列，我们在代码级别进行视图之间的连接。
生成的语句非常小，因为视图占用了其中的大部分工作。
Thanks for the answers! We ended up doing this ourselves through a collection of views!
For instances:
The views already take care of most of the joining between tables and return as much joined data as they can.
The user then selects what columns they could like to see from each view and we do the join between the views at code level.
The resulting statement is very small as the views take most of the work out of it.