C# (WPF): Database Relationship Viewer

Question

I would like to create a WPF application in C# that can show visually, in a user interface friendly way, the relationships between tables from a database chosen from the application user (MS SQL Server, MS Access, Oracle, MySQL, etc.) and allow the editing of the relationships.

For example, in Microsoft Access 2007 it is possible to do such a thing by having at least 2 tables and clicking on the Database Tools tab and then on Relationships. Another example is in Microsoft SQL Server when creating a diagram of the database.

The first step would be to create a data access layer to allow the connection to different types of databases (depending on the provider type).

I am not aware if there is already some sort of viewer in .NET (or maybe a free third party library) that could do the trick. Do you know one? If not what are the big lines for doing it manually with WPF?

Thank you for any help and suggestions!

Answer 1

There is a viewer of this sort built into visual studio, go to Tools-> Connect to Database, and get it all hooked up to the database. Then when you see the database on the Server Explorer, open a table and look at the different buttons in your toolbar, there's one that will bring the table up as a little window on a grey background. Once you've got that window open, you can drag/drop other tables into the gray area and it will show you all there relationships.

Answer 2

You could maybe have a look at the MS Office PIAs for MS Access 2007 and see if you can find the viewer you are talking about...

Answer 3

There's a good number of 'Big Ticket' items here, but we'll start from the core functionality.

DB Tasks: Enumerating the Tables For each table- enumerate relationships. Abstract above functionality for each DB type you wish to deal with.

UI Tasks: Lay out tables (simple same size boxes) draw lines for each relationship Implement Drag & Drop - redraw relationship lines on drop.

Now, this doesn't deal with nice curved lines, and routing those lines nicely It also doesn't deal with the table columns, so all the table 'boxes' in the UI are the same size, which simplifies layout.

But this level of functionality is a good goal as a proof of concept.

If this is for a client, in a professional capacity, dont waste your time on it.

However if it is for the learning experience then go for it! There's lots of nice nuggets to be gained from a project like this that you will use again and again in your programming career.

hope this helps!

Answer 4

Reading the foreign key relationships may be non-trivial. My application includes the following comment:

//get the list of all foreign keys
//unfortunately GetSchema doesn't return the column definitions
//http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=741870&SiteID=1 suggests getting this
//information from the the sys.foreign_keys catalog view joined with sys.objects (for SQL 2005)
//or from the sysforeignkeys and sysobjects tables in SQL 2000).

Answer 5

It's not free, but for any WPF diagramming I would always use Mindscape's diagramming product WPF Flow Diagrams. The product is great and the support is superb.