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.