Writing a Winforms Dynamic Query Tool

Question

I was tasked with writing a winforms C # application that allows users to run special requests. I searched the Internet and found many tools that you can buy ( EasyQuery ), but this time the purchase is out of the question.

So I'm trying to write it myself. At this point, I created a tree view that is populated with tables / columns at runtime, and I can select the columns that users checked. But now I have to figure out how to dynamically JOIN tables they selected.

The structure of the partial table 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 combined into different fields, but since users need to join on the fly, I need to define a JOIN when they want to generate SQL. At the moment, I don’t know how best to start generating these JOINs .

I even thought about creating a table in the database with the JOINs specified for each table / column, and then I could just build it there, but I'm at a loss.

I also tried something similar, but I do not want to start from the wrong path, if there are suggestions in 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 } } } } 

It seems like this is on the right track or can you suggest another way?

I know that this will be an incredibly difficult task, and I have not yet reached the dynamic WHERE . I'm just looking for advice on the best way to create JOINs on a one-time basis.

Answer 1

I can appreciate your desire to write it from scratch, because it is difficult and interesting! But do not make the mistake of spending valuable resources on what has already been written many times. Creating a functional and secure query tool is much more complicated than it might seem on the surface.

SQL Server 2008 Management Studio Express the last time I checked.

Versabanq Squel is a reliable and free sql query tool.

There are many others

And even more here

Answer 2

It seems to me that, based on your type scheme, it is best to create a view with all the predefined connections.

It also seems that you allow them to search for your db based on certain fields and parameters, not allowing them to write queries, since they are not sql per se literate. This is not an easy task (imho), but as soon as you have a view, you can present them with the “search” and “displayed” fields that they can select (wait until you start processing complex where clauses :)

Answer 3

I think you are mistaken. Should your users be able to join this whole combination of tables? Do they know what a connection is?

Here a simpler aproach:

• Create a database of queries that your users will want to fulfill.
• I assume your requests will take parameters. They must be marked with placeholders.
• In your interface, all possible requests for the user will be displayed. As soon as the user selects the request that they want to use (based on descriptive names, of course), the interface will analyze the request for parameter placeholders and generate a form for user input (that is, a course based on the assumption from above that your requests are parameters)

At the end of the day, you will receive a powerful repository of repeat requests, which you yourself will control the quality of. Instead of the user clicking the mouse to create harsh monsters that cannot even be reused.