Databases
StackOverflow2013
Postsdbo
POBuilding a custom SQL querying tool that doesn't require the user to know SQL (ASP.NET)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBuilding a custom SQL querying tool that doesn't require the user to know SQL (ASP.NET)
    text
    copied!<p>Hoping I can get some architectural advice on this. I have been given the requirement to develop a "custom reporting" module that can basically enable the user to query any table in the database, without knowing SQL. The idea as expressed to me is that the user should be able to use english-like way for saying what table they want, what fields they need to be displayed, any filtering they want, and now that they might need to create queries that have foreign key relationships in the DB.</p> <p>My original idea was to present a "wizard" of sorts that would let the user 1) Select a table from a dropdown list, 2) Get a list of available columns, not all columns are allowed, via a custom attribute applied to special "Query" classes, and 3) Add a variety of filter queries using english-like nomenclature from dropdowns (e.g. "IS EQUAL TO" instead of "=", "IS IN" instead of "IN ()").</p> <p>However, since I need to be able to know, seemingly magically, that if the user asks for x data I have to join tables together, I have no idea how to properly architect this or even if it's feasible (management seems to think it is...).</p> <p>Any suggestions or advice on tackling something like this? The users are external customers who will be accessing a website, not internal, so I can't just say "Let them use MS Access" or similar, but I can't just do a column name dump or give a big textbox for them to enter SQL queries, either. The only way I can think of doing this is to have specific views for the data, but that defeats the vision of the module (not having to create views/procedures for every report a client would want).</p> <p><strong>EDIT</strong> Note we are not using Reporting Services; the "report" is really just a tabular GridView, not an SSRS report.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload