Creating a Composite Table Programmatically

The following code is an example of how to create a composite table at run time in code. See Composite Tables and Using Composite Tables for additional information. Note that you might need change to the connection string to add the full directory where the Nwind database is installed.

To write code in Visual Basic

Visual Basic	Copy Code
'Connection string Private connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _ "Data Source=C:\Users\<User Name>\Documents\ComponentOne Samples\Common\C1NWind.mdb;" + _ "Persist Security Info=False" 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 = connectionString 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

Visual Basic

Copy Code

'Connection string
    Private connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
     "Data Source=C:\Users\<User Name>\Documents\ComponentOne Samples\Common\C1NWind.mdb;" + _
     "Persist Security Info=False"
 
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 = connectionString
        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

To write code in C#

C#	Copy Code
// Connection string private readonly string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;"+ @"Data Source=C1NWind.mdb;" + @"Persist Security Info=False"; private void btnFillCategoriesProducts_Click(object sender, System.EventArgs e) { C1SchemaDef schemaDef; C1OleDbConnection connection; DbTable dbTableCategories; DbTable dbTableProducts; SimpleRelation simpleRelation; JoinCondition joinCondition; CompositeTable compositeTable; TableView tableViewCategories; TableView tableViewProducts; ViewRelation viewRelation; DataSetDef dataSetDef; TableView tableView; C1DataSet dataSet; // Create Schema (C1SchemaDef) object schemaDef = new C1SchemaDef(); // Create a connection connection = new C1OleDbConnection(schemaDef.Schema); connection.ConnectionString = connectionString; connection.Name = "Connection"; // Create simple table Categories dbTableCategories = new DbTable(schemaDef.Schema); dbTableCategories.DbTableName = "Categories"; dbTableCategories.Name = dbTableCategories.DbTableName; dbTableCategories.Connection = connection; CreateCategoriesFields(dbTableCategories); // Create simple table Products dbTableProducts = new DbTable(schemaDef.Schema); dbTableProducts.DbTableName = "Products"; dbTableProducts.Name = dbTableProducts.DbTableName; dbTableProducts.Connection = connection; CreateProductsFields(dbTableProducts); // Establish a simple relation simpleRelation = new SimpleRelation(dbTableCategories, dbTableProducts); simpleRelation.Name = "Categories - Products"; joinCondition = new JoinCondition(simpleRelation.Joins); joinCondition.ParentField = dbTableCategories.Fields["CategoryID"]; joinCondition.ChildField = dbTableProducts.Fields["CategoryID"]; // Create a composite table compositeTable = new CompositeTable(schemaDef.Schema); compositeTable.Name = "CategoriesProducts"; // Create TableView for Categories table tableViewCategories = new CompositeDefView(compositeTable.CompositeTableDef, dbTableCategories); tableViewCategories.Name = "Categories"; tableViewCategories.RetrieveFields(); // Create TableView for Products table tableViewProducts = new CompositeDefView(compositeTable.CompositeTableDef, dbTableProducts); tableViewProducts.Name = "Products"; tableViewProducts.RetrieveFields(); // Create ViewRelation between the TableViews viewRelation = new 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 DataSetDef(schemaDef.Schema); dataSetDef.Name = "DataSet"; // Create TableView for the whole composite table tableView = new TableView(dataSetDef, compositeTable); tableView.Name = "CategoriesProducts"; tableView.RetrieveFields(); // Create C1DataSet to bind to dataSet = new C1DataSet(); dataSet.SchemaDef = schemaDef; dataSet.DataSetDef = "DataSet"; dataSet.Fill(true); // Unbind the grid from current data source gridBound.DataMember = ""; gridBound.DataSource = null; gridBound.ClearFields(); // Bind to CategoriesProducts composite table gridBound.DataMember = "CategoriesProducts"; gridBound.DataSource = dataSet; // Adjust columns width and format SetColumnsWidth(gridBound, new int[] {60, 63, 155, 85, 73, 62, 110}); gridBound.Columns["UnitPrice"].NumberFormat = "Currency"; }

Copy Code

// Connection string
private readonly string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;"+  @"Data Source=C1NWind.mdb;" + @"Persist Security Info=False";
 
private void btnFillCategoriesProducts_Click(object sender, System.EventArgs e)
{
    C1SchemaDef schemaDef;
    C1OleDbConnection connection;
    DbTable dbTableCategories;
    DbTable dbTableProducts;
    SimpleRelation simpleRelation;
    JoinCondition joinCondition;
    CompositeTable compositeTable;
    TableView tableViewCategories;
    TableView tableViewProducts;
    ViewRelation viewRelation;
    DataSetDef dataSetDef;
    TableView tableView;
    C1DataSet dataSet;
 
    // Create Schema (C1SchemaDef) object
    schemaDef = new C1SchemaDef();
 
    // Create a connection
    connection = new C1OleDbConnection(schemaDef.Schema);
    connection.ConnectionString = connectionString;
    connection.Name = "Connection";
 
    // Create simple table Categories
    dbTableCategories = new DbTable(schemaDef.Schema);
    dbTableCategories.DbTableName = "Categories";
    dbTableCategories.Name = dbTableCategories.DbTableName;
    dbTableCategories.Connection = connection;
    CreateCategoriesFields(dbTableCategories);
 
    // Create simple table Products
    dbTableProducts = new DbTable(schemaDef.Schema);
    dbTableProducts.DbTableName = "Products";
    dbTableProducts.Name = dbTableProducts.DbTableName;
    dbTableProducts.Connection = connection;
    CreateProductsFields(dbTableProducts);
 
    // Establish a simple relation
    simpleRelation = new SimpleRelation(dbTableCategories, dbTableProducts);
    simpleRelation.Name = "Categories - Products";
    joinCondition = new JoinCondition(simpleRelation.Joins);
    joinCondition.ParentField = dbTableCategories.Fields["CategoryID"];
    joinCondition.ChildField = dbTableProducts.Fields["CategoryID"];
 
    // Create a composite table
    compositeTable = new CompositeTable(schemaDef.Schema);
    compositeTable.Name = "CategoriesProducts";
 
    // Create TableView for Categories table
    tableViewCategories = new CompositeDefView(compositeTable.CompositeTableDef, dbTableCategories);
    tableViewCategories.Name = "Categories";
    tableViewCategories.RetrieveFields();
 
    // Create TableView for Products table
    tableViewProducts = new CompositeDefView(compositeTable.CompositeTableDef, dbTableProducts);
    tableViewProducts.Name = "Products";
    tableViewProducts.RetrieveFields();
 
    // Create ViewRelation between the TableViews
    viewRelation = new 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 DataSetDef(schemaDef.Schema);
    dataSetDef.Name = "DataSet";
 
    // Create TableView for the whole composite table
    tableView = new TableView(dataSetDef, compositeTable);
    tableView.Name = "CategoriesProducts";
    tableView.RetrieveFields();
 
    // Create C1DataSet to bind to
    dataSet = new C1DataSet();
    dataSet.SchemaDef = schemaDef;
    dataSet.DataSetDef = "DataSet";
    dataSet.Fill(true);
 
    // Unbind the grid from current data source
    gridBound.DataMember = "";
    gridBound.DataSource = null;
    gridBound.ClearFields();
 
    // Bind to CategoriesProducts composite table
    gridBound.DataMember = "CategoriesProducts";
    gridBound.DataSource = dataSet;
 
    // Adjust columns width and format
    SetColumnsWidth(gridBound, new int[] {60, 63, 155, 85, 73, 62, 110});
    gridBound.Columns["UnitPrice"].NumberFormat = "Currency";
}

Note: Sample Project Available For a complete sample detailing how to create a composite table programmatically, see the Programmatic sample available from the ComponentOne HelpCentral Sample page.