none
Constraints not enforced RRS feed

  • Question

  • We have some code in our application which calls a stored procedure that returns seven small tables of related data. The data is passed back from the data access layer in a standard DataSet object and we then merge the data into a strongly-typed DataSet.

    A couple of times in the past the DataSet.Merge statement has thrown a ConstraintException due to the fact that the source data did not meet all of the constraints in the destination dataset.  However, because we were merging a whole DataSet, it was not clear which of the seven tables contained some data that was breaking a constraint thereby making it difficult to diagnose the source of the issue.

    To get around this, I amended the code so that it merged each DataTable from the source DataSet separately into the destination DataSet.  I hoped that this would make it easier to get to the bottom of any data-related issues as it would be immediately apparent which of the source data tables contained the erroneous data.  However, I have found that if I do this, sometimes I am not getting a ConstraintException thrown at all even though some of the data breaks the constraints and even though the DataSet thinks that it is enforcing the constraints (i.e. the .EnforceConstraints property is set to true.)

    The documentation for the Merge function states:

    'During a merge, constraints are disabled. If any constraints cannot be enabled at the end of merge, a ConstraintException is generated and the merged data is retained while the constraints are disabled. In this case, the EnforceConstraints property is set to false, and all rows that are invalid are marked in error. The errors must be resolved before attempting to reset the EnforceConstraints property to true.'

    I am not getting the ConstraintException and EnforceConstraints remains set to true.  Here is some simplified code that illustrates the issue:

    static void Main(string[] args)
    {
        LoadData();
    }

    static void LoadData()
    {
        DataSet source = CreateDataSet();
        DataSet destination = CreateDataSet();

        source.Tables["Parent"].Rows.Add(1);
        source.Tables["Parent"].Rows.Add(2);
        source.Tables["Child"].Rows.Add(1);
        source.Tables["Child"].Rows.Add(3);

        destination.Tables["Parent"].Constraints.Add("PK_Parent", destination.Tables["Parent"].Columns["ParentID"], true);
        destination.Tables["Child"].Constraints.Add("FK_Parent_Child", destination.Tables["Parent"].Columns["ParentID"], destination.Tables["Child"].Columns["ParentID"]);

        try
        {
            destination.Merge(source);
        }
        catch (ConstraintException ex)
        {
            // This is correct and expected.
        }

        destination.Tables["Parent"].Merge(source.Tables["Parent"]);
        destination.Tables["Child"].Merge(source.Tables["Child"]);  // No exception here!

        Console.WriteLine("Constraints enforced: {0}", destination.EnforceConstraints);

        destination.EnforceConstraints = false;
        destination.EnforceConstraints = true;  // <--  Exception is thrown here!
    }

    static DataSet CreateDataSet()
    {
        DataSet dataSet = new DataSet();
        DataTable parentTable = new DataTable("Parent");
        DataTable childTable = new DataTable("Child");

        dataSet.Tables.Add(parentTable);
        dataSet.Tables.Add(childTable);
        parentTable.Columns.Add("ParentID", typeof(int));
        childTable.Columns.Add("ParentID", typeof(int));

        return dataSet;
    }

    Friday, November 11, 2016 11:14 AM

All replies