Databases
StackOverflow2013
Postsdbo
POQuery designer in winforms app
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQuery designer in winforms app
    primarykey
    data
    text
    <p>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.</p> <p>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.</p> <p>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.</p> <p>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).</p> <h2>Update</h2> <p>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:</p> <p><img src="https://i.stack.imgur.com/9dUYf.jpg" alt="enter image description here"></p> <p>The "Select table ..." combobox in the second group only shows tables that have been added to the top listbox.</p> <p>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.</p> <p>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).</p> <p>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.</p> <p>I've given them the first version of this, and they seem very happy with it so far.</p> <p>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!</p> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDan
    1. USDan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POQuery designer in winforms app
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POQuery designer in winforms app
      1. USPeterJ
      1. VTFavorite
    3. VO
      singulars
      1. POQuery designer in winforms app
      1. This table or related slice is empty.
      1. VTUpMod
    1. COare you looking for UI?
      singulars
      1. POQuery designer in winforms app
      1. USArshad
    2. COFolks already gave plenty (and sufficient) solutions: SQL Server Reporting Services (from Ben) or Access database linked to the tables from SQL Server (from Mark)
      singulars
      1. POQuery designer in winforms app
      1. USMarcus Vinicius Pompeu
    3. CO@mvaraujo Is this comment regarding my lack of response? I've not replied yet because I want to give a detailed update after I've got it working. I've decided to write a custom query editor which is designed specifically for the software's business objects. Don't worry - I've not forgotten about responding.
      singulars
      1. POQuery designer in winforms app
      1. USDan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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