Solved automatically detect SQL table relationships

Posted on 2014-11-15
Last Modified: 2014-11-16

I have a SQL database where relationships have been set up between all the tables.
I want to allow the user of my app to select columns from multiple tables, where
the table relationships are automatically detected and used in the code to pull the data
through to a DataGridView. How do I achieve this? Do I use a DataSet?
Question by:murbro
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 24

Expert Comment

by:Phillip Burton
ID: 40444429
LVL 63

Expert Comment

by:Fernando Soto
ID: 40444430
First you need to decide on which technology you will be using to access the database? For example Standard ADO .Net, Linq to Sql or Linq to Entity Framework?

Which SQL server are you using?

To your statement, "I want to allow the user of my app to select columns from multiple tables, where
the table relationships are automatically detected and used in the code to pull the data
through to a DataGridView", this is not done automatically for you. You will need to build a SQL statement for each request that will return the needed fields / columns back from the desired tables.

Author Comment

ID: 40444535
Hi Phillip. Thanks for referencing my other question. I hadn't realised that table relations could be added to a dataset until today so I am happy to find that there is a a different route
Hi Fernando. I am using the sqlclient namespace in I think My database is a sql server 2008 database
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Author Comment

ID: 40445225
So what is the best way to pull multiple joined table data through in I also want my users to be able to name and save a view for each scenario
LVL 63

Accepted Solution

Fernando Soto earned 500 total points
ID: 40445730
As I stated earlier there is no programming option that will do that automatically for you. Seeming that you want to allow the user to pick and choose which columns to download from the tables, joined or not, you will need to formulate a SQL statement that will query the database and bring down the wanted information, which will not be an easy feat. This means that the application must have a very good knowledge of the database structure, for example what columns are in what table they are in and if multiple tables are involved how to join the two between the two or more tables.

Here is a class that will help to do what you need a little easier and can be found on GitHub, DbExtensions SqlBuilder. I have not used this code before an so I can not say how well it performs. I understand that this code can be downloaded into your application using NuGet

Author Closing Comment

ID: 40445742
Thanks very much Fernando. I appreciate the help
LVL 63

Expert Comment

by:Fernando Soto
ID: 40445747
Not a problem Murray, glad to help.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question