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, _
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
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: