Okay, I have managed the following:

1. Built a MyGeneration template and supporting classes where the template auto-generates/populates schema info from a database. The supporting classes provide helper methods to get access to:
   a) Database schema (list of tables, fields, primary keys, foreign keys/ relationships, etc)
   b) Table schema (list of fields, etc)
   c) Field/Property schema
   d) Primary Key info
   e) Relationship/ Foreign Key info
   
   Examples:
   

   Code:

   // Get info for a specific field:
   IField fieldInfo = schemaManager.GetFieldInfo(_Database, table, field);
   
   // Get a collection of field info related to a specific table:
   ICollection fields = schemaManager.GetFields(_Database, table);
   
   // Get the foreign keys for a specific table:
   ICollection foreignKeys = schemaManager.GetForeignKeys(_Database, table);

   
   
2. Built a UI where most of the above is presented similar to the MS Access 2007 Query Builder

Here is a screen shoot:

Using the Query Builder:

1. The tables on the left are auto-populated from the classes/ auto-generated output with the custom MyGeneration template. (They just show up when the form is opened)
2. A user drags a table onto the working area (center screen) and the graphic appears.
3. Drag another table onto the work area and the schema determines the relationships and the are auto-created/ connected if relationships exist
4. NOTE: The primary key fields have a "key" icon next to the appropriate fields. The foreign key columns have the chain link icon, and self-referencing foreign keys do not get a connector, but do display a mofified link icon (look at 'ReportsTo' in the Employees table).
5. If a field is 'clicked', the properties pane on the right auto-populates the schema info about the specific field.
6. If a property in the property pane is clicked, the description is auto-displayed (look at bottom right side of image)
7. The bottom accepts a field being dragged into grid...

This is where I need ADVICE!

I need suggestion on the best way to supply (via the GUI) options for an ES query.

1. I imagine that relationships can be selected where a right-click menu pops up a context menu to define the join (Left, Inner, etc)
2. I imagine that relationships can be created manually by draggin a field form one table to another and then slecting the join properties. A join should also be abel to be deleted.
3. I show a sort column (Ascending/Descending by field)
4. I show a Total column where the enumeration for ES aggregate options are available (There should be a button to enable/disable totals/aggregate choices.
5. The 'Show' column is checked or unchecked where a checked column is part of the output and unchecked is to be used as part of criteria
6. THE BIGGY - the Criteria column - I imagine some form of dialog to define/edit criteria as well as allow for manual entry.
7. I imagine sub-queries being part of criteria
8. I am not sure how to allow for the creation of parameters for use in querries/ sub-querries

Please, look at the GUI and help me figure out what elements are likely to be the most user-friendly and be inclusive of offering all the options ES provides.

 NOTE: I will build property panes for all levels of schema

It is getting so close, I hope people feel inclined to contribute on any level.

 TIA

First, that looks stunning. The progress you are making is remarkable. I jotted down some notes when I originally looked over your post. My intention was to give them more consideration before posting. Unfortunately, time is not on my side. I thought I'd post what I had, before they became too stale. So, here are some quickly formed (and probably half-baked) ideas that popped into my head as I reviewed the screen-shot and your notes:

One thing that has always bothered me about the MS Access Query Builder model is that sort order and selection criteria are completely independent of the columns selected for display and the order in which they are displayed. One way to improve upon that might be to have the bottom window be a tabbed window, i.e.

Display Columns | Sort Order | Selection Criteria

You could then remove the Sort, Show, and Criteria fields from Display Columns.
You might need a "Display Name" field (a.k.a Alias) added to Display Columns.
You might want an "Expression" field added to Display Columns for non-aggregate functions like Coalesce or SubString. This would ideally need some sort of Expression Builder so they could be daisy-chained in a specific order, with the right parameters.

The Sort Order tab would just be a list of dragged elements, with the corresponding ASC/DESC. Aggregates have some specific requirements. If one of the Display Columns is defined as a aggregate, you may want to help out the user by pre-populating the Sort tab with all non-aggregate columns and using them in the GROUP BY.

The Criteria screen depends a lot on your target user. Maybe a combination of a right-click context menu for inserting paren, AND/OR, operators, etc. plus dragging columns and parameters.

Having FK relations automatically populate is very cool. But, I do not think those relationships need to be treated any differently than user defined relationships. The right-click context menu could have a "Define Relationship" and "Delete" menu items. You need to be able to delete pre-defined FK relationships, so they do not interfere if you need to join different columns from each table.

Some relations are left-right dependent. Do you want a zero-to-many list of all employees with related Orders, or do you want a many-to-one list of all Orders with their related Employee name. This should not be an issue for FULL and INNER joins, but for LEFT joins, the "Define Relationship" popup needs allow the user to define the "left" table. The default should probably be INNER join, unless the user overrides it from the context menu.

One would need to be able to delete/edit self-referencing relationships like Employees ReportsTo. You need to be able to select different fields from each side of the join. Most QueryBuilders handle this by dropping the table twice, e.g., Employees and Employees1. Then, you can select all fields except ReportsTo from the Employees table, and just the LastName and FirstName from the related Employees1 table.

Northwind does not have any composite PK/FK relationships, but imagine the Customers table with a composite PK of CustomerId and CustomerSub. The Orders table would now need separate CustomerId and CustomerSub fields with a composite FK to Customers. Most QueryBuilders and Report Designers will display two relationship lines between the two tables, one for each field. But, they need to be treated as one composite join. Right-clicking and changing/deleting either changes/deletes both. You should be able to add a relationship between just one of them, however.

I envision additional collapsible lists below "Tables" and "Views" in the left panel for user-defined elements like Parameters, Calculated Columns, and SubQueries. In additional to adding user-defined parameters, maybe have a quick parameter feature, if the user drags a column to the Parameters panel. You tack the traditional @ (at-sign) on to the front of the column name, and an incrementing number to the end.

Calculated columns would be things like "LastName + FirstName", or "UnitPrice * Quantity". These could be dragged to the Display Columns, Sort, or Criteria tabs.

SubQuery support sounds really challenging, not that the preceding is a piece of cake. One way to have the full QueryBuilder available while designing a SubQuery, would be to create a Query, save it as a named Query, start a new Query, then add the saved Query in the SubQuery panel with a file open dialog. You might even be able to support nesting SubQueries by saving the new Query with the SubQuery, starting a new Query, and adding the just saved Query with a SubQuery as a SubQuery to the current Query.

Just the SubQuery name would appear in the list, but you might have to import all its elements behind the scenes, and save them with the new query. A scalar SubQuery could be dragged directly to the Display Columns tab (SELECT SubQueries). Other SubQueries could be dragged to the main design window like a table or view (FROM SubQueries, JOIN ON SubQueries), or to the Criteria tab (WHERE SubQueries).

Hehe, my head is spinning just thinking about it all.  QueryBuilder 2010?

I jotted down some notes when I originally looked over your post. My intention was to give them more consideration before posting. Unfortunately, time is not on my side. I thought I'd post what I had, before they became too stale. So, here are some quickly formed (and probably half-baked) ideas that popped into my head as I reviewed the screen-shot and your notes:

Well, I am hoping for just such comments. While it is nice for people to take their time, I appreciate any feedback.

One thing that has always bothered me about the MS Access Query Builder model is that sort order and selection criteria are completely independent of the columns selected for display and the order in which they are displayed. One way to improve upon that might be to have the bottom window be a tabbed window, i.e.

Display Columns | Sort Order | Selection Criteria

You could then remove the Sort, Show, and Criteria fields from Display Columns.
You might need a "Display Name" field (a.k.a Alias) added to Display Columns.
You might want an "Expression" field added to Display Columns for non-aggregate functions like Coalesce or SubString. This would ideally need some sort of Expression Builder so they could be daisy-chained in a specific order, with the right parameters.

Okay, I like the idea of better control...

For example, if a user drags a column onto the Display Columns tab, does/should that automatically add the same column into the sort order columns with the option of Not Sorted automatically applied. Then the user can switch tabs and choose Ascending/Descending for those they want sorted. If they sort a column in the listed of unsorted columns, it jumps to the top of the sort order list (grid). If they select another it is added as second in the order. They can then use drag & drop to change the order from top most being first to bottom most being last... Or... should the same logic as above be used but the sort columns are manually added/deleted independent of the Display Columns?

I like also the idea of an Alias for the display name and was wondering if I should add a form for editing global alias associations that are persisted locally for each field of every table/view? Then the alias would be auto-applied if an alias has already been applied globally, but yet be capable of being overriden for any specific query?

I hope for some exampes for what you mean with Expressions. I have some good ideas, but like to understand better.

The Criteria screen depends a lot on your target user. Maybe a combination of a right-click context menu for inserting paren, AND/OR, operators, etc. plus dragging columns and parameters.

It will be a contexct menu and/or double-click the critieria column and/or manually enter the Criteria (as I envision it now). I obviously need a a tab somewhere to display parameters and allow them to be edited. I need to better understand what ES needs defined to supply a parameter (I.e. just the name or does it need a system type or DB type also associated?)

Having FK relations automatically populate is very cool. But, I do not think those relationships need to be treated any differently than user defined relationships. The right-click context menu could have a "Define Relationship" and "Delete" menu items. You need to be able to delete pre-defined FK relationships, so they do not interfere if you need to join different columns from each table.

Some relations are left-right dependent. Do you want a zero-to-many list of all employees with related Orders, or do you want a many-to-one list of all Orders with their related Employee name. This should not be an issue for FULL and INNER joins, but for LEFT joins, the "Define Relationship" popup needs allow the user to define the "left" table. The default should probably be INNER join, unless the user overrides it from the context menu.

One would need to be able to delete/edit self-referencing relationships like Employees ReportsTo. You need to be able to select different fields from each side of the join. Most QueryBuilders handle this by dropping the table twice, e.g., Employees and Employees1. Then, you can select all fields except ReportsTo from the Employees table, and just the LastName and FirstName from the related Employees1 table.

Auto-populated relationships are treated the same as manually entered relationships (they just provide a better starting point).

The joins will have a context menu to define the Join type (Full, Left, Inner, etc). I have no iissue with inner being the default, if it needs to change (practice using the builder dictates something better works - it can be changed)

As fas as adding a table twice for self-referencing relationships (I agree). They may also be need if two or more tables link to the same table. I will allow multiple copies and auto-append a number to keep the reference unique (I.e. Employees1 would be the second copy)

I envision additional collapsible lists below "Tables" and "Views" in the left panel for user-defined elements like Parameters, Calculated Columns, and SubQueries. In additional to adding user-defined parameters, maybe have a quick parameter feature, if the user drags a column to the Parameters panel. You tack the traditional @ (at-sign) on to the front of the column name, and an incrementing number to the end.

Calculated columns would be things like "LastName + FirstName", or "UnitPrice * Quantity". These could be dragged to the Display Columns, Sort, or Criteria tabs.

SubQuery support sounds really challenging, not that the preceding is a piece of cake. One way to have the full QueryBuilder available while designing a SubQuery, would be to create a Query, save it as a named Query, start a new Query, then add the saved Query in the SubQuery panel with a file open dialog. You might even be able to support nesting SubQueries by saving the new Query with the SubQuery, starting a new Query, and adding the just saved Query with a SubQuery as a SubQuery to the current Query.

Just the SubQuery name would appear in the list, but you might have to import all its elements behind the scenes, and save them with the new query. A scalar SubQuery could be dragged directly to the Display Columns tab (SELECT SubQueries). Other SubQueries could be dragged to the main design window like a table or view (FROM SubQueries, JOIN ON SubQueries), or to the Criteria tab (WHERE SubQueries).

 I agree with parameters and sub-querries as anothe rcollapable panel on the left, but not necessarily calculated fields. I think a context menu option should be available in the "Display Columns" and a form opens to edit/create a calulated column, they then drop into the Display Columns tab with somw identifier noting that they are "calculated".

Well, the query builder would suck (IMO) if you could not save the query. All the data will be serializable and be able to be persisted to save a query. Thus, it should be very possible to expose members of a query to another query in order to use it as a sub-query - I.e. any parameters and the result, or whatever elements are needed)

 You had mentioned composite key relationships and the fact that Northwind does not provide any examples.

I can tell you what would help me most:

1. A database that works for all known test samples.

2. Known queries that "should work" with ES that apply each of the options (parameters, each join type, sorting, sub-queries, etc)

Essentially, it is a thousand times easier for me if I can start with a database that has what is needed for the tests and if I have sample querries that are known to work. I can then get the query builder to create those queries. I would love to actually have two or more databases I.e. SQL Server (my primary), MySQL, etc.

I would be happy to get it working/ tested with SQL Server first.

 BTW: QueryBuilder 2010 ????

I want much of this working next month! :)

Okay, I took your advice (as best I interpreted)...

 What do you think?

Screenshot:

This is my sad face :{

I am so hoping for people to comment/ help ... ... ...

Hi Trevor

This is my happy face!!

Looking great - more than great to be honest!

One thing I would comment on is the "Expression" row - I think that typically an expression (in the select part) would be like a virtual/calculated field rather than a field 'option' (if that makes sense?) - so instead of having an Expression Row, it may make it appear more intuitive if you have a row that says what type of element it is - i.e. an actual field from the table/view or an expression/virtual/calculated field.  If the user drags a field/property onto the selection grid then that would show as coming from that table, if they either manually entered info there, or built the expression/virtual/calculated field (i.e. [Firstname] + " " + [Lastname]) then this would show as an Expression - does that make sense? As I say, for the Select part of the SQL I would show/treat 'expressions' as Virtual/Calculated fields, obviously in the WHERE part of the clause then expressions act differently

Hope that helps for the short-term - sorry I've not had time to post up proper (and may not be able to after today - I'm off all of next week and my internet access isn't sorted at my new home yet so while I may actually find time to have a play with what you sent to me before etc, I may not be able to feed back on it (damn!) - very frustrating not having broadband at home at the moment I can tell you!!!!

Cheers

Martin

I think it would be cool if you had a little executable we could download and play with. I really need to see it in action to understand it. Now let me lay this head trip down on you

EnitySpaces 2009 will have what we call a modeler. It wont be in the Q1 release but it will be in the 2009 product. Unless I am wrong what you are doing is building an ES DynamicQuery code snippet in C#/VB (sorry, I've been so heads down on this ES2008 maintenance release). What our modeler will do is have a designer just like yours only it will save the information (describing the joins and columns) to a database or in xml so that during code generation we can generate a strongly typed entity/collection/query off it just as if it were a view in your database. So I see great overlap here. I can't help but think if only Trevor's product was generic enough then we could possibly work something out, buy it, whatever. However, I'm speaking off the cuff here, but those are the thoughts in my head. You can see this in our posts HERE and HERE and HERE. So, finally, ES2009 is the version the modeler will come into existance. So don't take my lack of comments as disinterest in your work by any means.

Nice work indeed !

Is it a project for internal use only or do you intend to distribute it (commercially or as freeware)?
 

Yahoo! Responses!

 Quote [pritcham/Martin]:

One thing I would comment on is the "Expression" row - I think that typically an expression (in the select part) would be like a virtual/calculated field rather than a field 'option' (if that makes sense?) - so instead of having an Expression Row, it may make it appear more intuitive if you have a row that says what type of element it is - i.e. an actual field from the table/view or an expression/virtual/calculated field.  If the user drags a field/property onto the selection grid then that would show as coming from that table, if they either manually entered info there, or built the expression/virtual/calculated field (i.e. [Firstname] + " " + [Lastname]) then this would show as an Expression - does that make sense? As I say, for the Select part of the SQL I would show/treat 'expressions' as Virtual/Calculated fields, obviously in the WHERE part of the clause then expressions act differently

I added the Expression row based on this suggestion:

Quote [David Parsons]:

You might want an "Expression" field added to Display Columns for non-aggregate functions like Coalesce or SubString. This would ideally need some sort of Expression Builder so they could be daisy-chained in a specific order, with the right parameters.

If it helps,  I do expect to provide some graphic means (like different coloring) to clearly differentiate calculated fields from regular fields let alone the field name would need a custom name and the table would likely be named something like,  "Calculated Field". I am sure that changes will have to be made once the UI is matched to the actual code needed to generate the queries (in other words, it should become more self-evident what eventually works best).

 btw: thanks for commenting - receiving comments is very motivating - thanks!

Quote [Mike Griffin]:

I think it would be cool if you had a little executable we could download and play with. I really need to see it in action to understand it.

That would be nice - I know. Currently, the project is based on the DevExpress library and there are issues with licensing. Most of it could be ported to a more "generic" or basic look & feel, but I started with my current needs: to match the Look & Feel of my core application.

Quote [Mike Griffin]:

EnitySpaces 2009 will have what we call a modeler. It wont be in the Q1 release but it will be in the 2009 product. Unless I am wrong what you are doing is building an ES DynamicQuery code snippet in C#/VB (sorry, I've been so heads down on this ES2008 maintenance release). What our modeler will do is have a designer just like yours only it will save the information (describing the joins and columns) to a database or in xml so that during code generation we can generate a strongly typed entity/collection/query off it just as if it were a view in your database. So I see great overlap here. I can't help but think if only Trevor's product was generic enough then we could possibly work something out, buy it, whatever. However, I'm speaking off the cuff here, but those are the thoughts in my head.

All of that is VERY interesting. I think "something" could definetely be worked out. All the information is there and its easy to save/ persist the info.

In fact, as I have been building it, it has become very nice to work with and some other possible future features come to mind - like a two way relationship with the database - as in the ability create/modify the schema. How hard could it be to add the most common and basic of features like dropping/adding tables, changing table names, changing field names, or the data type along with the restictions like field size? It seems like it would not be overly challenging. It would certainly not be a "cake walk", but it would certainly make me happy.

I have to consider some restrictions on the products I used to build it. I will follow up on that with my reply to ElPipo.

Quote [ElPipo]:

Is it a project for internal use only or do you intend to distribute it (commercially or as freeware)?

Currently, I use the Developer Express library to create the Look and Feel and I love thier components. Developer Express also makes XPO which is a competing product and their EULA forbids using their products to build something for competing products. Because of these comments, I wrote them to ask about this scenario. Personally, it would be a two-edged sword for them. Enhancing ES could loose some XPO business. conversely, using a Developer Express based UI could draw in many users to their other products. I actually believe that it would draw in more users for heir .NET library than it would loose in XPO business.

I intend to point out to them that it will be built anyway - I have licenses to other vendor products that would work and I could port it over based on another vendor such as DotNetBar. So, thee "competition" element will exist no matter what.

Now, should it be freeware/open source, should it be sold...

Well, the "Query Builder" aspect began as an idea here on the forum and many people started to contribute and an idea grew that we should have a "free" version. Conversely, who wants to spend too much time on something for free? That means it would likely stay basic and not be too impressive if only a free version existed.

 My initial thoughts right now are:

1. Create a "basic" Query Builder version that uses only standard Visual Studio controls. It would be very handy not only for those experimenting with ES, but for those who want a tool that will generate the code for a query using a GUI tool.

2. Create a beefed up version that has a nice ribbon-based UI. It would likely offer many enhanced features and simply would look better. There are licensing issues with DevExpress, but there isn't with DotNotBar as well as some other alternatives I have available. I would much prefer to use DevExpress, so we will see where that goes.

3. The initial version (any version of above) should be for developers, not end-users. This meand the UI won; conflict with anything for these puposes.

4. I expect to devlop end-user capability - that means there is no way to avoid some level of conflict that can occur with each respective developer's choice of product(s) to create a UI - as in the skinning/ look & feel may not match someone else's UI. The most that could be done is create a version using standard controls and one using a vendor that has a ribbon Look & Feel. These could be used with no changes (as is) if the UI aspects are not an issue.

4. No matter what, the "engine" or API would be available and would allow someone to create their own UI - assuming a purchase of source code. Therefore, it is importat to separate the UI from the core API. From what I have built so far, that should not be an issue.

NOTE: based on Mike's comments and my response to him, there is potential to expand the feature set to include custom modeling (if I intpret properly - he means build a view in the UI and an associated business object (ES classes) would be generated. I assume this means users could pick and choose fields and work with one object that is a mix selected fields from various tables). As an idea to throw in the air, I also think it could grow to support a UI that could create and modify database schema.

ALSO NOTE: I don't show it in the images, but the output would not just be the data, it would also be the code to create te same query in C# and/or VB (cut and paste into Visual Studio).

All my comments are "off-the-top of my head", so there is nothing written in stone. In other words, I am thinking out loud and sharing my thought with everyone.

If those two things happened, what product out there would be even remotely as desireable?