Follow @RoyOsherove on Twitter

Automatically Create DataRelations Based On OleDB Schema

I was frustrated about the lack of automatic support for DataRelation creation via the DataAdapters, and the less-than-friendly OleDBConnection.GetOleDbSchemaTable() method, so I've decided to create a small class, which does what I needed.

It's not refactored or optimized (it's pretty slow), and there are several big features missing, but it’s a start.

Mind you, this only works with OLE DB connections...


What it does:

·         accepts a Dataset and an OleDBConnection object

·         Retrieves the schema containing all the foreign keys using that connection

·         Creates missing tables that are found in the foreign keys schema(along with primary keys and such)

·         Creates DataRelations to represent the foreign keys in the schema


Here's the code (I welcome all comments, this is just a start):

Imports System.Text

Imports System.Data

Imports System.Data.OleDb


Public Class MatchMaker


    Public Shared Sub InferRelations(ByVal ds As DataSet, _

ByVal cn As OleDbConnection)

        Dim row As DataRow

        Dim adp As New OleDbDataAdapter("", cn)


        'we can put restrictions on the data we want to recieve

        Dim restrictions As Object() = _

New Object() {Nothing, _

Nothing, _

Nothing, _



        'this is the Foreign key data we need to create relations

        Dim schemaTable As DataTable = _

cn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, _



        'go through all the foreign keys displayed

        For Each row In schemaTable.Rows

            Dim strTable As String = row("PK_TABLE_NAME")

            Dim strChild As String = row("FK_TABLE_NAME")

            Dim strParentColName As String = row("PK_COLUMN_NAME")

            Dim strChildColName As String = row("FK_COLUMN_NAME")


            'the relation name that will be created

            Dim strRelationName As String = _

strTable + "_" + strParentColName + _

"_" + strChild + "_" + strChildColName


            'create any non existing tables along with key information

            CreateAndfFillSchema(ds, adp, strTable)

            CreateAndfFillSchema(ds, adp, strChild)



                Dim PrimaryColumn As DataColumn = _



                Dim ChildColumn As DataColumn = _



                ds.Relations.Add(strRelationName, PrimaryColumn, ChildColumn)

            Catch e As Exception

                'well get here if there's already such as relation

                'but we want to continue anyway

            End Try


    End Sub


    Private Shared Sub CreateAndfFillSchema(ByVal ds As DataSet, _

ByVal adp As OleDbDataAdapter, _

ByVal TableName As String)

        If Not ds.Tables.Contains(TableName) Then

            adp.SelectCommand.CommandText = "SELECT * FROM " + TableName

            adp.FillSchema(ds, SchemaType.Source, TableName)

        End If

    End Sub

End Class


What's missing:

·         Support for multi-column primary keys relations

·         Optimized FillSchema functionality – I think that copying the functionality of FillSchema to create primary keys and so on within the code instead of calling FillSchema will speed things us considerably

·         Support for Types of relation update and delete functionality(Cascaded ,none and so on) to be read from the schema

·         Probably more stuff.



Wasted Years

User Groups