Click here to Skip to main content
Click here to Skip to main content

SQL Database Relation Builder

, 23 Aug 2004
Rate this:
Please Sign up or sign in to vote.
An article on automatically building the relationships between tables in a DataSet.

Sample Image - SQLRelationBuilder.gif

Introduction

I recently had the need to fill a DataSet with a SQL table, and all related tables, up or down. I also needed the ability to autocreate the relationships in the DataSet as they existed in SQL. I'm posting the result here for the benefit of other programmers.

Please note: using this object to load large SQL tables is not a good idea!!

Background

The SQL script for returning the relationship members for a specific table can be found in SQLScripts.resx under RelationQuiery_Down or RelationQuiery_Up.

Using the code

The snippet below assumes that you have a DataGrid called DataGrid1.

GetDataSet returns a DataSet filled with all the tables related to the table whose name was passed in as a parameter, as well as this table itself. The tables in the DataSet are named as they are in the SQL database.

   Protected Const SQL_CONNECTION_STRING As String = _
   "Server=localhost;" & _
   "DataBase=northwind;" & _
   "Integrated Security=SSPI"

   Protected DidPreviouslyConnect As Boolean = False
   Protected strConn As String = SQL_CONNECTION_STRING

   Sub CreateDataSet()
      Dim scnnNW As SqlClient.SqlConnection

      scnnNW = New SqlClient.SqlConnection(strConn)
      scnnNW.Open()

      'Create a new instance of the relation builder
      Dim rb As New Level54.Data.SQLClient.SQLRelationBuilder(scnnNW)

      DataGrid1.DataSource = rb.GetDataSet("Employees", _
             Data.SQLClient.RelationDirection.Down, -1)
      DataGrid1.DataMember = "Employees"
   End Sub

History

Version 1.0.1690.32421: released.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Charl Pohlmann
Engineer
South Africa South Africa
I'm a mechanical engineer working in the pipe manufacturing business, although programming has been my first love for the past twelve years.
 
My languages of choice are VB.NET, VB6 and C#, in that order. I took last year off from programming, as me and my family moved into a new house, and there were things to be done. When I started up again, I discovered that not only had my .NET abilities gone rusty, but also my VB6 abilities.
 
Anyway, I love programming. I think .NET is the best thing since sliced bread (coming from VB6).

Comments and Discussions

 
GeneralLinks are now working PinmemberCharl Pohlmann24-Aug-04 0:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140821.2 | Last Updated 24 Aug 2004
Article Copyright 2004 by Charl Pohlmann
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid