Finding DataSet rows which violate a foreign relation?

Question

If I do something like the following and then add a row that has a foreign key value that is not a primary key value in the parent table, no error is thrown. What I would like to do is the following:

1) Set up some foreign relations. 2) Merge some data into the dataset, but do not merge rows which violate the foreign relation. 3) Write the erroneous rows to a comma delimitted file, an excel file, a DataSet or something.

DataColumn pkColumn = 
    AllData.Tables["ParentTable"].Columns["PrimaryKeyColumn"];
DataColumn fkColumn =
    AllData.Tables["ChildTable"].Columns["ForeignKeyColumn"];

DataRelation testRelations = 
    new DataRelation("RelationName", pkColumn, fkColumn);
AllData.Relations.Add(testRelations);

How can I do this? Even if I have to insert into the DataSet row by row, that would be okay so long as I'm not doing some hard coded checks.

Kind regards, Fugu

score 0 · Answer 1 · answered Sep 26 '11 at 22:32

If you perform single inserts, you can check for bad relatives at the time of the insert. You can than copy the bad data to a new table.

In this example I have defined a typed dataset called "dsBadRowTest" Which contains a table called "ParentTable" with the column "PrimaryKeyColumn". It is the parent table of another table called "ChildTable" on it's "ForeignKeyColumn". I define a relational dataset and then attempt to add invalid children. For each invalid child, I store the column information in a separate table variable.

//Our relational dataset...
dsBadRowTest dsRelated = new dsBadRowTest();

//The error table will be a non-relational version
dsBadRowTest.ChildTableDataTable dtErrors = new dsBadRowTest.ChildTableDataTable();

//Add an extra column the error table for extra info
dtErrors.Columns.Add("ErrorMessage");

//Fill our parent table
for (Int32 i = 1; i <= 5; i++) {
    dsRelated.ParentTable.AddParentTableRow(i);
}

//attempt to fill our child table, with invalid children
for (Int32 i = 1; i <= 10; i++) {
    dsBadRowTest.ChildTableRow drNewChild = dsRelated.ChildTable.NewChildTableRow;
    drNewChild.ForeignKeyColumn = i;

    try {
        dsRelated.ChildTable.AddChildTableRow(drNewChild);
    } catch (Data.InvalidConstraintException ex) {
        //Problem adding...Copy the row for the error table    
        dsBadRowTest.ChildTableRow drError = dtErrors.NewChildTableRow;
        foreach (System.Data.DataColumn dc in drNewChild.Table.Columns) {
            drError(dc.ColumnName) = drNewChild(dc);
        }

        //Our non-typed extra column will contain the error message
        drError("ErrorMessage") = ex.Message;

        dtErrors.AddChildTableRow(drError);
    }
}

if (dtErrors.Rows.Count > 0) {
    //Uh oh, we had some bad inserts

    //...do something with the list of errors...
}

You can keep the relations in place and check for specific exceptions as they are throw.

(Here is the typed dataset I used for this sample... (dsBadRowTest.xsd)

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="dsBadRowTest" targetNamespace="http://tempuri.org/dsBadRowTest.xsd" xmlns:mstns="http://tempuri.org/dsBadRowTest.xsd" xmlns="http://tempuri.org/dsBadRowTest.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop" attributeFormDefault="qualified" elementFormDefault="qualified">
  <xs:annotation>
    <xs:appinfo source="urn:schemas-microsoft-com:xml-msdatasource">
      <DataSource DefaultConnectionIndex="0" FunctionsComponentName="QueriesTableAdapter" Modifier="AutoLayout, AnsiClass, Class, Public" SchemaSerializationMode="IncludeSchema" xmlns="urn:schemas-microsoft-com:xml-msdatasource">
        <Connections />
        <Tables />
        <Sources />
      </DataSource>
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="dsBadRowTest" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msprop:Generator_DataSetName="dsBadRowTest" msprop:Generator_UserDSName="dsBadRowTest">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="ParentTable" msprop:Generator_TableClassName="ParentTableDataTable" msprop:Generator_TableVarName="tableParentTable" msprop:Generator_TablePropName="ParentTable" msprop:Generator_RowDeletingName="ParentTableRowDeleting" msprop:Generator_UserTableName="ParentTable" msprop:Generator_RowChangingName="ParentTableRowChanging" msprop:Generator_RowEvHandlerName="ParentTableRowChangeEventHandler" msprop:Generator_RowDeletedName="ParentTableRowDeleted" msprop:Generator_RowEvArgName="ParentTableRowChangeEvent" msprop:Generator_RowChangedName="ParentTableRowChanged" msprop:Generator_RowClassName="ParentTableRow">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="PrimaryKeyColumn" msprop:Generator_ColumnVarNameInTable="columnPrimaryKeyColumn" msprop:Generator_ColumnPropNameInRow="PrimaryKeyColumn" msprop:Generator_ColumnPropNameInTable="PrimaryKeyColumnColumn" msprop:Generator_UserColumnName="PrimaryKeyColumn" type="xs:int" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="ChildTable" msprop:Generator_TableClassName="ChildTableDataTable" msprop:Generator_TableVarName="tableChildTable" msprop:Generator_TablePropName="ChildTable" msprop:Generator_RowDeletingName="ChildTableRowDeleting" msprop:Generator_UserTableName="ChildTable" msprop:Generator_RowChangingName="ChildTableRowChanging" msprop:Generator_RowEvHandlerName="ChildTableRowChangeEventHandler" msprop:Generator_RowDeletedName="ChildTableRowDeleted" msprop:Generator_RowEvArgName="ChildTableRowChangeEvent" msprop:Generator_RowChangedName="ChildTableRowChanged" msprop:Generator_RowClassName="ChildTableRow">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ForeignKeyColumn" msprop:Generator_ColumnVarNameInTable="columnForeignKeyColumn" msprop:Generator_ColumnPropNameInRow="ForeignKeyColumn" msprop:Generator_ColumnPropNameInTable="ForeignKeyColumnColumn" msprop:Generator_UserColumnName="ForeignKeyColumn" type="xs:int" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
    <xs:unique name="Constraint1" msdata:PrimaryKey="true">
      <xs:selector xpath=".//mstns:ParentTable" />
      <xs:field xpath="mstns:PrimaryKeyColumn" />
    </xs:unique>
    <xs:keyref name="FK_ParentTable_ChildTable" refer="Constraint1" msprop:rel_Generator_UserChildTable="ChildTable" msprop:rel_Generator_ChildPropName="GetChildTableRows" msprop:rel_Generator_UserParentTable="ParentTable" msprop:rel_Generator_UserRelationName="FK_ParentTable_ChildTable" msprop:rel_Generator_RelationVarName="relationFK_ParentTable_ChildTable" msprop:rel_Generator_ParentPropName="ParentTableRow" msdata:UpdateRule="None" msdata:DeleteRule="None">
      <xs:selector xpath=".//mstns:ChildTable" />
      <xs:field xpath="mstns:ForeignKeyColumn" />
    </xs:keyref>
  </xs:element>
</xs:schema>

I hope this helps you.

Finding DataSet rows which violate a foreign relation?

1 Answers1