Query designer in winforms app

Question

I'm working on a Winforms C# application for a client which replaces their old MS Access based solution. It's a complete rewrite and now uses SQL Server as its database. This software is now in the final stages of development.

One thing that they've just requested is that ability to do custom queries that the software's UI may not currently let them do at the time they need it.

With their old Access solution, they could use the query designer to create their own custom queries. With the winforms/Sql Server solution they can't do this. They don't want to have to write SQL themselves either.

Can anyone think of a good solution to this problem? Perhaps a Winforms library which allows the user to create a graph of business objects and "and|or" logic. Or some other type of UI which allows them to customise queries, almost like they could do in Access (but perhaps more domain specific).

Update

I've flagged Yaqub's answer as the answer, as this is the closest to what I was looking for at the time. I ended up though writing a custom form for them to generate their queries:

The "Select table ..." combobox in the second group only shows tables that have been added to the top listbox.

Because the database layout is pretty much set in stone now, I've written code to intelligently calculate any joins required. For example, if they add two indirectly related tables in the top group, then when it generates the SQL, it'll add any required joins to relate those. If the database layout does change, I've made it very easy to change FK references in the query editor's code.

For the condition group, the value control (4th control down in that group) changes depending on the field type (textbox, numerical up/down control, datepicker, checkbox).

When they click on "Run query", they get another form with a gridview displaying the results. In that results form, they can export to a tab delimited file.

I've given them the first version of this, and they seem very happy with it so far.

I didn't want to go the Access route because the whole point of this new version of the software is moving them away from Access (well, not the whole point, as there's a lot more functionality in there too). It seemed a huge step back to keep that dependency with Access there. It also means that if they save lots of custom queries in Access, and I ever change the database schema, I'll most likely break their queries. I don't want them having access to the database like that. In my mind, it's asking for trouble. The only thing that should be touching the database is the new software, and any automated database backups we do - nothing else, especially not users!

Another advantage to doing it within the software, is that I can do post processing on the query results. For example, there are quite a few data analysis algorithms that are run in the software which are written in the .NET code. So I can add fields to this interface that allow them to select the results of these algorithms.

Answer 1

You may find the Visual SQL Query Designer helpful. It will give you an idea that how you can implement this functionality.

This tool can be used to design SQL queries. Its UI is very basic as compared to SQL Server Management Studio. Its limitation is that it uses OLEDB connection string. You can download the source code from here.

EDIT:

EasyQuery.NET WinForms can be an option but its not free.

This article may also help you a bit.