This sample demonstrates the different kinds of relations that exist between tables in a data set. Relations can be automatically populated from the information stored in a data set or manually set up in the designer.
Features:
The following types of relations are shown in this example:
Master-Details Relations
In this type, the matching keys in columns in the parent and child tables define a relationship between two tables. This is a 1:n relation where each record in the child table can belong to only one parent record. Each record can have multiple nested tables and the end user can expand or collapse the nested tables.
Foreign-Key Relations
This is for looking up values in cases where an I.D. column in the main table can be used to show a record in a related table. This is a n:1 relation where multiple records in the parent table can reference the same record in a related table. Fields in the related table can be referenced using a '.' (dot) in the FieldDescriptor.MappingName of the main table. Multiple columns of a foreign-key table can be imported into the main table and used just like any other regular field of the main table. You can use these foreign columns in expressions, filters, sorting, or to group by operations.
Foreign-Key Word Relations
This is a unique relation that is offered by the grouping engine. It is a foreign-key relation where matching keys in the columns of the parent and child table define a relationship between the two tables. This is a m:n relation. Field summaries of the related child table can be referenced using a '.' (dot) in the FieldDescriptor.MappingName of the main table.
This example also shows you how to implement unbound fields inside one of the foreign-key relations.
This is how the sample looks.
Interactive Features:
Employee Territory Order - Edit Territories
Employee Territory Order - Foreign-Key Keywords
Employee Territory Order - Foreign-Key Keywords
In this sample, a data set has been created where the designer and foreign-key constraints have been defined with related tables at design time. The GridRelationDescriptor is created through code and the name of the foreign-key constraints are assigned to GridRelationDescriptor.MappingName.
The grouping engine automatically extracts all the relation keys and the child table name from the foreign-key constraint of the data set.
// Setting up Master-Details Relation between Employees and Territories. GridRelationDescriptor gridRelationDescriptor1 = new GridRelationDescriptor(); gridRelationDescriptor1.MappingName = "EmployeesEmployeeTerritories";... this.gridGroupingControl1.TableDescriptor.Relations.Add(gridRelationDescriptor1);
This sample extracts the relationship information, between the Employees table and the employee territories, from the data set. The data set has a data relation with the same name, and the grouping engine knows how to extract the primary-key and the foreign-key information from the data relation.
You also have the option to manually specify the relations between the two tables.
// Add an additional foreign key relation for ReportsTo.
GridRelationDescriptor reportsToRd = new GridRelationDescriptor();
reportsToRd.RelationKind = RelationKind.ForeignKeyReference;
reportsToRd.Name = "ReportsTo"; // Prefix for fields that get added to main table (e.g. ReportsTo_Title).
reportsToRd.ChildTableName = "Employees"; // Name to look up a related table in the Engine.SourceListSet.
reportsToRd.RelationKeys.Add("ReportsTo", "EmployeeID"); // Foreign key and primary key in related table.
reportsToRd.ChildTableDescriptor.Relations.Clear(); // don't autopopulate further nested relations - avoid recursion.
In the above code, we establish a relationship between employees and specify the exact fields that define the relationship with the RelationKeys collection.
You can also create relations to collections not belonging to the data set. You can create relationships between any IList-derived collection. The following example creates a collection, adds it to the engine's source-list set, and defines a foreign-key relation.
First, we register the collection and give it a name so that it can later be referenced.
this.gridGroupingControl1.Engine.SourceListSet.Add("USStates", USStatesCollection.CreateDefaultCollection());
Now, you can bind this collection to the Region field of the Employees table.
//
// USStates
//
GridRelationDescriptor usStatesRd = new GridRelationDescriptor();
usStatesRd.Name = "State";
usStatesRd.RelationKind = RelationKind.ForeignKeyReference;
usStatesRd.ChildTableName = "USStates"; // SourceListSet name for lookup
usStatesRd.RelationKeys.Add("Region", "Key");
// Let's also customize the appearance of the dropdown table here.
usStatesRd.ChildTableDescriptor.Appearance.AlternateRecordFieldCell.BackColor = Color.Beige;
// Description is a a custom property descriptor in USStatesCollection.
usStatesRd.ChildTableDescriptor.Columns.Add(new GridColumnDescriptor("State", "Description"));
usStatesRd.ChildTableDescriptor.SortedColumns.Add("State");
usStatesRd.ChildTableDescriptor.AllowEdit = false;
usStatesRd.ChildTableDescriptor.AllowNew = false; // Make pencil icon disappear, users can't modify states.
mainTd.Relations.Add(usStatesRd);
As mentioned earlier, this example also demonstrates setting up an unbound field. The Reports field in the main table shows the title information inside the field.
This is the code that is used to provide data for the unbound field at run time.
// Unbound field: Event to fill ReportsTo_LastNameAndTitle with formatted text.
reportsToRd.ChildTableDescriptor.UnboundFields.Add("LastNameAndTitle"); // "[LastName] + '(' + [Title] + ')'"));
this.gridGroupingControl1.QueryValue += new FieldValueEventHandler(gridGroupingControl1_QueryValue);
private void gridGroupingControl1_QueryValue(object sender, FieldValueEventArgs e)
{
if (e.TableDescriptor.Name == "ReportsTo" && e.Field.Name == "LastNameAndTitle")
{
// "[LastName] + '(' + [Title] + ')'"));
e.Value = String.Format("{0} ({1})", e.Record.GetValue("LastName"), e.Record.GetValue("Title"));
}
}
Once you set up an unbound field, it can be used just like any other regular field in a table. You can add it to the SortedColumns or GroupedColumns collections, filter by it, or it can be used as a display member of a foreign-key column, as shown in the code below.
mainTd.VisibleColumns.Add("ReportsTo_LastNameAndTitle"); // will be shown as ReportsTo_LastNameAndTitle in main table.
Finally, if you want to access nested tables at run time and expand or collapse them from code, follow the sample given below that shows how to gain access to a specific element.
this.gridGroupingControl1.Table.Records[0].NestedTables["EmployeeTerritories"].IsExpanded = true;
GridGroupingControl.Table.Records is a collection of all employee records. You can gain access to employee territories that belong to a record using its NestedTables collection and then set its Expanded state to true.
this.gridGroupingControl1.Table.Records[0].NestedTables["Orders"].Records[0].NestedTables["Order
Details"].Records[0].SetCurrent("Products_ProductName");
In the above code, the current record is moved to the first record in the Order Details nested table of the first record. You can then set the current cell in the Products_ProductName field.
When this sample starts, check where the current record and field are being displayed.