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 |
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"; } |