SQL Database Relation Builder

• Download source - 22.7 Kb
• Download binary and help file - 20.4 Kb

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.