none
Basic Newbie Question about Relationships RRS feed

  • Question

  • C# - VS 2005:

     

    I need a relationship where when I add a new row to a parent, the child also automatically gets a new row. Same for deleting rows.

     

    Both tables have AutoID and ProcessID fields. ProcessID is set as a key field in both tables.

     

    When I add a Relationship using the wizard, should I create a "Relation Only," "Both Relation and Foreign Key Constraint," or "Foreign Key Constraint Only?" Or, am I using the wrong tool?

     

    Thanks,

     

    Dennis

    Wednesday, August 29, 2007 4:29 PM

Answers

  • A DataRelation won't do this for you automatically, but that doesn't make it the wrong tool.

     

    Creating a DataRelation makes it possible for you to rapidly access all of a given parent record's child records.  Creating a foreign key constraint makes it impossible for you to add a child record that has no related parent record.  You probably want both in this scenario.

     

    You'll need to write your own code to ensure that adding a new parent adds a child.  The simplest way to do this is to with an event handler on the table, e.g.

     

    Code Snippet

     

    MyDataSet.ParentTable.RowChanged += new DataRowChangeEventHandler(ParentTable_RowChanged);

     

    void ParentTable_RowChanged(object sender, DataRowChangeEventArgs e)

    {

       DataTable parentTable = (DataTable)sender;

       if (e.Action == DataRowAction.Add)

       {

          DataTable childTable = parentTable.DataSet.Tables["ChildTable"];

          object[] newRow = new object[childTable.Columns.Count];

          newRow[childTable.Columns["ParentID"].Ordinal] = e.Row["ID"];

          childTable.Rows.Add(newRow);

       }

    }

     

    You have to put this in RowChanged, not TableNewRow.  TableNewRow is called when the new row is created, but before the change is committed to the table.  If you have a foreign-key constraint on the child table, adding the child row at that point will throw an exception, because its parent row isn't in the parent table yet.

    Wednesday, August 29, 2007 5:40 PM

All replies

  • A DataRelation won't do this for you automatically, but that doesn't make it the wrong tool.

     

    Creating a DataRelation makes it possible for you to rapidly access all of a given parent record's child records.  Creating a foreign key constraint makes it impossible for you to add a child record that has no related parent record.  You probably want both in this scenario.

     

    You'll need to write your own code to ensure that adding a new parent adds a child.  The simplest way to do this is to with an event handler on the table, e.g.

     

    Code Snippet

     

    MyDataSet.ParentTable.RowChanged += new DataRowChangeEventHandler(ParentTable_RowChanged);

     

    void ParentTable_RowChanged(object sender, DataRowChangeEventArgs e)

    {

       DataTable parentTable = (DataTable)sender;

       if (e.Action == DataRowAction.Add)

       {

          DataTable childTable = parentTable.DataSet.Tables["ChildTable"];

          object[] newRow = new object[childTable.Columns.Count];

          newRow[childTable.Columns["ParentID"].Ordinal] = e.Row["ID"];

          childTable.Rows.Add(newRow);

       }

    }

     

    You have to put this in RowChanged, not TableNewRow.  TableNewRow is called when the new row is created, but before the change is committed to the table.  If you have a foreign-key constraint on the child table, adding the child row at that point will throw an exception, because its parent row isn't in the parent table yet.

    Wednesday, August 29, 2007 5:40 PM
  • It would be nice to see this solution in Basic too!

     

     

     

    Thursday, January 17, 2008 1:58 PM