creating a sql like query for an end-user who is unfamiliar with SQL

Question

0.00/5 (No votes)

See more:

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.

Posted 15-Jun-11 5:18am

pzn3xq

Updated 15-Jun-11 5:23am

#realJSOP

v2

Add a Solution

2 solutions

Solution 1

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.

Posted 15-Jun-11 5:23am

#realJSOP

Updated 15-Jun-11 5:27am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gregory Gadow · Accepted Answer · 2011-06-15T05:46:00

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.