Google
WWW Yariv Hammer's Code Site

Monday, August 28, 2006

Loading the Entire Database Into a DataSet, Including Relations

Introduction
Ironically, it is hard to load the entire database into a dataset. You either need to use many command objects, or TableAdapters/DataAdapters.
The ralations are even harder to get from the database. You need to manually create them on the DataSet.

In this article I give code to load the entire MS-Access database into an Untyped DataSet, with the relations. You can easily adapt this to SQL Server database. The code is in VB.NET.

The Code
----------------------------------------------
Function getDataSetAndFill(ByRef connection As OleDb.OleDbConnection, Optional ByVal isExportSchema As Boolean = True) As DataSet
Dim myDataSet As New DataSet
Dim myCommand As New OleDb.OleDbCommand
Dim myAdapter As New OleDb.OleDbDataAdapter
myCommand.Connection = connection
'Get Database Tables
Dim tables As DataTable = connection.GetOleDbSchemaTable( System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
'iterate through all tables
Dim table As DataRow
For Each table In tables.Rows
'get current table's name
Dim tableName As String = table("TABLE_NAME")
Dim strSQL = "SELECT * FROM " & "[" & tableName & "]"
Dim adapter1 As New OleDb.OleDbDataAdapter(New OleDb.OleDbCommand(strSQL, connection))
adapter1.FillSchema(myDataSet, SchemaType.Source, tableName)
'Fill the table in the dataset
myCommand.CommandText = strSQL
myAdapter.SelectCommand = myCommand
myAdapter.Fill(myDataSet, tableName)
Next

'Add relationships to dataset
'First, get relationships names from database (as well as parent table and child table names)
Dim namesQuery As String = "SELECT DISTINCT szRelationship, szReferencedObject, szObject FROM MSysRelationships"
Dim namesCommand As New System.Data.OleDb.OleDbCommand(namesQuery, connection)
Dim namesAdapter As New System.Data.OleDb.OleDbDataAdapter(namesCommand)
Dim namesDataTable As New DataTable
mesAdapter.Fill(namesDataTable)
'Now, get MSysRelationship from database
Dim relationsQuery As String = "SELECT * FROM MSysRelationships"
Dim command As New System.Data.OleDb.OleDbCommand(relationsQuery, connection)
Dim adapter As New System.Data.OleDb.OleDbDataAdapter(command)
Dim relationsDataTable As New DataTable
adapter.Fill(relationsDataTable)
Dim relationsView As DataView = relationsDataTable.DefaultView
Dim relationName As String
Dim parentTableName As String
Dim childTablename As String
Dim row As DataRow
For Each relation As DataRow In namesDataTable.Rows
relationName = relation("szRelationship")
parentTableName = relation("szReferencedObject")
childTablename = relation("szObject")
'Keep only the record of the current relationship
relationsView.RowFilter = "szRelationship = '" & relationName & "'"
'Declare two arrays for parent and child columns arguments
Dim parentColumns(relationsView.Count - 1) As DataColumn
Dim childColumns(relationsView.Count - 1) As DataColumn
For i As Integer = 0 To relationsView.Count - 1
parentColumns(i) = myDataSet.Tables(parentTableName). Columns(relationsView.Item(i)("szReferencedColumn"))
childColumns(i) = myDataSet.Tables(childTablename). Columns(relationsView.Item(i)("szColumn"))
Next
Dim newRelation As New DataRelation(relationName, parentColumns, childColumns, False)
myDataSet.Relations.Add(newRelation)
Next
If isExportSchema Then
Dim schemaName = GetXmlSchemaFileName()
If File.Exists(schemaName) Then File.SetAttributes(schemaName, FileAttributes.Normal)
myDataSet.WriteXmlSchema(schemaName)
End If
Return myDataSet
End Function
------------------------------------------

3 Comments:

At 7:30 PM, May 17, 2013, Blogger jdavidb0216 said...

Hello, Excellent work on this segment of code. Do you have any example applications where it is used?

I want to see how to implement it so that i can adapt it to a project i am working on. Thank you!

 
At 1:29 AM, January 18, 2016, Blogger wdmtest said...

VB.Net
Introduction
Why VB .NET?
What Is VB .NET?
What Can You Do with VB .NET?
Variables and Data Types
Variables
Declaring Variables and Constants
Data Types
Arrays

 
At 2:43 AM, June 27, 2017, Blogger kingrani said...

Flipkart is Offering Cashback Offers Flipkart SBI Cashback Offer You will get upto 75% Discount

Flipkart is Offering Cashback Offers Flipkart AXIS Cashback Offer You will get upto 75% Discount

Flipkart is Offering Cashback Offers Flipkart YES Cashback Offer You will get upto 75% Discount

Flipkart is Offering Cashback Offers Flipkart Phonepe Cashback Offer You will get upto 75% Discount

Flipkart is one of the biggest world wide eCommerce Site flipkart cashback offersso

Flipkart is one of the biggest world wide eCommerce Site Flipkart hdfc offersso

 

Post a Comment

<< Home

Feel free to use everything here. Add links to my site if you wish.

Do not copy anything to other sites without adding link to here.

All the contents of the site belong to Yariv Hammer.