[ADVANCED-DOTNET] ADO.NET Parent/Child Relation Referencing

[Doug finke]

How deep can you reference with Parent/Child Relation Referencing?

I have three tables to relate, the first table is clients, the second
table is how many lists each has and the third table holds the items of
each list.

In the client table I create a column and set it's expression to count how
many lists that client has, no problem. An error occurs when I want to sum
the items Amount in that list.



System.Data.SyntaxErrorException: Syntax error in aggregate argument:
Expecting a single column argument with possible 'Child' qualifier.
   at System.Data.ExpressionParser.ParseAggregateArgument(FunctionId
aggregate)
   at System.Data.ExpressionParser.Parse()
   at System.Data.DataExpression..ctor(String expression, DataTable
table,
Type type)
   at System.Data.DataColumn.set_Expression(String value)
   at testRelationsError.Form1.Build() in
c:\temp\testrelationserror\form1.cs:line 129
   at testRelationsError.Form1.Form1_Load(Object sender, EventArgs e) in
c:\temp\testrelationserror\form1.cs:line 95
   at System.Windows.Forms.Form.OnLoad(EventArgs e)
   at System.Windows.Forms.Form.OnCreateControl()
   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl()
   at System.Windows.Forms.Control.WmShowWindow(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ContainerControl.WndProc(Message& m)
   at System.Windows.Forms.Form.WmShowWindow(Message& m)
   at System.Windows.Forms.Form.WndProc(Message& m)
   at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)


  void Build()
  {
   DataSet ds = new DataSet();

   ds.Tables.Add(new DataTable());
   ds.Tables.Add(new DataTable());
   ds.Tables.Add(new DataTable());

   ds.Tables[0].Columns.Add("ClientID", typeof(int));
   ds.Tables[0].Columns.Add("ClientName");

   ds.Tables[1].Columns.Add("ClientID", typeof(int));
   ds.Tables[1].Columns.Add("ListID", typeof(int));
   ds.Tables[1].Columns.Add("ListName");

   ds.Tables[2].Columns.Add("ListID", typeof(int));
   ds.Tables[2].Columns.Add("Amount", typeof(int));
   ds.Tables[2].Columns.Add("Ticker");

   PopulateData(ds);

   ds.Relations.Add("Lists", ds.Tables[0].Columns["ClientID"],
ds.Tables[1].Columns["ClientID"]);
   ds.Relations.Add("ListDetail", ds.Tables[1].Columns["ListID"],
ds.Tables[2].Columns["ListID"]);

   DataColumn dc =null;

   dc = new DataColumn("# in List");
   dc.Expression = "Count(Child(Lists).ListID)";
   ds.Tables[0].Columns.Add(dc);

   dc = new DataColumn("Total Amount");
   dc.Expression = "Sum(Child(Lists)(ListDetail).Amount)";
   ds.Tables[0].Columns.Add(dc);

   dataGrid1.DataSource = ds;
   dataGrid1.DataMember = "Table1";
  }

  void PopulateData(DataSet ds)
  {
   Random r = new Random();
   DataRow dr = null;
   int ListID = 0;

   for(int i = 0; i < 5; i++)
   {
    dr = ds.Tables[0].NewRow();
    dr["ClientID"] = i;
    dr["ClientName"] = "Client " + i;
    ds.Tables[0].Rows.Add(dr);

    for(int j = 0; j < r.Next(1,10); j++)
    {
     dr = ds.Tables[1].NewRow();
     dr["ClientID"] = i;
     dr["ListID"] = ListID;
     dr["ListName"] = "List " + j;
     ds.Tables[1].Rows.Add(dr);

     for(int k = 0; k < r.Next(1,10); k++)
     {
      dr = ds.Tables[2].NewRow();
      dr["ListID"] = ListID;
      dr["Amount"] = r.Next(1000,2000);
      dr["Ticker"] = "Ticker " + k;
      ds.Tables[2].Rows.Add(dr);
     }

     ListID++;
    }
   }
  }

You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced 
DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.