Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB.net automatically detect SQL table relationships

Posted on 2014-11-15
7
Medium Priority
?
221 Views
Last Modified: 2014-11-16
Hi

I have a SQL database where relationships have been set up between all the tables.
I want to allow the user of my VB.net 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?
0
Comment
Question by:Murray Brown
[X]
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
7 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40444429
0
 
LVL 64

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 VB.net 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.
0
 

Author Comment

by:Murray Brown
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 ADO.net. My database is a sql server 2008 database
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Murray Brown
ID: 40445225
So what is the best way to pull multiple joined table data through in VB.net. I also want my users to be able to name and save a view for each scenario
0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 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
0
 

Author Closing Comment

by:Murray Brown
ID: 40445742
Thanks very much Fernando. I appreciate the help
Murray
0
 
LVL 64

Expert Comment

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

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

688 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