What triggers ConstraintException when loading DataSet?

Question

How can I find out which column and value is violating the constraint? The exception message isn't helpful at all:

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

Answer 1

There is a property called RowError you can check.

See http://dotnetdebug.net/2006/07/16/constraintexception-a-helpful-tip/

Edited to add this link showing iteration of rows to see which had errors.

http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic58812.aspx

Answer 2

Like many people, I have my own standard data access components, which include methods to return a DataSet. Of course, if a ConstraintException is thrown, the DataSet isn't returned to the caller, so the caller can't check for row errors.

What I've done is catch and rethrow ConstraintException in such methods, logging row error details, as in the following example (which uses Log4Net for logging):

...
try
{
    adapter.Fill(dataTable); // or dataSet
}
catch (ConstraintException)
{
    LogErrors(dataTable);
    throw;
}
...

private static void LogErrors(DataSet dataSet)
{
    foreach (DataTable dataTable in dataSet.Tables)
    {
     LogErrors(dataTable);
    }
}

private static void LogErrors(DataTable dataTable)
{
    if (!dataTable.HasErrors) return;
    StringBuilder sb = new StringBuilder();
    sb.AppendFormat(
     CultureInfo.CurrentCulture,
     "ConstraintException while  filling {0}",
     dataTable.TableName);
    DataRow[] errorRows = dataTable.GetErrors();
    for (int i = 0; (i < MAX_ERRORS_TO_LOG) && (i < errorRows.Length); i++)
    {
     sb.AppendLine();
     sb.Append(errorRows[i].RowError);
    }
    _logger.Error(sb.ToString());
}

Answer 3

I added some code that I've found to be useful in debugging ConstraintException occurrences here

Hope this helps.

Answer 4

When you use a strong typed dataset and used the visual designer (xsd): to access tbl.Rows[0].RowError information, you need to create the Fill method.

You can't use the Get method, since the DataTable is instanced within generated code.