为不熟悉SQL的最终用户创建类似sql的查询 [英] creating a sql like query for an end-user who is unfamiliar with SQL

问题描述

你好，

我有一个客户想要根据与该表关联的字段过滤记录.例如.如果有超过2000名员工，我们希望用户说出(员工身份=<值>和员工性别=<值>)或(员工职位=<值>).您会注意到< values>是另一个表的外键.

大约需要5个不同的屏幕，其中需要类似的功能(例如订单，公司，产品等).

关于如何实现这种解决方案并允许最终用户使用paranthesis()构建逻辑AND OR的任何想法?

谢谢.

Hello,

I have a customer who would like to filter records based on fields associated with that table. E.g. If there are over 2000 Employees, we want the user to say where (Employee Status = <value> and Employee Gender = <value>) OR(Employee Position = <value>). You''ll notice the <values> are foreign keys to another table.

There''s aproxmiately 5 different screens in which similar functionality will be required (e.g. Orders, Company, Product, etc..)

Any ideas how we can implement such a solution and allow the end user to build the logical AND OR with paranthesis ()?

Thank you.

推荐答案

它称为即席查询.有百万种方法可以实现这样的目标. Google是您的朋友.

[

It''s called an ad-hoc query. There are a million and one ways to implement something like that. Google is your friend.

716,000 google results for "asp.net ad-hoc query"[^]

I saw a link to codeplex in there, and if you add "codeproject" to the search phrase, you''ll likely find something there as well.

我建​​议您不允许用户手动输入临时SQL或类似SQL的查询.我从这里的经验谈起:那条路通往疯狂.

列出可搜索的列；员工可能有名字，姓氏，状态，性别，分支机构，职位和雇用日期.创建一个过滤器表单，用户可以在其中输入完整或部分信息.如果可能，请预先填充选项:可以将性别编码为带有F，M和空白的下拉列表(这不会进行过滤)，而可以从数据库下拉列表填充分支机构.

当用户单击 Search 按钮时，您的代码随后将表单数据组合到WHERE子句中.如果输入为空，则该数据点上没有过滤.例如，如果用户从下拉列表中选择一个，则可以包含BranchOffice=@BranchOffice；如果LastNameFilter.Text不为空，则可以包含LastName LIKE @LastName，且参数值设置为LastNameFilter.Text + %.执行查询并返回结果.

设置会花费一些工作，但是比尝试编写命令解析器并教您的用户如何使用它要少得多.

I recommend NOT allowing your users to manually enter ad hoc SQL or SQL-like queries. I speak from experience here: that road leads to madness.

Make a list of searchable columns; Employee might have first name, last name, status, gender, branch office, position and hire date. Create a filter form where the user can enter complete or partial information. When possible, pre-populate your options: gender can be coded to be a drop-down with F, M and blank (which would be no filtering) while branch office could be drop-down populated from the database.

When the user clicks on the Search button, your code then assembles the form data into a WHERE clause. If an input is blank, then there is no filtering on that data point. For example, you would include BranchOffice=@BranchOffice if the user selected one from the drop-down, or LastName LIKE @LastName if LastNameFilter.Text is not empty, with the parameter value set to LastNameFilter.Text + %. Perform your query and return the results.

It will take some work to set up, but it will be much less work than trying to write a command parser and teaching your users how to use it.

这篇关于为不熟悉SQL的最终用户创建类似sql的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！