Solved

VB.net automatically detect SQL table relationships

Posted on 2014-11-15
7
190 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Code enhancement 4 20
reccommendations for a free msft sql query manager? 4 26
What is this datetime? 1 18
job schedule 8 16
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

809 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