Solved

VB.net automatically detect SQL table relationships

Posted on 2014-11-15
7
209 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:murbro
[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 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 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:murbro
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:murbro
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 63

Accepted Solution

by:
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
0
 

Author Closing Comment

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

Expert Comment

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

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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