Imports System.Data.OleDb
Imports System.IO

Public Class MainForm
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents btnClearAll As System.Windows.Forms.Button
    Friend WithEvents btnFillCategoriesProducts As System.Windows.Forms.Button
    Friend WithEvents label2 As System.Windows.Forms.Label
    Friend WithEvents gridUnboundChild As C1.Win.C1TrueDBGrid.C1TrueDBGrid
    Friend WithEvents gridUnboundParent As C1.Win.C1TrueDBGrid.C1TrueDBGrid
    Friend WithEvents label1 As System.Windows.Forms.Label
    Friend WithEvents btnFillSuppliers As System.Windows.Forms.Button
    Friend WithEvents gridBound As C1.Win.C1TrueDBGrid.C1TrueDBGrid
    Friend WithEvents btnFillCustomersOrders As System.Windows.Forms.Button
    Friend WithEvents btnClose As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(MainForm))
        Me.btnClearAll = New System.Windows.Forms.Button()
        Me.btnFillCategoriesProducts = New System.Windows.Forms.Button()
        Me.label2 = New System.Windows.Forms.Label()
        Me.gridUnboundChild = New C1.Win.C1TrueDBGrid.C1TrueDBGrid()
        Me.gridUnboundParent = New C1.Win.C1TrueDBGrid.C1TrueDBGrid()
        Me.label1 = New System.Windows.Forms.Label()
        Me.btnFillSuppliers = New System.Windows.Forms.Button()
        Me.gridBound = New C1.Win.C1TrueDBGrid.C1TrueDBGrid()
        Me.btnFillCustomersOrders = New System.Windows.Forms.Button()
        Me.btnClose = New System.Windows.Forms.Button()
        CType(Me.gridUnboundChild, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.gridUnboundParent, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.gridBound, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'btnClearAll
        '
        Me.btnClearAll.Location = New System.Drawing.Point(592, 12)
        Me.btnClearAll.Name = "btnClearAll"
        Me.btnClearAll.TabIndex = 12
        Me.btnClearAll.Text = "Clear All"
        '
        'btnFillCategoriesProducts
        '
        Me.btnFillCategoriesProducts.Location = New System.Drawing.Point(316, 12)
        Me.btnFillCategoriesProducts.Name = "btnFillCategoriesProducts"
        Me.btnFillCategoriesProducts.Size = New System.Drawing.Size(268, 23)
        Me.btnFillCategoriesProducts.TabIndex = 11
        Me.btnFillCategoriesProducts.Text = "Fill with Products-Categories (Composite) Table"
        '
        'label2
        '
        Me.label2.Location = New System.Drawing.Point(16, 208)
        Me.label2.Name = "label2"
        Me.label2.Size = New System.Drawing.Size(252, 16)
        Me.label2.TabIndex = 15
        Me.label2.Text = "Unbound tables with master-detail relationship:"
        '
        'gridUnboundChild
        '
        Me.gridUnboundChild.AllowFilter = True
        Me.gridUnboundChild.AllowRowSizing = C1.Win.C1TrueDBGrid.RowSizingEnum.AllRows
        Me.gridUnboundChild.AllowSort = True
        Me.gridUnboundChild.CaptionHeight = 17
        Me.gridUnboundChild.CollapseColor = System.Drawing.Color.Black
        Me.gridUnboundChild.DataChanged = False
        Me.gridUnboundChild.BackColor = System.Drawing.Color.Empty
        Me.gridUnboundChild.ExpandColor = System.Drawing.Color.Black
        Me.gridUnboundChild.Images.Add(CType(resources.GetObject("resource.Images"), System.Drawing.Bitmap))
        Me.gridUnboundChild.Location = New System.Drawing.Point(36, 248)
        Me.gridUnboundChild.MarqueeStyle = C1.Win.C1TrueDBGrid.MarqueeEnum.DottedCellBorder
        Me.gridUnboundChild.Name = "gridUnboundChild"
        Me.gridUnboundChild.PreviewInfo.Location = New System.Drawing.Point(0, 0)
        Me.gridUnboundChild.PreviewInfo.Size = New System.Drawing.Size(0, 0)
        Me.gridUnboundChild.PreviewInfo.ZoomFactor = 75
        Me.gridUnboundChild.RecordSelectorWidth = 16
        Me.gridUnboundChild.RowDivider.Color = System.Drawing.Color.DarkGray
        Me.gridUnboundChild.RowDivider.Style = C1.Win.C1TrueDBGrid.LineStyleEnum.Single
        Me.gridUnboundChild.RowHeight = 15
        Me.gridUnboundChild.RowSubDividerColor = System.Drawing.Color.DarkGray
        Me.gridUnboundChild.ScrollTips = False
        Me.gridUnboundChild.Size = New System.Drawing.Size(612, 134)
        Me.gridUnboundChild.TabIndex = 17
        Me.gridUnboundChild.TabStop = False
        Me.gridUnboundChild.Text = "c1TrueDBGrid3"
        Me.gridUnboundChild.PropBag = CType(resources.GetObject("gridUnboundChild.PropBag"), String)
        '
        'gridUnboundParent
        '
        Me.gridUnboundParent.AllowFilter = True
        Me.gridUnboundParent.AllowRowSizing = C1.Win.C1TrueDBGrid.RowSizingEnum.AllRows
        Me.gridUnboundParent.AllowSort = True
        Me.gridUnboundParent.CaptionHeight = 17
        Me.gridUnboundParent.ChildGrid = Me.gridUnboundChild
        Me.gridUnboundParent.CollapseColor = System.Drawing.Color.Black
        Me.gridUnboundParent.DataChanged = False
        Me.gridUnboundParent.BackColor = System.Drawing.Color.Empty
        Me.gridUnboundParent.ExpandColor = System.Drawing.Color.Black
        Me.gridUnboundParent.Images.Add(CType(resources.GetObject("resource.Images1"), System.Drawing.Bitmap))
        Me.gridUnboundParent.Location = New System.Drawing.Point(15, 228)
        Me.gridUnboundParent.MarqueeStyle = C1.Win.C1TrueDBGrid.MarqueeEnum.DottedCellBorder
        Me.gridUnboundParent.Name = "gridUnboundParent"
        Me.gridUnboundParent.PreviewInfo.Location = New System.Drawing.Point(0, 0)
        Me.gridUnboundParent.PreviewInfo.Size = New System.Drawing.Size(0, 0)
        Me.gridUnboundParent.PreviewInfo.ZoomFactor = 75
        Me.gridUnboundParent.RecordSelectorWidth = 16
        Me.gridUnboundParent.RowDivider.Color = System.Drawing.Color.DarkGray
        Me.gridUnboundParent.RowDivider.Style = C1.Win.C1TrueDBGrid.LineStyleEnum.Single
        Me.gridUnboundParent.RowHeight = 15
        Me.gridUnboundParent.RowSubDividerColor = System.Drawing.Color.DarkGray
        Me.gridUnboundParent.ScrollTips = False
        Me.gridUnboundParent.Size = New System.Drawing.Size(652, 166)
        Me.gridUnboundParent.TabIndex = 16
        Me.gridUnboundParent.Text = "c1TrueDBGrid2"
        Me.gridUnboundParent.PropBag = CType(resources.GetObject("gridUnboundParent.PropBag"), String)
        '
        'label1
        '
        Me.label1.Location = New System.Drawing.Point(16, 26)
        Me.label1.Name = "label1"
        Me.label1.Size = New System.Drawing.Size(88, 16)
        Me.label1.TabIndex = 13
        Me.label1.Text = "Bound table:"
        '
        'btnFillSuppliers
        '
        Me.btnFillSuppliers.Location = New System.Drawing.Point(120, 12)
        Me.btnFillSuppliers.Name = "btnFillSuppliers"
        Me.btnFillSuppliers.Size = New System.Drawing.Size(188, 23)
        Me.btnFillSuppliers.TabIndex = 10
        Me.btnFillSuppliers.Text = "Fill with Suppliers (Simple) Table"
        '
        'gridBound
        '
        Me.gridBound.AllowFilter = True
        Me.gridBound.AllowRowSizing = C1.Win.C1TrueDBGrid.RowSizingEnum.AllRows
        Me.gridBound.AllowSort = True
        Me.gridBound.CaptionHeight = 17
        Me.gridBound.CollapseColor = System.Drawing.Color.Black
        Me.gridBound.DataChanged = False
        Me.gridBound.BackColor = System.Drawing.Color.Empty
        Me.gridBound.ExpandColor = System.Drawing.Color.Black
        Me.gridBound.Images.Add(CType(resources.GetObject("resource.Images2"), System.Drawing.Bitmap))
        Me.gridBound.Location = New System.Drawing.Point(15, 46)
        Me.gridBound.MarqueeStyle = C1.Win.C1TrueDBGrid.MarqueeEnum.DottedCellBorder
        Me.gridBound.Name = "gridBound"
        Me.gridBound.PreviewInfo.Location = New System.Drawing.Point(0, 0)
        Me.gridBound.PreviewInfo.Size = New System.Drawing.Size(0, 0)
        Me.gridBound.PreviewInfo.ZoomFactor = 75
        Me.gridBound.RecordSelectorWidth = 16
        Me.gridBound.RowDivider.Color = System.Drawing.Color.DarkGray
        Me.gridBound.RowDivider.Style = C1.Win.C1TrueDBGrid.LineStyleEnum.Single
        Me.gridBound.RowHeight = 15
        Me.gridBound.RowSubDividerColor = System.Drawing.Color.DarkGray
        Me.gridBound.ScrollTips = False
        Me.gridBound.Size = New System.Drawing.Size(652, 150)
        Me.gridBound.TabIndex = 14
        Me.gridBound.Text = "c1TrueDBGrid1"
        Me.gridBound.PropBag = CType(resources.GetObject("gridBound.PropBag"), String)
        '
        'btnFillCustomersOrders
        '
        Me.btnFillCustomersOrders.Location = New System.Drawing.Point(16, 406)
        Me.btnFillCustomersOrders.Name = "btnFillCustomersOrders"
        Me.btnFillCustomersOrders.Size = New System.Drawing.Size(312, 23)
        Me.btnFillCustomersOrders.TabIndex = 18
        Me.btnFillCustomersOrders.Text = "Fill with Customers and Orders Data (SQL-based Tables)"
        '
        'btnClose
        '
        Me.btnClose.Location = New System.Drawing.Point(592, 406)
        Me.btnClose.Name = "btnClose"
        Me.btnClose.TabIndex = 19
        Me.btnClose.Text = "Close"
        '
        'MainForm
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 14)
        Me.ClientSize = New System.Drawing.Size(682, 441)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.btnClearAll, Me.btnFillCategoriesProducts, Me.label2, Me.gridUnboundChild, Me.gridUnboundParent, Me.label1, Me.btnFillSuppliers, Me.gridBound, Me.btnFillCustomersOrders, Me.btnClose})
        Me.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
        Me.MaximizeBox = False
        Me.Name = "MainForm"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = " ComponentOne DataObjects - Programmatic Schema Composition"
        CType(Me.gridUnboundChild, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.gridUnboundParent, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.gridBound, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Close()
    End Sub

    'Connection string
    Private defaultConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
     "Data Source=C:\Program Files\ComponentOne Studio.NET 2.0\common\NWIND.MDB;" + _
     "Persist Security Info=False"

#Region " Switch to C1NWind.mdb database "

    Private Function GetConnectionString() As String
        Dim dbPath As String = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + _
            "\ComponentOne Samples\Common\C1NWind.mdb"
        If Not File.Exists(dbPath) Then
            Return defaultConnectionString
        End If
        Dim csb As New OleDbConnectionStringBuilder(defaultConnectionString)
        csb.DataSource = dbPath
        Return csb.ToString()
    End Function

#End Region

    'Retrieve composite field data from the underlying table field
    Private Sub AddCompositeField(ByVal fieldName As String, ByVal compositeTable As C1.Data.SchemaObjects.CompositeTable, _
    ByVal tableView As C1.Data.SchemaObjects.TableView, ByVal table As C1.Data.SchemaObjects.Table)
        Dim field As C1.Data.SchemaObjects.CompositeTableField = New C1.Data.SchemaObjects. _
         CompositeTableField(compositeTable)
        field.TableViewField = tableView.Fields(fieldName)
        field.AllowDbNull = table.Fields(fieldName).AllowDbNull
        field.Name = table.Fields(fieldName).Name
        field.Type = table.Fields(fieldName).Type
    End Sub

    'Adjust width of grid columns
    Private Sub SetColumnsWidth(ByVal grid As C1.Win.C1TrueDBGrid.C1TrueDBGrid, ByVal width_arr() As Int32)
        Dim i As Int32
        For i = 0 To width_arr.Length - 1
            grid.Splits(0).DisplayColumns(i).Width = width_arr(i)
        Next
    End Sub

    'Apply a proper Date format to the grid column
    Private Sub AdjustDateColumn(ByVal grid As C1.Win.C1TrueDBGrid.C1TrueDBGrid, _
    ByVal columnName As String)
        grid.Columns(columnName).NumberFormat = "Medium Date"
        grid.Splits(0).DisplayColumns(columnName).Style. _
         HorizontalAlignment = C1.Win.C1TrueDBGrid.AlignHorzEnum.Center
        grid.Splits(0).DisplayColumns(columnName).HeadingStyle. _
         HorizontalAlignment = C1.Win.C1TrueDBGrid.AlignHorzEnum.Center
    End Sub


    Private Sub btnFillSuppliers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFillSuppliers.Click
        Dim schemaDef As C1.Data.C1SchemaDef
        Dim connection As C1.Data.SchemaObjects.C1OleDbConnection
        Dim dbTable As C1.Data.SchemaObjects.DbTable
        Dim dataSetDef As C1.Data.SchemaObjects.DataSetDef
        Dim tableView As C1.Data.SchemaObjects.TableView
        Dim dataSet As C1.Data.C1DataSet

        'Create main schema
        schemaDef = New C1.Data.C1SchemaDef()

        'Create a connection
        connection = New C1.Data.SchemaObjects.C1OleDbConnection(schemaDef.Schema)
        connection.ConnectionString = GetConnectionString()
        connection.Name = "Connection"

        'Create simple table Suppliers
        dbTable = New C1.Data.SchemaObjects.DbTable(schemaDef.Schema)
        dbTable.DbTableName = "Suppliers"
        dbTable.Name = dbTable.DbTableName
        dbTable.Connection = connection
        CreateSupplierFields(dbTable)

        'Create DataSetDef object
        dataSetDef = New C1.Data.SchemaObjects.DataSetDef(schemaDef.Schema)
        dataSetDef.Name = "DataSet"

        'Create TableView object
        tableView = New C1.Data.SchemaObjects.TableView(dataSetDef, dbTable)
        tableView.Name = "Suppliers"
        tableView.RetrieveFields()

        'Create C1DataSet (to which a grid is bound)
        dataSet = New C1.Data.C1DataSet()
        dataSet.SchemaDef = schemaDef
        dataSet.DataSetDef = "DataSet"
        dataSet.Fill(True)

        'Unbind the grid from current data source
        gridBound.DataMember = ""
        gridBound.DataSource = Nothing
        gridBound.ClearFields()

        'Bind to Suppliers simple table
        gridBound.DataMember = "Suppliers"
        gridBound.DataSource = dataSet

        'Adjust columns width
        SetColumnsWidth(gridBound, New Int32() {70, 180, 130, 150, 80})
    End Sub

    Private Sub btnFillCategoriesProducts_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFillCategoriesProducts.Click
        Dim schemaDef As C1.Data.C1SchemaDef
        Dim connection As C1.Data.SchemaObjects.C1OleDbConnection
        Dim dbTableCategories As C1.Data.SchemaObjects.DbTable
        Dim dbTableProducts As C1.Data.SchemaObjects.DbTable
        Dim simpleRelation As C1.Data.SchemaObjects.SimpleRelation
        Dim joinCondition As C1.Data.SchemaObjects.JoinCondition
        Dim compositeTable As C1.Data.SchemaObjects.CompositeTable
        Dim tableViewCategories As C1.Data.SchemaObjects.TableView
        Dim tableViewProducts As C1.Data.SchemaObjects.TableView
        Dim viewRelation As C1.Data.SchemaObjects.ViewRelation
        Dim dataSetDef As C1.Data.SchemaObjects.DataSetDef
        Dim tableView As C1.Data.SchemaObjects.TableView
        Dim dataSet As C1.Data.C1DataSet

        'Create Schema (C1SchemaDef) object
        schemaDef = New C1.Data.C1SchemaDef()

        'Create a connection
        connection = New C1.Data.SchemaObjects.C1OleDbConnection(schemaDef.Schema)
        connection.ConnectionString = GetConnectionString()
        connection.Name = "Connection"

        'Create simple table Categories
        dbTableCategories = New C1.Data.SchemaObjects.DbTable(schemaDef.Schema)
        dbTableCategories.DbTableName = "Categories"
        dbTableCategories.Name = dbTableCategories.DbTableName
        dbTableCategories.Connection = connection
        CreateCategoriesFields(dbTableCategories)

        'Create simple table Products
        dbTableProducts = New C1.Data.SchemaObjects.DbTable(schemaDef.Schema)
        dbTableProducts.DbTableName = "Products"
        dbTableProducts.Name = dbTableProducts.DbTableName
        dbTableProducts.Connection = connection
        CreateProductsFields(dbTableProducts)

        'Establish a simple relation
        simpleRelation = New C1.Data.SchemaObjects.SimpleRelation(dbTableCategories, dbTableProducts)
        simpleRelation.Name = "Categories - Products"
        joinCondition = New C1.Data.SchemaObjects.JoinCondition(simpleRelation.Joins)
        joinCondition.ParentField = dbTableCategories.Fields("CategoryID")
        joinCondition.ChildField = dbTableProducts.Fields("CategoryID")

        'Create a composite table
        compositeTable = New C1.Data.SchemaObjects.CompositeTable(schemaDef.Schema)
        compositeTable.Name = "CategoriesProducts"

        'Create TableView for Categories table
        tableViewCategories = New C1.Data.SchemaObjects.CompositeDefView(compositeTable.CompositeTableDef, dbTableCategories)
        tableViewCategories.Name = "Categories"
        tableViewCategories.RetrieveFields()

        'Create TableView for Products table
        tableViewProducts = New C1.Data.SchemaObjects.CompositeDefView(compositeTable.CompositeTableDef, dbTableProducts)
        tableViewProducts.Name = "Products"
        tableViewProducts.RetrieveFields()

        'Create ViewRelation between the TableViews
        viewRelation = New C1.Data.SchemaObjects.CompositeDefRelation(compositeTable.CompositeTableDef, simpleRelation)
        viewRelation.Name = simpleRelation.Name
        viewRelation.Parent = tableViewProducts
        viewRelation.Child = tableViewCategories

        'Create fields in the composite table (ProductID field is already here)
        'AddCompositeField("ProductID", compositeTable, tableViewProducts, dbTableProducts)
        AddCompositeField("CategoryID", compositeTable, tableViewProducts, dbTableProducts)
        AddCompositeField("ProductName", compositeTable, tableViewProducts, dbTableProducts)
        AddCompositeField("CategoryName", compositeTable, tableViewCategories, dbTableCategories)
        AddCompositeField("Discontinued", compositeTable, tableViewProducts, dbTableProducts)
        AddCompositeField("UnitPrice", compositeTable, tableViewProducts, dbTableProducts)
        AddCompositeField("QuantityPerUnit", compositeTable, tableViewProducts, dbTableProducts)

        'Create DataSetDef object
        dataSetDef = New C1.Data.SchemaObjects.DataSetDef(schemaDef.Schema)
        dataSetDef.Name = "DataSet"

        'Create TableView for the whole composite table
        tableView = New C1.Data.SchemaObjects.TableView(dataSetDef, compositeTable)
        tableView.Name = "CategoriesProducts"
        tableView.RetrieveFields()

        'Create C1DataSet to bind to
        dataSet = New C1.Data.C1DataSet()
        dataSet.SchemaDef = schemaDef
        dataSet.DataSetDef = "DataSet"
        dataSet.Fill(True)

        'Unbind the grid from current data source
        gridBound.DataMember = ""
        gridBound.DataSource = Nothing
        gridBound.ClearFields()

        'Bind to CategoriesProducts composite table
        gridBound.DataMember = "CategoriesProducts"
        gridBound.DataSource = dataSet

        'Adjust columns width and format
        SetColumnsWidth(gridBound, New Int32() {60, 63, 155, 85, 73, 62, 110})
        gridBound.Columns("UnitPrice").NumberFormat = "Currency"
    End Sub

    'This is an event handler specifying query statements for
    'Customers and Orders SQL-based tables
    Private Sub UnboundTables_BeforeGenerateSql(ByVal sender As Object, ByVal e As C1.Data.GenerateSqlEventArgs)
        If e.TableView.Name = "Customers" Then
            e.Command = New System.Data.OleDb.OleDbCommand( _
             "SELECT Customers.CustomerID, Customers.CompanyName, Customers.Country, " + _
             "	Customers.ContactName, Customers.Phone, " + _
             "   SUM(CCur(OD.UnitPrice*OD.Quantity*(1-OD.Discount)/100)*100) As [Order Sum] " + _
             "FROM Customers, Orders, [Order Details] OD " + _
             "WHERE Customers.CustomerID = Orders.CustomerID AND " + _
             "   OD.OrderID = Orders.OrderID " + _
             "GROUP BY Customers.CustomerID, Customers.CompanyName, " + _
             "   Customers.Country, Customers.ContactName, Customers.Phone", _
             CType(e.TableView.Diagram.Schema.Connections("Connection").DbConnection, System.Data.OleDb.OleDbConnection))
            e.Status = C1.Data.DataSetEventStatusEnum.Skip
        ElseIf e.TableView.Name = "Orders" Then
            e.Command = New System.Data.OleDb.OleDbCommand( _
             "SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.ShippedDate, " + _
             "   COUNT(OD.OrderID) As [Item Count], " + _
             "   SUM(CCur(OD.UnitPrice*OD.Quantity*(1-OD.Discount)/100)*100) As [Sum], " + _
             "   Employees.FirstName+' '+Employees.LastName As [Employee], Orders.ShipName " + _
             "FROM Orders, Employees, [Order Details] OD " + _
             "WHERE Employees.EmployeeID=Orders.EmployeeID AND OD.OrderID = Orders.OrderID " + _
             "GROUP BY Orders.OrderID, Orders.CustomerID, Orders.OrderDate, " + _
             "   Orders.ShippedDate, Employees.FirstName+' '+Employees.LastName, Orders.ShipName", _
             CType(e.TableView.Diagram.Schema.Connections("Connection").DbConnection, System.Data.OleDb.OleDbConnection))
            e.Status = C1.Data.DataSetEventStatusEnum.Skip
        End If
    End Sub

    Private Sub btnFillCustomersOrders_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFillCustomersOrders.Click
        Dim schemaDef As C1.Data.C1SchemaDef
        Dim connection As C1.Data.SchemaObjects.C1OleDbConnection
        Dim dbTableCustomers As C1.Data.SchemaObjects.DbTable
        Dim dbTableOrders As C1.Data.SchemaObjects.DbTable
        Dim simpleRelation As C1.Data.SchemaObjects.SimpleRelation
        Dim joinCondition As C1.Data.SchemaObjects.JoinCondition
        Dim dataSetDef As C1.Data.SchemaObjects.DataSetDef
        Dim tableViewCustomers As C1.Data.SchemaObjects.TableView
        Dim tableViewOrders As C1.Data.SchemaObjects.TableView
        Dim viewRelation As C1.Data.SchemaObjects.ViewRelation
        Dim dataSetLogic As C1.Data.C1DataSetLogic
        Dim dataSet As C1.Data.C1DataSet

        'Create Schema (C1SchemaDef) object
        schemaDef = New C1.Data.C1SchemaDef()

        'Create a connection
        connection = New C1.Data.SchemaObjects.C1OleDbConnection(schemaDef.Schema)
        connection.ConnectionString = GetConnectionString()
        connection.Name = "Connection"

        'Create SQL-based simple table Customers
        dbTableCustomers = New C1.Data.SchemaObjects.DbTable(schemaDef.Schema)
        dbTableCustomers.Name = "Customers"
        dbTableCustomers.Connection = connection
        CreateCustomersFields(dbTableCustomers)

        'Create SQL-based simple table Orders
        dbTableOrders = New C1.Data.SchemaObjects.DbTable(schemaDef.Schema)
        dbTableOrders.Name = "Orders"
        dbTableOrders.Connection = connection
        CreateOrdersFields(dbTableOrders)

        'Create a simple relation between Customers and Orders
        simpleRelation = New C1.Data.SchemaObjects.SimpleRelation(dbTableCustomers, dbTableOrders)
        simpleRelation.Name = "Customers - Orders"
        joinCondition = New C1.Data.SchemaObjects.JoinCondition(simpleRelation.Joins)
        joinCondition.ParentField = dbTableCustomers.Fields("CustomerID")
        joinCondition.ChildField = dbTableOrders.Fields("CustomerID")

        'Create DataSetDef object
        dataSetDef = New C1.Data.SchemaObjects.DataSetDef(schemaDef.Schema)
        dataSetDef.Name = "DataSet"

        'Create TableViews for Customers and Orders tables
        tableViewCustomers = New C1.Data.SchemaObjects.TableView(dataSetDef, dbTableCustomers)
        tableViewCustomers.Name = "Customers"
        tableViewCustomers.RetrieveFields()
        tableViewOrders = New C1.Data.SchemaObjects.TableView(dataSetDef, dbTableOrders)
        tableViewOrders.Name = "Orders"
        tableViewOrders.RetrieveFields()

        'Establish a master-detail view relation
        viewRelation = New C1.Data.SchemaObjects.ViewRelation(dataSetDef, simpleRelation)
        viewRelation.Name = simpleRelation.Name
        viewRelation.Parent = tableViewCustomers
        viewRelation.Child = tableViewOrders

        'Create C1DataSetLogic object to handle BeforeGenerateSql event
        dataSetLogic = New C1.Data.C1DataSetLogic()
        dataSetLogic.SchemaComponent = schemaDef
        dataSetLogic.DataSetDef = "DataSet"
        AddHandler dataSetLogic.BeforeGenerateSql, AddressOf UnboundTables_BeforeGenerateSql

        'Create C1DataSet to bind to
        dataSet = New C1.Data.C1DataSet()
        dataSet.SchemaDef = schemaDef
        dataSet.DataSetDef = "DataSet"
        dataSet.Fill(True)

        'Bind grid gridUnboundParent to Customers (master) table
        gridUnboundParent.DataMember = ""
        gridUnboundParent.DataMember = "_Customers"
        gridUnboundParent.DataSource = dataSet

        'Bind grid gridUnboundChild to Orders (detail) table
        gridUnboundChild.DataMember = ""
        gridUnboundChild.DataMember = "_Customers.Customers - Orders"
        gridUnboundChild.DataSource = dataSet

        'Adjust columns width and format
        SetColumnsWidth(gridUnboundParent, New Int32() {70, 175, 72, 115, 92, 85})
        gridUnboundParent.Columns("Order Sum").NumberFormat = "Currency"
        SetColumnsWidth(gridUnboundChild, New Int32() {50, 70, 80, 80, 65, 73, 110, 110})
        AdjustDateColumn(gridUnboundChild, "OrderDate")
        AdjustDateColumn(gridUnboundChild, "ShippedDate")
        gridUnboundChild.Splits(0).DisplayColumns("CustomerID").Visible = False
        gridUnboundChild.Columns("Sum").NumberFormat = "Currency"
    End Sub

    'Unbind all grids from their data sources
    Private Sub btnClearAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClearAll.Click
        gridBound.DataMember = ""
        gridBound.DataSource = Nothing
        gridUnboundParent.DataMember = ""
        gridUnboundParent.DataSource = Nothing
        gridUnboundChild.DataMember = ""
        gridUnboundChild.DataSource = Nothing
    End Sub

    'Add field definitions to Suppliers bound table
    Private Sub CreateSupplierFields(ByVal dbTableSuppliers As C1.Data.SchemaObjects.DbTable)
        Dim field As C1.Data.SchemaObjects.DbTableField

        field = New C1.Data.SchemaObjects.DbTableField(dbTableSuppliers)
        field.AllowDbNull = False
        field.DbFieldName = "SupplierID"
        field.Name = field.DbFieldName
        field.Type = GetType(Int32)
        field.PrimaryKey = True

        field = New C1.Data.SchemaObjects.DbTableField(dbTableSuppliers)
        field.AllowDbNull = False
        field.DbFieldName = "CompanyName"
        field.Name = field.DbFieldName
        field.Type = GetType(String)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableSuppliers)
        field.DbFieldName = "ContactName"
        field.Name = field.DbFieldName
        field.Type = GetType(String)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableSuppliers)
        field.DbFieldName = "ContactTitle"
        field.Name = field.DbFieldName
        field.Type = GetType(String)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableSuppliers)
        field.DbFieldName = "Country"
        field.Name = field.DbFieldName
        field.Type = GetType(String)
    End Sub

    'Add field definitions to Categories bound table
    Private Sub CreateCategoriesFields(ByVal dbTableCategories As C1.Data.SchemaObjects.DbTable)
        Dim field As C1.Data.SchemaObjects.DbTableField

        field = New C1.Data.SchemaObjects.DbTableField(dbTableCategories)
        field.AllowDbNull = False
        field.DbFieldName = "CategoryID"
        field.Name = field.DbFieldName
        field.Type = GetType(Int32)
        field.PrimaryKey = True

        field = New C1.Data.SchemaObjects.DbTableField(dbTableCategories)
        field.AllowDbNull = False
        field.DbFieldName = "CategoryName"
        field.Name = field.DbFieldName
        field.Type = GetType(String)
    End Sub

    'Add field definitions to Products bound table
    Private Sub CreateProductsFields(ByVal dbTableProducts As C1.Data.SchemaObjects.DbTable)
        Dim field As C1.Data.SchemaObjects.DbTableField

        field = New C1.Data.SchemaObjects.DbTableField(dbTableProducts)
        field.AllowDbNull = False
        field.DbFieldName = "ProductID"
        field.Name = field.DbFieldName
        field.Type = GetType(Int32)
        field.PrimaryKey = True

        field = New C1.Data.SchemaObjects.DbTableField(dbTableProducts)
        field.DbFieldName = "CategoryID"
        field.Name = field.DbFieldName
        field.Type = GetType(Int32)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableProducts)
        field.AllowDbNull = False
        field.DbFieldName = "ProductName"
        field.Name = field.DbFieldName
        field.Type = GetType(String)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableProducts)
        field.AllowDbNull = False
        field.DbFieldName = "Discontinued"
        field.Name = field.DbFieldName
        field.Type = GetType(Boolean)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableProducts)
        field.DbFieldName = "UnitPrice"
        field.Name = field.DbFieldName
        field.Type = GetType(Decimal)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableProducts)
        field.DbFieldName = "QuantityPerUnit"
        field.Name = field.DbFieldName
        field.Type = GetType(String)
    End Sub

    'Add field definitions to Customers unbound table
    Private Sub CreateCustomersFields(ByVal dbTableCustomers As C1.Data.SchemaObjects.DbTable)
        Dim field As C1.Data.SchemaObjects.DbTableField

        field = New C1.Data.SchemaObjects.DbTableField(dbTableCustomers)
        field.AllowDbNull = False
        field.Name = "CustomerID"
        field.DbFieldName = "CustomerID"
        field.Type = GetType(String)
        field.PrimaryKey = True

        field = New C1.Data.SchemaObjects.DbTableField(dbTableCustomers)
        field.AllowDbNull = False
        field.Name = "CompanyName"
        field.DbFieldName = "CompanyName"
        field.Type = GetType(String)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableCustomers)
        field.Name = "Country"
        field.DbFieldName = "Country"
        field.Type = GetType(String)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableCustomers)
        field.Name = "ContactName"
        field.DbFieldName = "ContactName"
        field.Type = GetType(String)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableCustomers)
        field.Name = "Phone"
        field.DbFieldName = "Phone"
        field.Type = GetType(String)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableCustomers)
        field.Name = "Order Sum"
        field.DbFieldName = "Order Sum"
        field.Type = GetType(Decimal)
    End Sub

    'Add field definitions to Orders unbound table
    Private Sub CreateOrdersFields(ByVal dbTableOrders As C1.Data.SchemaObjects.DbTable)
        Dim field As C1.Data.SchemaObjects.DbTableField

        field = New C1.Data.SchemaObjects.DbTableField(dbTableOrders)
        field.AllowDbNull = False
        field.Name = "OrderID"
        field.DbFieldName = "OrderID"
        field.Type = GetType(Int32)
        field.PrimaryKey = True

        field = New C1.Data.SchemaObjects.DbTableField(dbTableOrders)
        field.Name = "CustomerID"
        field.DbFieldName = "CustomerID"
        field.Type = GetType(String)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableOrders)
        field.Name = "OrderDate"
        field.DbFieldName = "OrderDate"
        field.Type = GetType(DateTime)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableOrders)
        field.Name = "ShippedDate"
        field.DbFieldName = "ShippedDate"
        field.Type = GetType(DateTime)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableOrders)
        field.Name = "Item Count"
        field.DbFieldName = "Item Count"
        field.Type = GetType(Int32)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableOrders)
        field.Name = "Sum"
        field.DbFieldName = "Sum"
        field.Type = GetType(Decimal)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableOrders)
        field.Name = "Employee"
        field.DbFieldName = "Employee"
        field.Type = GetType(String)

        field = New C1.Data.SchemaObjects.DbTableField(dbTableOrders)
        field.Name = "ShipName"
        field.DbFieldName = "ShipName"
        field.Type = GetType(String)
    End Sub

End Class
