Hi, when you use itemarray you must set all values at once.
so :
theRow.ItemArray = new object[] { x,y,z,s,d };
I agree it is not clear that you should use it that way..
I guess the getter makes a copy of the data and you fill element 5 of that copy
You should do :
theRow["Groups"] = "1234"
Or :
theRow[5] = "1234"
Assuming that there's relational integrity between your employees/groups and associations, you can put the tables in a DataSet, add DataRelations to define the relationships between the tables, and then create DataColumns in the association table that use DataColumn.Expression to look up values from the related tables. This will give you an EmployeeGroups table that can display each employee and group name.
Tragically, if you want to go the other way, i.e. create a column in the Employees table that aggregates together all of its groups (which is what you're describing), you can't do it with an expression. In that case, it probably is easiest to do what you describe, and add a column to the Employees table. But LINQ makes populating that column pretty simple, and if you've already created the relations between the tables, and the columns in the EmployeeGroups table that get the employee and group names, so much the better.
Which brings us to this. Here's a class that creates and populates a data set with Employees, Groups, and EmployeeGroups tables. It creates relations between the association table and the other two tables, which DataColumn.Expression and DataRow.GetChildRows need in order to do their magic. It also exposes the tables as IList types that WPF can bind to:
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
namespace JoinAndGroupDemo
{
public class DataSource
{
private DataSet _DS;
public DataSource()
{
CreateDataSet();
}
private void CreateDataSet()
{
_DS = new DataSet();
DataTable emp = _DS.Tables.Add("Employees");
DataTable grp = _DS.Tables.Add("Groups");
InitTable(emp);
InitTable(grp);
DataTable assoc = _DS.Tables.Add("EmployeeGroups");
assoc.Columns.Add("empId", typeof (int));
assoc.Columns.Add("grpId", typeof (int));
_DS.Relations.Add(new DataRelation("FK_EmployeeGroups_Employees",
emp.Columns["id"], assoc.Columns["empId"]));
_DS.Relations.Add(new DataRelation("FK_EmployeeGroups_Groups",
grp.Columns["id"], assoc.Columns["grpId"]));
assoc.Columns.Add("emp_name");
assoc.Columns["emp_name"].Expression = "Parent(FK_EmployeeGroups_Employees).name";
assoc.Columns.Add("grp_name");
assoc.Columns["grp_name"].Expression = "Parent(FK_EmployeeGroups_Groups).name";
emp.Rows.Add(new object[] { 1, "Malcolm Reynolds"});
emp.Rows.Add(new object[] { 2, "Zoe Washburne" });
emp.Rows.Add(new object[] { 3, "Hoban Washburne" });
emp.Rows.Add(new object[] { 4, "Irina Serra" });
emp.Rows.Add(new object[] { 5, "Jayne Cobb" });
emp.Rows.Add(new object[] { 6, "Kaylee Frye" });
emp.Rows.Add(new object[] { 7, "Simon Tam" });
emp.Rows.Add(new object[] { 8, "River Tam" });
emp.Rows.Add(new object[] { 9, "Derrial Book" });
grp.Rows.Add(new object[] { 1, "Command"});
grp.Rows.Add(new object[] { 2, "Combat" });
grp.Rows.Add(new object[] { 3, "Operations" });
grp.Rows.Add(new object[] { 4, "Other" });
assoc.Rows.Add(new object[] { 1, 1 });
assoc.Rows.Add(new object[] { 2, 1 });
assoc.Rows.Add(new object[] { 1, 2 });
assoc.Rows.Add(new object[] { 2, 2 });
assoc.Rows.Add(new object[] { 5, 2 });
assoc.Rows.Add(new object[] { 8, 2 }); // spoiler alert!
assoc.Rows.Add(new object[] { 3, 3 });
assoc.Rows.Add(new object[] { 6, 3 });
assoc.Rows.Add(new object[] { 4, 4 });
assoc.Rows.Add(new object[] { 7, 4 });
assoc.Rows.Add(new object[] { 8, 4 });
assoc.Rows.Add(new object[] { 9, 4 });
emp.Columns.Add("groups", typeof (string));
foreach (DataRow empRow in emp.Rows)
{
empRow["groups"] = string.Join(
", ",
empRow
.GetChildRows("FK_EmployeeGroups_Employees")
.AsEnumerable()
.Select(x => (string) x["grp_name"])
.ToArray());
}
grp.Columns.Add("employees", typeof(string));
foreach (DataRow grpRow in grp.Rows)
{
grpRow["employees"] = string.Join(
", ",
grpRow
.GetChildRows("FK_EmployeeGroups_Groups")
.AsEnumerable()
.Select(x => (string)x["emp_name"])
.ToArray());
}
}
private void InitTable(DataTable t)
{
t.Columns.Add("id", typeof (int));
t.Columns.Add("name", typeof (string));
// this is required by DataRelations
t.PrimaryKey = new DataColumn[] { t.Columns["id"]};
}
public IList Employees
{
get
{ return ((IListSource)_DS.Tables["Employees"]).GetList(); }
}
public IList Groups
{
get { return ((IListSource)_DS.Tables["Groups"]).GetList(); }
}
public IList EmployeeGroups
{
get { return ((IListSource)_DS.Tables["EmployeeGroups"]).GetList(); }
}
}
}
And here's the XAML for a Window that displays all this information:
<Window x:Class="JoinAndGroupDemo.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:local="clr-namespace:JoinAndGroupDemo"
Title="MainWindow" Height="350" Width="525">
<Window.Resources>
<local:DataSource x:Key="DataSource"/>
<Style TargetType="Label">
<Style.Setters>
<Setter Property="Background" Value="Navy"/>
<Setter Property="Foreground" Value="White"/>
<Setter Property="HorizontalAlignment" Value="Stretch"/>
<Setter Property="HorizontalContentAlignment" Value="Center"/>
</Style.Setters>
</Style>
</Window.Resources>
<DockPanel DataContext="{StaticResource DataSource}">
<DockPanel DockPanel.Dock="Left">
<Label DockPanel.Dock="Top">Employees</Label>
<ListView ItemsSource="{Binding Employees}">
<ListView.View>
<GridView>
<GridView.Columns>
<GridViewColumn Header="ID" DisplayMemberBinding="{Binding Path=id}"/>
<GridViewColumn Header="Name" DisplayMemberBinding="{Binding Path=name}"/>
<GridViewColumn Header="Groups" DisplayMemberBinding="{Binding Path=groups}"/>
</GridView.Columns>
</GridView>
</ListView.View>
</ListView>
</DockPanel>
<DockPanel DockPanel.Dock="Left">
<Label DockPanel.Dock="Top">Groups</Label>
<ListView ItemsSource="{Binding Groups}">
<ListView.View>
<GridView>
<GridView.Columns>
<GridViewColumn Header="ID" DisplayMemberBinding="{Binding Path=id}"/>
<GridViewColumn Header="Name" DisplayMemberBinding="{Binding Path=name}"/>
<GridViewColumn Header="Employees" DisplayMemberBinding="{Binding Path=employees}"/>
</GridView.Columns>
</GridView>
</ListView.View>
</ListView>
</DockPanel>
<DockPanel>
<Label DockPanel.Dock="Top">Employee groups</Label>
<ListView ItemsSource="{Binding EmployeeGroups}">
<ListView.View>
<GridView>
<GridView.Columns>
<GridViewColumn Header="Employee" DisplayMemberBinding="{Binding Path=emp_name}"/>
<GridViewColumn Header="Group" DisplayMemberBinding="{Binding Path=grp_name}"/>
</GridView.Columns>
</GridView>
</ListView.View>
</ListView>
</DockPanel>
</DockPanel>
</Window>