Some initial feedback

Jun 26, 2013 at 1:05 AM
Hey there,

I just had a quick look through your BETA version. Just have a few notes to share:
  • Tables ComboBox ("Current" -> "DataBox" -> "Load") should be sorted
  • Reset link doesn't seem to do anything
  • Enable FIlters button doesn't seem to do anything
  • Text in places like grid is cut-off (letters g, y, p, etc)
  • Nice feature would be for Foreign Key columns to be displayed as a dropdown with values from the child table. Only problem is figuring out which value from the child table to display. Maybe a multi-column combobox would do the trick? Alternatively, a link could be provided which when clicked could display a popup datagrid and the user could select a record from it and upon clicking OK it would close the popup and input the ID into the Foreign Key field on the input form.
  • Use the WinForms Error Component instead of message boxes for validation
  • Foreign Key columns are sometimes nullable, but nullable is not an option in your input form (due to the NumericUpDown control).
Hope this helps. Keep up the good work!
Coordinator
Jun 27, 2013 at 3:20 PM
Edited Jun 27, 2013 at 3:27 PM
Hey, hey,

Thanks very much for this really great feedback.
  • You're right, it's one two many clicks. Reason behind using a ListBox on separate form is because I thought that there may be databases with a large number of tables and it would be easier in the listbox ... it's larger I suppose. I definitely need to add an extra button or something to make it easier to access though.
  • Reset link is a bug, thanks for spotting it.
  • Filters is an extra feature I'd like to add in the future, but decided to leave it for another day. Not removing the button is a bug though :)
  • Text cut-offs: quite a strange one as I haven't seen this on my machine. I'll have to test on another machine. What machine/OS are you using?
  • Yip, catering for foreign keys is must and it's a high priority feature. It is definitely a tricky one. I have to investigate it, but chances are that I can determine what table and field the foreign key is linked to (assuming the database has foreign key relationships defined). The way I see it is that a foreign key field would have a button/link that would open up a form for you to select a record from the parent table (who is the parent table), which would then be linked to the current record you're working with. Also maybe have an option to browse and select a child table and then select multiple children that will link to the current record. Where it would get very tricky especially is on tables that have compound primary/foreign keys. Anyway, I'd have to give it a lot of thought, but those were good ideas you gave me.
  • WinForms Error Component: good idea, I'll look into it.
  • Nullable: true, NumericUpDowns are mapped to integer (Int32) fields and you're right I haven't catered for this.
FYI: Don't know if you noticed (if you have GUIDs in your database), but GUIDs do not get displayed on the interface at all ... because they're ugly :). So blank GUID keys are generated automatically at run time and never displayed to the user. However in the FiglutDesktopDataBoxSettings.xml, there's a setting dictating which types should be hidden ... if I remember it's called HiddenTypeNames.

Thanks again for the feedback and encouragement, really appreciate it :)
Jun 29, 2013 at 8:18 AM
Edited Jun 29, 2013 at 8:18 AM
Hi,
  • The first point wasn't about the number of clicks, but the fact that the listbox is unsorted (I said combobox, but meant listbox). I know there is a filter box there and that's cool, but I still think the listbox should be sorted. When I load my database of 50+ tables, it just looks messy.
  • For text cut-offs: I am using Windows 8.
  • For Foreign Keys, I use the following SQL query:
SELECT FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
    SELECT i1.TABLE_NAME, i2.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
        i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
    WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME = '<the table name>'
ORDER BY 1,2,3,4
I actually wrote an extension method for the SqlConnection class which executes the above with a given table name and returns a list of objects containing the values in the SELECT clause.

Using this, you can easily obtain the required foreign key info.

Happy coding!
Coordinator
Jun 30, 2013 at 10:04 AM
Hey, hey,
  • Oh I see what you mean about sorting. I'll add the sorting.
  • That's strange, I'm also working on Windows 8. I'll have to check on another machine. I still have to set myself up a test server with different environments.
  • Cool, thanks a lot for that snippet, it looks like it's exactly what's needed.
Cheers,

Paul
Coordinator
Aug 2, 2013 at 4:53 PM
BTW your code snippet worked like a charm, thanks again :)