---
title: "Automatically Create DataRelations Based On OleDB Schema"
url: "https://osherove.com/blog/2003/7/1/automatically-create-datarelations-based-on-oledb-schema.md"
canonical: "https://osherove.com/blog/2003/7/1/automatically-create-datarelations-based-on-oledb-schema.html"
published: "2003-07-01T09:59:00"
updated: "2003-07-01T09:59:00"
tags: [".Net Original"]
author: "Roy Osherove"
description: "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,…"
usage: "Licensed under CC BY 4.0 — reuse, quote and adapt with attribution (Roy Osherove, link to the original page)."
license: "https://creativecommons.org/licenses/by/4.0/"
---

# Automatically Create DataRelations Based On OleDB Schema

By Roy Osherove July 1, 2003 In [.Net Original](https://osherove.com/blog/category/net-original)

**Historical article** · Originally published July 1, 2003; technical details may be outdated.

I was frustrated about the [lack of automatic support for DataRelation creation via the DataAdapters](http://weblogs.asp.net/rosherove/posts/9446.aspx), 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, \_

Nothing}

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

        Dim schemaTable As DataTable = \_

cn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign\_Keys, \_

restrictions)

        '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)

            Try

                Dim PrimaryColumn As DataColumn = \_

ds.Tables(strTable).Columns(strParentColName)

                Dim ChildColumn As DataColumn = \_

ds.Tables(strChild).Columns(strChildColName)

                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

        Next

    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

---

[](http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q309488) 

**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.

 **Resources:**

- [HOW TO: Retrieve Schema Information by Using GetOleDbSchemaTable and Visual Basic .NET](http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q309488)

## More in .Net Original

[All posts in .Net Original](https://osherove.com/blog/category/net-original)

- Jul 3, 2003

    ### [A much needed SchemaHelper class](https://osherove.com/blog/2003/7/3/a-much-needed-schemahelper-class-1.html)

- Jul 1, 2003

    ### [Why I write in Both C# and VB.NET](https://osherove.com/blog/2003/7/1/why-i-write-in-both-c-and-vbnet.html)

- Jul 1, 2003

    ### [More about .Net Deep Dive](https://osherove.com/blog/2003/7/1/more-about-net-deep-dive.html)

- Jun 30, 2003

    ### [.Net Deep Dive Recap](https://osherove.com/blog/2003/6/30/net-deep-dive-recap.html)


---

## About the author

**Roy Osherove**: Senior Solutions Architect at AWS working on AI for production and AI-driven development; author of The Art of Unit Testing and Elastic Leadership, with about 20 years of consulting and training on unit testing, TDD, team leadership and continuous delivery.

### Sites

- [RobotPaper](https://robotpaper.ai/): This blog: AI patterns and anti-patterns, AI and coding agents, and engineering leadership.
- [osherove.com](https://osherove.com): Roy's personal site. Author of The Art of Unit Testing and Elastic Leadership; consulting and training on unit testing, TDD, team leadership and continuous delivery, plus about 20 years of blog posts.
- [The Art of Unit Testing](https://www.artofunittesting.com): The Art of Unit Testing, 3rd Edition (Manning): readable, maintainable, trustworthy unit tests, plus TDD workshops in C#, JavaScript, Java and Kotlin.
- [5whys.com](https://www.5whys.com): Roy's writing on software team leadership: self-organizing teams, surviving chaos, learning mode and the Elastic Leadership model.
- [Pipeline Driven](https://pipelinedriven.org): Continuous delivery and DevOps practices: build patterns, pipeline-friendly testing, DevOps metrics and enterprise DevOps.
- [Elastic Leadership](https://www.elasticleadership.com): Elastic Leadership, Roy's framework for software team leaders (survival, learning and self-organizing modes).

### Social

- X: https://x.com/royosherove
- LinkedIn: https://www.linkedin.com/in/osherove/

### Contact

- Contact form: https://forms.gle/jQSx5KoXvrVA1JCK7

### License

Licensed under CC BY 4.0 — reuse, quote and adapt with attribution (Roy Osherove, link to the original page). License: https://creativecommons.org/licenses/by/4.0/