Does anyone have any tips for dealing with ConstraintExceptions thrown by XSD datasets?
This is the exception with the cryptic message:
System.Data.ConstraintException : Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
A couple of tips that I've found lately.
It's much better to use the TableAdapter FillByDataXXXX() methods instead of GetDataByXXXX() methods because the DataTable passed into the fill method can be interrogated for clues:
Recently, I wrapped up some interrogation code into a subclass of ConstraintException that's turned out to be a useful starting point for debugging.
C# Example usage:
Example.DataSet.fooDataTable table = new DataSet.fooDataTable();
try
{
tableAdapter.Fill(table);
}
catch (ConstraintException ex)
{
// pass the DataTable to DetailedConstraintException to get a more detailed Message property
throw new DetailedConstraintException("error filling table", table, ex);
}
Output:
DetailedConstraintException : table fill failed
Errors reported for ConstraintExceptionHelper.DataSet+fooDataTable [foo]
Columns in error: [1]
[PRODUCT_ID] - total rows affected: 1085
Row errors: [4]
[Column 'PRODUCT_ID' is constrained to be unique. Value '1' is already present.] - total rows affected: 1009
[Column 'PRODUCT_ID' is constrained to be unique. Value '2' is already present.] - total rows affected: 20
[Column 'PRODUCT_ID' is constrained to be unique. Value '4' is already present.] - total rows affected: 34
[Column 'PRODUCT_ID' is constrained to be unique. Value '6' is already present.] - total rows affected: 22
----> System.Data.ConstraintException : Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
I don't know if this is too much code to include in a Stack Overflow answer but here's the C# class in full. Disclaimer: this works for me, please feel free to use/modify as appropriate.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace ConstraintExceptionHelper
{
/// <summary>
/// Subclass of ConstraintException that explains row and column errors in the Message property
/// </summary>
public class DetailedConstraintException : ConstraintException
{
private const int InitialCountValue = 1;
/// <summary>
/// Initialises a new instance of DetailedConstraintException with the specified string and DataTable
/// </summary>
/// <param name="message">exception message</param>
/// <param name="ErroredTable">DataTable in error</param>
public DetailedConstraintException(string message, DataTable erroredTable)
: base(message)
{
ErroredTable = erroredTable;
}
/// <summary>
/// Initialises a new instance of DetailedConstraintException with the specified string, DataTable and inner Exception
/// </summary>
/// <param name="message">exception message</param>
/// <param name="ErroredTable">DataTable in error</param>
/// <param name="inner">the original exception</param>
public DetailedConstraintException(string message, DataTable erroredTable, Exception inner)
: base(message, inner)
{
ErroredTable = erroredTable;
}
private string buildErrorSummaryMessage()
{
if (null == ErroredTable) { return "No errored DataTable specified"; }
if (!ErroredTable.HasErrors) { return "No Row Errors reported in DataTable=[" + ErroredTable.TableName + "]"; }
foreach (DataRow row in ErroredTable.GetErrors())
{
recordColumnsInError(row);
recordRowsInError(row);
}
StringBuilder sb = new StringBuilder();
appendSummaryIntro(sb);
appendErroredColumns(sb);
appendRowErrors(sb);
return sb.ToString();
}
private void recordColumnsInError(DataRow row)
{
foreach (DataColumn column in row.GetColumnsInError())
{
if (_erroredColumns.ContainsKey(column.ColumnName))
{
_erroredColumns[column.ColumnName]++;
continue;
}
_erroredColumns.Add(column.ColumnName, InitialCountValue);
}
}
private void recordRowsInError(DataRow row)
{
if (_rowErrors.ContainsKey(row.RowError))
{
_rowErrors[row.RowError]++;
return;
}
_rowErrors.Add(row.RowError, InitialCountValue);
}
private void appendSummaryIntro(StringBuilder sb)
{
sb.AppendFormat("Errors reported for {1} [{2}]{0}", Environment.NewLine, ErroredTable.GetType().FullName, ErroredTable.TableName);
}
private void appendErroredColumns(StringBuilder sb)
{
sb.AppendFormat("Columns in error: [{1}]{0}", Environment.NewLine, _erroredColumns.Count);
foreach (string columnName in _erroredColumns.Keys)
{
sb.AppendFormat("\t[{1}] - rows affected: {2}{0}",
Environment.NewLine,
columnName,
_erroredColumns[columnName]);
}
}
private void appendRowErrors(StringBuilder sb)
{
sb.AppendFormat("Row errors: [{1}]{0}", Environment.NewLine, _rowErrors.Count);
foreach (string rowError in _rowErrors.Keys)
{
sb.AppendFormat("\t[{1}] - rows affected: {2}{0}",
Environment.NewLine,
rowError,
_rowErrors[rowError]);
}
}
/// <summary>
/// Get the DataTable in error
/// </summary>
public DataTable ErroredTable
{
get { return _erroredTable; }
private set { _erroredTable = value; }
}
/// <summary>
/// Get the original ConstraintException message with extra error information
/// </summary>
public override string Message
{
get { return base.Message + Environment.NewLine + buildErrorSummaryMessage(); }
}
private readonly SortedDictionary<string, int> _rowErrors = new SortedDictionary<string, int>();
private readonly SortedDictionary<string, int> _erroredColumns = new SortedDictionary<string, int>();
private DataTable _erroredTable;
}
}
Thank you for the code!
Copy and past doesn't work properly. It just groups all lines in to one line.
I've manually modified for now, but its a big pain.
EDIT: NM! I did 'View Source' and copied the code from there!
Thank you Jonathan Webb!
I implemented your custom exception class and I found it right away!
I had a problem where a column was returning null when it's constraint was set to not null.
I changed the constraint in the xsd and the problem went away.
Again, thanks for the class, it worked great.
Regards, Mac