允许用户创建表的自定义查询 [英] Allowing a user to create a custom query of a 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 
  

查询：

  SELECT sum（Quanity1），sum（Quanity2），Property1 FROM Table Group By Property1 
  

结果：

  Quanity1 Quanity2 Property1 
 43 23蓝色
 55 133红色
  

我想做的是给用户图形界面要做到这一点，知道如何编写SQL查询，或任何代码。例如一组列表框，其中它们选择要查看的属性和数量，并显示一个表，显示所选字段的数量相加。我以后也可能希望添加用户执行其他SQL查询的功能，例如基于特定条件的过滤。此外，我知道以后，我需要能够根据这些用户查询生成漂亮的报告。

我对ADO和.NET一般很新。但是我认为最好的方法是将我的数据导出到System.Data.DataTable中，然后为用户创建一个接口，以便通过为其RowFilter属性生成一个字符串来创建一个System.Data.DataView。尽管如此，我不仅可以过滤和排序DataTable，还可以生成另一个表或视图，它只包含大主表中的特定列。

这听起来像是最好的选择，还是有另一种方法我应该考虑？有没有人有什么具体的提示或建议如何实现这一点？我也在质疑LINQ是否容易这样做。

更新
我感谢使用Access或其他可用的工具，但它真的不是一个选择。访问方式对于这里的用户来说太复杂了，试图弄清楚，实际上我需要的更多。我会永远保留Access作为高级用户的选项。但是我仍然希望设置一个基本的查询功能，用户可以在其中选择所需的列，软件会自动创建查看/查询，以选择和合并相应的列。

除了要复制Access的另一个问题之外，在更改数据结构中的某些内容并看到报表发生变化之间有许多点击。我不希望用户改变一些东西，重新导出以访问，打开另一个程序，然后打开报告来看看它们的变化。

解决方案

考虑购买现成的查询工具，而不是重新创建轮子。可以做这种事情的最便宜的一个是MS Access或Excel中的MSQuery。更精细地，您可以使用Report Builder（如果您的数据库基于SQL Server - 此为免费）或第三方工具（如Business Objects或Brio）。

如果你没有紧密的集成就可以生活，这比尝试构建自己的特别查询工具要容易得多。

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.

解决方案

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.

这篇关于允许用户创建表的自定义查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！