允许用户创建表的自定义查询
我正在编写一个生成单个大信息表的程序。 该表由两种类型的列组成：包含数量的列和包含属性的列。 数量是可以求和的数值，属性是归因于数量值的定性值。
如果数据位于数据库的表中，我可以编写一个查询，选择特定的属性和数量，并对所选属性具有相同值的数量求和。
示例：
表：
Quanity1 Quanity2 Quanity3 Property1 Property2 Property3
12 43 12 RED Long Rough
43 23 23 Blue Short Smooth
43 90 34 RED Fat Bumpy
查询：
SELECT sum(Quanity1), sum(Quanity2), Property1 FROM Table Group By Property1
结果：
Quanity1 Quanity2 Property1
43 23 Blue
55 133 Red
我想要做的是为用户提供一个图形界面来执行此操作，而无需知道如何编写SQL 查询或任何与此相关的代码。 例如，用户可以在一组列表框中选择想要查看的属性和数量，然后显示一个表格，其中显示所选字段以及数量的总和。 稍后我可能还想添加用户执行其他 SQL 查询之类操作的功能，例如根据某些条件进行过滤。 我还知道稍后我需要能够根据这些用户查询生成美观的报告。
总的来说，我对 ADO 和 .NET 非常陌生。 但我认为最好的方法是将数据导出到 System.Data.DataTable 中，然后为用户创建一个接口，通过为其 RowFilter 属性生成字符串来创建 System.Data.DataView。 尽管如此，对我来说，如何不仅可以过滤和排序 DataTable，还可以生成另一个仅包含大主表中特定列的表或视图，这对我来说并不明显。
总的来说，这听起来是最好的选择，还是我应该考虑其他方法？ 有人对我应该如何实现这一点有任何具体的提示或建议吗？ 我还想知道使用 LINQ 是否可以使这一切变得更容易。
更新 我很欣赏使用 Access 或其他可用工具的建议，但这确实不是一个选择。 对于这里的用户来说，访问太复杂了，无法理解，而且比我实际需要的要多得多。 我始终将 Access 作为高级用户的一个选项。 但我仍然想设置一个基本的查询功能，用户可以选择他们想要的列，软件会自动创建视图/查询来选择并汇总适当的列。
除了过于复杂之外，Access 的另一个问题是在更改数据结构中的某些内容和查看报告中的更改之间需要进行多次点击。 我不希望用户必须更改某些内容，重新导出才能访问，打开另一个程序，然后打开报告以查看更改的效果。
I am writing a program that generates a single large table of information. The table is made up of two types of columns, columns that contain quantities, and columns that contain properties. Quantities are numeric values that can be summed, properties are qualitative values that attribute the quantity values.
If the data is in a table in a database I can write a query that selects specific properties and quantities and sums the quantities that have the same value for the selected properties.
Example:
Table:
Quanity1 Quanity2 Quanity3 Property1 Property2 Property3
12 43 12 RED Long Rough
43 23 23 Blue Short Smooth
43 90 34 RED Fat Bumpy
Query:
SELECT sum(Quanity1), sum(Quanity2), Property1 FROM Table Group By Property1
Result:
Quanity1 Quanity2 Property1
43 23 Blue
55 133 Red
What I want to do is give the user a graphical interface to do this with out knowing how to write SQL queries, or any code for that matter. Such as a set of list boxes where they select the properties and quantities they want to view and a table is displayed that shows the selected fields with the quantities summed. I may also later want to add the ability for the user to perform other SQL query like actions such as filtering based on certain conditions. Also I know later I'll need to be able to generate nice looking reports based on these user Queries.
I'm very new to ADO and .NET in general. But I'm thinking the best way to do this is to export my data into a System.Data.DataTable and then create an interface for the user to create a System.Data.DataView by generating a string for it's RowFilter property. Although, it's not obvious to me how I can not only filter and sort a DataTable but generate another Table or view that only contains specific columns from the big master table.
Overall does this sound like the best option, or is there another method I should consider? Does anyone have any specific tips or suggestions on how I should implement this? I was also questioning if any of this would be made easier with LINQ.
Update
I appreciate the suggestion of using Access or other available tool, but it's really not an option. Access is way too complicated for users here to try to figure out, and much more then I actually need. I'd always leave Access as an option for advanced users. But I would still like to setup a basic querying feature where the user selects the columns they want and the software automatically creates the view/query that selects and sums the appropriate columns.
Aside from being to complex the other issue with Access is there are to many clicks between changing something in my data structure and seeing a change in a report. I don't want the user to have to change something, re-export to access, open another program, and then open the report to see the effect of their change.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
考虑购买现成的查询工具，而不是重新发明轮子。 能够完成此类任务的最便宜的工具是 MS Access 或 Excel 中的 MSQuery。 更详细地说，您可以使用 Report Builder（如果您的数据库基于 SQL Server - 它是免费提供的）或第三方工具，例如 Business Objects 或 Brio。
如果您可以在没有紧密集成的情况下生活，这比尝试构建自己的临时查询工具要容易得多。
Consider buying an off-the-shelf query tool rather than re-inventing the wheel. The cheapest one that could do this sort of thing is MS Access or MSQuery in Excel. More elaborately you could use Report Builder (if your database is based on SQL Server - it comes for free with this) or a third-party tool such as Business Objects or Brio.
If you can live without tight integration this is far easier than trying to build your own ad-hoc query tool.
我还强烈推荐现成的——尤其是在早期。 如果后来发现用户确实需要您编写自定义解决方案，那么请务必这样做。 但在这么早的时候，我认为不值得你花时间和精力。
I also strongly recommend off-the-shelf - especially early on. If it becomes apparent later on that the users really need you to write a custom solution, then by all means go for it. But this early on I don't think it will be worth the time and effort you will spend.