Debugging DataSet Constraint issues

If you work with typed datasets often enough, then chances are that at some point you're going to run across this error message:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

The error message is logical enough - it tells you what's wrong. You have a dataset, and there's a piece of data which is violating it's constraints. If you have a small dataset, then you can usually spot where the problem lies quickly enough. However for larger datasets the offending table(s) and/or column(s) are usually harder to locate. If you don't feel like combing through your datasets with a fine comb, then here's a quick way to find out where your problem lies.

First, you're obviously going to need a general idea of which dataset is causing the problem, as well as an instance of that dataset being populated to use for your testing. Find a spot in your code where your offending dataset is being populated. Update your code so that you disable the constraints before population, ie:

someDataset.EnforceConstraints = false;
someDataset = MyAwesomeBusinessFacade.PopulateAwesomeDataset(awsesomeParameter);


Now, add the following method somewhere in your code:

private static void CheckDataSet(DataSet ds)
{
    try
    {
        ds.EnforceConstraints = true;
    }
    catch (Exception ex)
    {
        DataRow[] rowsInError;

        foreach (DataTable myTable in ds.Tables)
        {
            // Test if the table has errors. If not, skip it.
            if (myTable.HasErrors)
            {
                // Get an array of all rows with errors.
                rowsInError = myTable.GetErrors();
                // Print the error of each column in each row.
                for (int i = 0; i < rowsInError.Length; i++)
                {
                    foreach (DataColumn myCol in myTable.Columns)
                    {
                        Debug.WriteLine(myCol.ColumnName + " " +
                        rowsInError[i].GetColumnError(myCol));
                    }
                    // Clear the row errors
                    rowsInError[i].ClearErrors();
                }
            }
        }
    }
}

Finally call the method and pass through your dataset:

someDataset.EnforceConstraints = false;
someDataset = MyAwesomeBusinessFacade.PopulateAwesomeDataset(awsesomeParameter);
CheckDataSet(someDataset);

In your Visual Studio debug output window you should have enough details to help you fix the problem. If not, then it's pretty easy to modify the CheckDataSet method in order to add more detail around wherever you percieve your problem to be.

This is one of those code snippets which is pretty basic, but gets filed under the category of "incredibly usefulful when you just need to find the problem, fix it, and move on in a hurry".

Tags: ASP.NET, ADO.NET,

 Print | Posted on Tuesday, March 13, 2007 2:24 PM | Filed Under [ ASP.NET Visual Studio Web Development ]