Writing a Winforms Dynamic Query Tool

Question

I have been tasked with writing a winforms c# application that allows the users to run ad-hoc queries. I have searched online and found a lot of tools that can be purchased (EasyQuery) but at this time buying it is out of the question.

So I am trying to write this myself. At this point I have created a treeview that is populated at run-time with the tables/columns and I can select the columns that are checked by the users. But now I have to figure out how to dynamically JOIN the tables that they have selected.

Partial table structure is below:

Table - Roles - PK = RoleId
RoleId 
RoleName

Table - Center PK = CenterId/RoleId/Prefix
CenterId  
RoleId
Prefix

Table - Employee - PK = EmployeeID
EmployeeId
Name
CenterId
Dept
Org

Table - Prof - PK = EmployeeId/Profile
EmployeeId
Profile

I have a total of 6 tables that can be joined on a variety of fields, but since the users need to join on the fly I need to determine the JOIN when they want to generate the SQL. At this point I don't know the best way to proceed to generate these JOINs.

I even thought about creating a table in the database with the JOINs listed for each table/column and then I could just build it from there but I am at a loss.

I have also tried something similar to this but I don't want to start down the wrong path if there are suggestions for a different way:

private void GetJoins()
{
     string joinList = string.Empty;

     foreach (TreeNode tn in TablesTreeView.Nodes)
     {
         if (tn.Checked)
            if (tn.Nodes.Count > 0) // this would be parent items only
            {
                foreach (TreeNode childNode in tn.Nodes)
                {
                    // for first child checked 
                    // check the next checked parent nodes for matching checked fields
                    // if next parent node has same field name checked then add the JOIN
                 }
             }
      }
}

Does this seem like it is on the right track or can you suggest another way?

I am aware that this is going to be a incredibly difficult task and I haven't even gotten to the dynamic WHERE clause yet. I am just looking for advice on the best way to create the JOINs on an ad-hoc basis.

currently, there is no budget to purchase an outside product. — Taryn, Mar 11 '12 at 20:10

score 1 · Answer 1 · edited May 23 '17 at 12:11

1

I can appreciate your urge to write this from scratch, after all it's challenging and fun! But don't make the mistake of wasting valuable resources writing something that has already been written many times over. Creating a functional and secure query tool is much more complex then it may seem on the surface.

SQL Server 2008 Management Studio Express is free last time I checked.

Versabanq Squel is a robust and free sql query tool.

There are many others

And even more here

edited May 23 '17 at 12:11

Community

1
1

answered Mar 11 '12 at 23:00

Coltech

1,670
3
16
31

Giving them access to SQL server management studio or an alternative does not solve my problem. This will be used by people who do not know SQL. – Taryn Mar 11 '12 at 23:04
If you really want to write your own, you can download this as a starting point and tweak it all you want: http://www.codeproject.com/Articles/13419/SelectQueryBuilder-Building-complex-and-flexible-S – Coltech Mar 11 '12 at 23:15

score 0 · Answer 2 · answered Mar 12 '12 at 04:28

0

It seems to me, based on your sample schema, your best bet is to create a view with all the joins predefined.

Also, seems that you're letting them search your db based on certain fields and parameters, not really letting them write queries as they're not sql literate per se. It's still not a simple task (imho), but once you have the view, you could present to them the "search-able" fields, and the "display-able" fields that they can select against (wait 'till you start handling complex where clauses :)

answered Mar 12 '12 at 04:28

Jason

3,844
1
21
40

I have looked at creating a view with all tables joined (there are more than what is displayed above) but the problem with that is a cartesian product of records is returned making the results pretty much unusable. I am aware that once I get to the WHERE clause my fun will truly begin. – Taryn Mar 12 '12 at 14:19
@bluefeet, that depends on how you join your tables no? And if you let the users pick which fields they wish to see in the result, it should not give them a ginormous Cartesian resultset, basically, you would use one view in the *FROM* clause and you would generate the *SELECT* and the *WHERE* parts – Jason Mar 12 '12 at 14:26

score -1 · Answer 3 · answered Apr 20 '12 at 14:55

I think you're going about it the wrong way. Must your users be able to join all these combination of tables? Do they even know what a join is?

Here's a simpler aproach:

Create a database of queries that your users will want to execute.
I assume your queries will take parameters. Those should be marked with placeholders.
Your interface will display all the posible queries for the user. Once the user selects the query they want to use (based on descriptive names of course) the interface will parse the query for parameter placeholders and generate a form for user input (that is of course based on the assumption from above that your queries take parameters)

At the end of the day, you'll end up with a powerful repository of reusable queries that you yourself will control the quality of. Rather than have the user click around to create hidious monsters that can't even be reused!!

Writing a Winforms Dynamic Query Tool

3 Answers3